قالب وردپرس درنا توس
Home / Tips and Tricks / How to group worksheets in Excel

How to group worksheets in Excel



  The Microsoft Excel logo.

If you are editing multiple worksheets in Microsoft Excel, it can be useful to group them together. Allows you to make changes to the same cell range on multiple sheets. Here's how to do it.

Grouping multiple worksheets in Microsoft Excel

Grouping worksheets in Excel can be useful if you have an Excel workbook with multiple sheets that contain different data but follow the same layout.

The example below shows this in action. Our Excel workbook, called "School Data", contains multiple worksheets regarding the operation of a school. Three of the worksheets have lists of students for different classes, called "Class A", "Class B" and "Class C".

 An Excel workbook with similar worksheets from

If we group these worksheets, all actions that we perform on one of these worksheets are applied to all of them.

Suppose, for example, that we want to insert an IF formula in column G (cells G4 to G12) on each worksheet to determine whether students were born in 1998 or 1999. If we group the worksheets before we insert the formula, we can apply it to the same cell range on all three worksheets.

RELATED: How to use logical functions in Excel: IF, AND, OR, XOR, NOT

To group worksheets together, hold down the Ctrl key and press click on each worksheet that you want to group at the bottom of the Excel window.

 Three worksheets selected at the bottom of an Excel window. [19659005] Grouped worksheets are displayed with a white background, while unselected worksheets are displayed in gray.

The example below shows the IF formula that we proposed above in the "Class B" worksheet. Thanks to worksheet grouping, the same formula was also inserted in cells G4 to G12 on the "Class A" and "Class C" worksheets.

 An Excel IF formula applied to multiple grouped worksheets.

If we further change one of these cells – for example, by adding a second set of formulas to column H – the change is applied to all grouped worksheets at the same time.

Group all worksheets in Microsoft Excel

When you hold down Ctrl, you can select multiple individual worksheets and group them. However, if you have a much larger workbook, this is not practical.

If you want to group all worksheets in an Excel workbook, you can save time by right-clicking on one of the worksheets at the bottom of the Excel window.

Click here "Select all sheets" to group all your sheets.

 Click

Ungrouping Worksheets in Microsoft Excel

After you have finished editing multiple worksheets, you can ungroup them in two ways.

The fastest method is to right-click on a selected worksheet at the bottom of the Excel window and then click on "Ungroup Sheets".

 Click on

You can also ungroup individual sheets one by one. Hold down Ctrl and then select the sheets that you want to remove from the group. Worksheet tabs for which you specify the grouping are restored to a gray background.


Source link