One of the most frequent Excel questions I hear is some variation of “Is there a way to add the same header and footer (or page layout) to more than one worksheet at the same time?” The answer is easy but the steps are not intuitive and not even covered in most Excel books or websites. The solution is to group worksheets by selecting multiple sheets in a workbook. When you group worksheets you can:
To select two or more adjacent sheets: Click the tab for the first sheet in the group. Then hold down [Shift] while you click the tab for the last sheet you want to select.
To select two or more non-adjacent sheets: Click the tab for the first sheet in the group. Then hold down [Ctrl] while you click the tabs of the other sheets you want to select. Tabs for selected worksheets will display as active or white.
To select all of the worksheets in a workbook: Right-click on any sheet tab and then left-click on Select All Sheets from the shortcut menu.
When more than one worksheet is selected, you should see [Group] in the title bar of your Excel workbook.
Once you group worksheets, any action or command you do in one of the worksheets will also apply to all of the other worksheets in the group. This is the trick to duplicate the same header, footer, or page layout to a group of worksheets. Once the sheets are selected pick Insert > Header & Footer. Also, any text or formatting you add, such as a change to a column heading will be the same in each selected worksheet. When grouping, if you type data into cell B5, for example, every selected worksheet will now have the new data in cell B5.
Careful! Once you are finished with the formatting and data changes you want to add to the selected worksheets, be sure to un-group worksheets so you don’t accidentally change a group of worksheets with an edit intended only for one worksheet.
To un-group/un-select a group of worksheets:
Check the workbook title bar to verify your worksheets are no longer grouped and continue working with each worksheet individually.
By Dawn Bjork, MCT, The Software Pro®
Microsoft Certified Trainer, Technology Speaker, Software Consultant