There are a variety of uses for grouping Excel worksheets that can all save your time and make your task more efficient. This guide will take you through the basics of how to group worksheets in Excel and make changes that apply to all sheets in one go…
Learning some of the basic skills with Excel can help enhance your productivity and efficiency as an Excel analyst, one of those basic skills is how to group the worksheets together.
Why group worksheets in Excel?
- Time saving: This is the big one. Imagine your boss comes along and tells you that your 30 sheet Excel report is great, but could you add in the company name in Cell A1 on all the sheets. If you don’t know how to group the sheets you will be writing that title and organising your data 30 times…learn to group excel worksheets and you only have to do it once!
- Improved accuracy: Whenever you have to repeat a task over and over us humans tend to switch off and mistakes can creep in. Thinking of the example above its quite likely that writing out the company title 30 times could result in the occasional error, especially if your company name is “The Wondrously Quintessential Fantastic Fabric Group”. (note: I’m fully aware you could copy and paste…but not every analyst is!)
How to Group worksheets in Excel
To show you how easy it is to group worksheets in Excel a quick example is to open a new workbook. Unless you have changed the Excel options a new workbook should open up with 3 worksheets as standard these are named Sheet1, Sheet2 and Sheet3. If you have more or less worksheets then you can still follow this example however you will need more than 1!
You can group these three (or however many you have) together in 2 quick ways:
The first way – Select all worksheets together
To select all the worksheets in one group right-click on any of the tabs (Sheet1 or Sheet2 or Sheet3) and select the option “Select All Sheets” from the option box:
This will select all the worksheets in your Excel workbook and make a group of them. You can identify that the sheets are grouped because the tabs which display the sheet names (sheet 1 etc.) will all turn the same colour (in my Excel setup it changes from grey to white when selected). If you only have one worksheet selected then you will notice that sheet is the only one highlighted in white, the rest will still be grey:
Now all your worksheets are grouped any actions you take on the active sheet is repeated on all of the worksheets. So if you type “Test Company 1” into cell A1, then select any of the worksheets, you will see that “Test Company 1” is in cell A1 of each of the sheets:
The Second way – Select individual worksheets to group
Sometimes you may only want to group certain sheets together, for example Sheet2 might be fine and you just want to change Sheet1 and Sheet3.
Open a fresh workbook then press and hold down the control key on the keyboard (the CTRL key which is usually found to the side of the space bar). With the CTRL key pressed you can use your mouse to select the worksheets that you want to group, left clicking on the sheet names one by one, again you will see each sheet name change colour when selected:
As you can see Sheet1 and Sheet3 are highlighted so now any changes that we make to the active worksheet will apply to both Sheet1 and Sheet3 but as we have not selected Sheet2 that will remain as it is.
How to Ungroup the worksheets
Once you have finished any group changes you can ungroup the sheets by clicking on any of the sheet names, for example if you left-click on Sheet3 that will revert to the only highlighted sheet so you know they are no longer grouped. Alternatively you can right-click on any of the sheet names and select the “Ungroup Sheets” option:
Grouping the worksheets is a great technique for saving time but always take care and keep an eye on what sheets are grouped.
It is common to forget some or all of the worksheets have been grouped for a previous task and then continue with developing only to realise your entire workbook has been defaced with changes! Spare your blushes and just keep a close eye on things and avoid this pain.