Format Many Excel Worksheets With A Single Click

BY STANLEY ZAROWIN

Key to Instructions
To help readers follow the instructions in this article, we used two different typefaces:
Boldface type is used to identify the names of icons, agendas and URLs.
Sans serif type shows commands and instructions users should type into the computer and the names of files.
 
Q. Every month I have to custom-format 24 worksheets with a complex layout, and it’s tedious doing them one at a time. Is there some way to do it more efficiently?

A. Yes, you can group the worksheets so that any action performed on one automatically affects all the others. When the tabs are contiguous—that is, lined up next to each other—you can group them by clicking on the first tab and then, while holding down the Shift key, clicking on the last tab. If they are not contiguous, hold down the Ctrl key and click on each sheet you want grouped.

Now do your page setup in any of the grouped worksheets, and all the others will be similarly formatted. When finished, you must ungroup your sheets or any data added to one will be added to the others. To ungroup, click on any sheet not in the group or right-click on a sheet tab and select Ungroup (see screenshot at right).

Since you have to repeat the process monthly, another possibility is to create a template of the 24-worksheet file. That way you’ll only have to do the format once. To create a template, set up a new file with all 24 sheets formatted, then click on Save As to generate this screen:

Give a name to the file and click on Template (*.xlt) and then on Save. Now, whenever you need the file, click on New in the toolbar and the template name will appear on your right. Click on it and you’re in business.

SPONSORED REPORT

2018 financial reporting survey: Challenges and trends

Learn the top reporting challenges that emerged in a survey of more than 800 finance, accounting, and compliance professionals across the world, and compare them with your organization's obstacles.

PODCAST

How the skill set for today’s CFO is changing

Scott Simmons, a search expert for large-company CFOs, gives advice for the next generation of finance leaders and more, including which universities are regularly producing future CEOs and CFOs.