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:
- 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 all at the same time. When you group worksheets, there is no need to repeat the same steps over and over again.
- Move, copy, or delete multiple worksheets at the same time.
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. Note: Excel 365 simply reads as Group without the brackets.
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 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, MOSM, CSP®, The Software Pro®
Microsoft Certified Trainer, Productivity Speaker, Certified Speaking Professional
2 Comments.
This is great, thank you! Is there any way to group sheets in different workbooks? I want to make an edit in one and have it show up in a different workbooks. I tried linking them, but it makes one of the files take up a ton of storage and slow the computer down.
Hi Sam, The grouping feature is limited to a workbook. Although you can group worksheets in side-by-side workbooks, any actions are only applied to the active workbook. As you guessed, linking is the answer. Although it speeds up updating, you don’t need to have all of the workbooks open to make edits. A file with links, called a destination workbook, can become much larger. Try saving the destination workbook under a different name (same file type). Sometimes this will reduce the file size.
By the way, links are managed under Data > Edit Links. Always good to check here to make sure you are linking to the correct files and don’t have any unneeded links.