Format Many Excel Worksheets With A Single Click

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.