How to group sheets in Excel
By
SpreadCheaters
Let’s consider the dataset containing the marks of various students in sheets 2, 4 and 5. The data structure in all these sheets is the same and we’ll learn to group these sheets in the following steps.
Have you ever caught yourself in a situation where you have large workbooks containing many worksheets, for example sales data from various branches of the company all over the world, having the same layout and data structure. Imagine if your boss asks you to calculate the revenue of all the sales in every sheet then under normal conditions, you’ll perform all calculations on the sheets one by one. Imagine if there were 100s of such sheets in the same workbook then it would cost you a lot of time to complete the task.
However, Excel has a built-in feature to handle such cases by grouping all those sheets which have the same layout and data structure. The benefits of grouping the sheet are more than just performing one common calculation on all sheets. Few benefits are as follows;
– Add new data column to all sheets at a time
– Delete a column from all sheets at a time
– Print a selection of worksheets from all sheets at the same time
– Setup printing preferences such as Header, footer etc for all sheets
– Correct the same typo mistake on all sheets at a time
Step 1 – Press and hold CTRL key and click on sheets
– The process to group the sheets is very simple and easy. Just press and hold down the CTRL key and keep on clicking those sheets which you wish to group. The sheets don’t need to be adjacent. You can choose any sheets you want.
– All those sheets which you clicked on, while pressing and holding CTRL key will be grouped together.
Step 2 – Perform same calculation on all grouped sheets
– Let’s calculate the total marks of the student on the first sheet of the group. For this, we’ll first calculate the sum of marks obtained on the first sheet only and see if that calculation has been performed on the other sheets or not.
Step 3 – Change a specific value on all grouped sheets
– There is a conditional formatting rule implemented in these sheets based on the Pass Criteria cell value. All those subjects in which a student will acquire more than Pass Criteria marks will be highlighted with green color.
– This time we’ll change the Pass Criteria and see how it affects all sheets.
– We can see that when we changed the Pass Criteria from 55 to 45, it was changed on all grouped sheets and subsequently, conditional formatting of cells was changed as well.
