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 are 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:
- Print a selection of worksheets at the same time.
- Enter or edit data on several worksheets at once.
- Apply formatting, such as Page Setup options, to a selection of worksheets simultaneously. When you group worksheets, you don’t need to repeat the same steps over and over.
- Move, copy, or delete multiple worksheets at once.
To find out how to group worksheets and apply editing and formatting actions, continue reading this post or watch this video:
How to Group Excel Worksheets
- 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 perform in one worksheet will also apply to all the other worksheets in the group. This is the trick to duplicating 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:
- Left-click on any sheet tab
-OR- - Right-click on any sheet tab and then left-click on Ungroup Sheets from the shortcut menu
Important: Check the workbook title bar to verify your worksheets are no longer grouped, and continue working with each worksheet individually.
To learn more about shortcuts for Microsoft Excel, check out these additional tips and techniques at TheSoftwarePro.com/Excel.
© Dawn Bjork, MCT, MOSE, CSP®, The Software Pro®
Microsoft Certified Trainer, Productivity Speaker, Certified Speaking Professional
