In my current and previous FP&A roles, I have been responsible for consolidating inputs from various Finance Business Partners and much of it involves first sharing a template to populate to the Finance Business Partners and then seamlessly integrating submissions on the Consolidated model. Many a times, I had to group worksheets that were linked together and export at the same time so that they continue being linked in the new worksheet. To achieve this, knowing how to group worksheets is a must. If you don’t yet know how to group worksheets, don’t worry, we would be taking you through the steps in todays post. But before we begin, let’s explore the reasons why you may want to group worksheets in Excel
Why Do We Group Worksheets in Excel
- You may want to make make changes (like calculations, formatting, creating standard tables and so on) simultaneously on more than one worksheet, and to save time, knowing how to group worksheets is essential unless you would have to go through each worksheet to make changes.
- You may want to share more than one related worksheet to users in other departments to work on and you don’t want the links to refer back to the original workbook
- You may need to hide various sheets and grouping before hiding may save you the stress of having to right click and hide each sheet
Steps to Group Worksheets in Excel
To group multiple worksheets in excel, just follow these easy steps:
Step 1: Hold down the Ctrl key and select the tabs you want to group
Step 2: Release Ctrl, now you can work on all worksheets simultaneously
Moving Grouped Worksheets
To move grouped worksheets just follow the steps below:
Step 1: Right click on any of the grouped worksheets and select Move or Copy
Step 2: Select the workbook you intend to move these sheets to, click create copy box and then click on Ok.
Result: You should see all the grouped worksheets in whichever workbook you selected in step 2
Ungroup Sheets
Ungrouping sheets is just as easy as grouping them.
Step 1: Right click on one of the grouped sheets and click ungroup sheets
That’s it, you are done. The current sheet should be the only highlighted sheet in the sheet tab.
To ungroup, you can also release the Ctrl key and click on any sheet outside the current group.
Result:
Word of Caution
Please remember that when worksheets are grouped, whatever you do on one of the worksheets is replicated on the other worksheets. You should always ensure to check whether you are working on a grouped worksheet or not before you make significant changes to your model.
How can you check? Well, Microsoft Excel is kind enough to add a “Group” to the name of the workbook. You would also see the sheet tabs highlighted
Even with these cues, I have found myself in situations where I forgot that had grouped some worksheets and then made significant changes to all of them. If you find yourself in a similar situation, don’t panic, just try to undo or check for previous versions of the workbook either under Version History or Manage Workbook and restore to that one
