How to group specific worksheets
The image below shows the income/expense data of a company (for the first quarter).
This company runs three segments. – food, garments, and electronics. in the lookup column.
Within each of these three sheets, we have the same data for all three segments. Now, is the time to calculate the net profit (per month) for each segment.
Simple, let’s make a new column for Net Profit (Income minus expenses).
Don’t do that for each segment individually. Instead, do this.
1. Select the sheets that you want to group.
Here, we only need to select sheets for all three segments.
2. Press down the Control (Ctrl) button and select each of these three sheets.
An alternative shortcut to this. To select consecutive sheets is to hold the Shift button, select the first sheet and then select the lasts sheet.
Doing so selects all the sheets between and including the first and the last sheet.
3. All three sheets are now selected and grouped.
How do you know they are grouped? Navigate to the top of your Excel workbook. You’d see the ‘Group’ suffix to the workbook’s name.
Not only that – but the grouped worksheet tab turned white while the other sheet tabs remain grey.
4. Make any edits to any of these sheets.
For example, write the following formula in Cell E2 of Sheet: Food Segment.
= C2 – D2
This formula translates to ‘Income minus expenses’ for January. Drag and drop the same for the next two months.
Time for some magic? 😄
5. Go to the other two sheets to find the same edits there.
That is how grouping in Excel works!