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.

TAX PRACTICE CORNER

More R&D tax help

"Can I use the R&D credit?" PATH Act enhancements make the credit more attractive to a wider range of taxpayers.

QUIZ

News quiz: Tax-related data breach explained

News about a data breach that affected about 100,000 people, the IRS’s budget for the fiscal year, and the 2018 health spending account limits received attention recently. See how much you know with this short quiz.