Use a Template to Spare You the Labor of Formatting a Frequently Used Special-Needs Worksheet

BY STANLEY ZAROWIN

I use a spreadsheet for my work that’s formatted in a very complicated way. So every time I open a new worksheet, I have to go through all the preliminary format setup steps to prepare it for my special requirements—such as columns with varying widths and even different colors for numbers in certain rows. Is there some way to adjust Excel to do that time-consuming setup for me?

You can create a macro to do that by clicking on Tools, Macro, Record New Macro, and then, after you type in where you want the macro stored and add a Shortcut key and a Description, click on OK and…

…then go through all the formatting steps. When you’re finished, click on the Stop Recording button (see screenshot at left).

Another way—and one that I personally prefer because it’s so simple and avoids some of the security drawbacks inherent with macros—is to create a template of your ideal worksheet. Then, when you need your special worksheet, just one click brings it up. You can evoke it as many times as you wish. In fact, if you like the template idea, you may even want to set up a group of templates—one for each unique setup (whether in Excel or Word, for that matter) and then create a folder to store them and a shortcut for the collection on your desktop for convenient access.

By the way, a template stores not just formatting (borders, shading, etc.), it also stores column and row headings, and even charts and formulas and toolbars for customized features. So you see, it can be quite powerful.

To create a template, open a new Excel file and set up your model worksheet. Then save it (Ctrl+S) to a convenient folder using the Template designation (see screenshot below). If you’re starting from an existing file, you must use the Save As command.

Then, to create a shortcut, go to your desktop, right-click, and then click on New, Shortcut (see screenshot below)…


…and fill in the required information to locate the template.

Unlike ordinary shortcuts, templates have a welldeserved golden crown (see screenshot at right).

 

 

 

 

 

 

SPONSORED REPORT

Keeping client information safe in an age of scams and security threats

A look at the Dirty Dozen tax scams and ways to protect taxpayer information.

TECHNOLOGY Q&A

How to create maps in Excel 2016

Microsoft Excel 2016 has two new mapping capabilities. J. Carlton Collins, CPA, demonstrates how to make masterful 2D and 3D maps in Excel 2016.

QUIZ

News quiz: IRS enforcement, a hot job, and audit value

The IRS’s 2016 Data Book, a “hot job” of particular interest at this time of year, and insight into how executive and audit committees view the insights from financial statement audits received attention recently. See how much you know with this short quiz.