In this simple guide, we are going to run through the benefits, and steps to group worksheets in excel. Also known as tabs, this guide will explain how to turn multiple sheets into a single one for the purposes of editing.
We will cover:
- What are the advantages of grouping worksheets in excel?
- The steps taken
- Examples of the output
- Ungrouping the worksheets when finished
Advantages of grouping worksheets
When you are working with a lot of data in excel that follows a consistent format and structure, grouping worksheets can be a massive time saver when looking to make updates to each sheet.
Some tasks that can be completed across multiple worksheets at once include:
- Creating formulas
- Adding the same data to multiple pages at once
- Setting up formatting such as colour coding, font changes and so on
- Cleaning incorrect data formatting when multiple pages contain the same errors
As you can see, there are so many tasks that if you were to work through tab by tab in a large document would take a very long time, but by simply ensuring that the files are structured in a consistent way, you can save a ton of time by simply grouping the worksheets and making the changes from there.
How to group worksheets in Excel
To group worksheets, there are a couple of different approaches depending on how many sheets your document has.
The simplest way to group worksheets is simply through the use of the CTRL key on your keyword.
First, open up the first tab you want to to be part of the newly formed grouped sheets, and while holding down the CTRL key, simply click on the name of the next tab like so:
As you can see, both sheets are now highlighted!
Alternatively, if you have a wide range of sheets all in order that you want to group, you can start by clicking on the left most tab, and holding down the SHIFT key, select the furthest right key, which will group the selected sheets and all of those that sit between them.
Finally, the third and easiest approach to grouping worksheets when you have a large document where you are looking for every single sheet to be grouped together, is to simply right click on any of the sheet names, and click on the Select All Sheets button. Easy!
Now that we have run through the steps on how to group worksheets in excel, lets look at a couple of quick examples of these benefits in action.
Examples of editing multiple sheets at once
First, lets look at creating a formula across two sheets. Lets say we are tracking yearly revenue and expenses, and want to quickly calculate how much profit we have made each month of each year. Rather than going into each tab and creating the formula manually each time, we can simply type the formula in once on the first page, drag it down through all of the months, and it has now been calculated for the following year too:
Creating Formula on First sheet:
Formula already calculated on second sheet:
Secondly, let’s look at a quick formatting change. When creating a report to be sent out to potential customers or clients making it look nicer can make the document more readable. In this example, we have dragged down the table by a few rows, and added a nice header – which as you can see has been copied over to the second tab of the report as well. Note the 2020 and 2021 parts of the header are created separately as individual formatting tweaks.
Same header copied over to the 2021 sheet:
How to ungroup worksheets when finished
Now that we have made all of the required changes to multiple sheets, lets look at how to ungroup these worksheets so we can go back to making individual changes where required and prevent any accidental workbook wide tweaks.
There are two main ways to do this.
First, simply take the opposite approach we used with CTRL when grouping them in the first place. Hold down the CTRL key and one by one click on the sheets you want to ungroup from the selection.
Alternatively, you can right click on any of the grouped sheets and select Ungroup Sheets which if you have grouped a high number of sheets can be a nice time saver as opposed to clicking them all one by one.
This sums up up guide on how to group multiple worksheets in Excel. As we have covered, this can be a super handy time saver when working with large documents where we want to ensure consistent changes across multiple pages at once.
For more handy guides on working with Excel, be sure to check out our Excel Tips page.