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)…
Unlike ordinary shortcuts, templates have a welldeserved golden crown (see screenshot at right).