How to group and ungroup worksheets in Excel

This tutorial will teach you how to group worksheets together in Excel to get the ability to modify multiple sheets at a time.

Have you ever found yourself in a situation when you need to perform the same tasks on multiple sheets? That's very easy to do with the Group Worksheets feature. If your sheets have the same layout and structure, just group them together, and any changes you make on one sheet will be automatically applied to all other worksheets in the group.

Advantages of grouping worksheets in Excel

When you are working with a set of identically structured sheets, grouping them together can save you a lot of time. Once the worksheets are grouped, you can enter the same data, make the same changes, write the same formulas and apply the same formatting to all the worksheets at once without having to switch through different sheets and edit each one individually.

Here are just a few examples of what you can do to a group of worksheets:

  • Add new or edit the existing data on several worksheets at a time.
  • Perform the same calculations with the same regions and cells.
  • Print out a selection of worksheets.
  • Set up the header, footer, and page layout.
  • Correct the same typo or mistake on multiple sheets.
  • Move, copy, or delete a group of worksheets.

In the screenshot below, we are setting up a table with the same data, formatting and layout for the 4 grouped worksheets: East, North, South and West.
Grouping worksheets in Excel

How to group worksheets in Excel

To group sheets in Excel, hold down the Ctrl key and click the sheet tabs of interest one by one. After clicking the last tab, release Ctrl.

To group adjacent (consecutive) worksheets, click the first sheet tab, hold down the Shift key, and click the last sheet tab.

For example, here's how you can group two worksheets:
Two worksheets are grouped

Once the worksheets are grouped, you can edit them all in one go. Also, you can perform calculations that will automatically reflect on all the worksheets in the group.

As an example, suppose we want to calculate the amount of commission based on the commission percentage (column C) and sales (column D) on the following sheets: East, North, South and West.

Here's the fastest way:

  1. Group the 4 sheets.
  2. Enter the below formula in cell E2, and copy it down through cell E5:

Done! The formula will appear on all the grouped sheets in the same cells.
Calculating grouped worksheets

Note. Clicking any unselected tab will ungroup the worksheets.

How to group all worksheets in Excel

To group all the worksheets in a workbook, this is what you need to do:

  1. Right-click any sheet tab.
  2. Choose Select All Sheets in the context menu.
    Grouping all worksheets in Excel.
Note. When all the sheets in a workbook are grouped, switching to another sheet tab will ungroup the worksheet. If only some worksheets are grouped, you can browse through the grouped sheets without ungrouping them.

How do you tell if worksheets are grouped in Excel?

There are two visual signs of grouped worksheets in Excel:

The sheet tabs in a group have a white background; the sheet tabs outside the group appear in gray.
Grouped sheet tabs appear in a white background.

The word Group is added to the name of the workbook; as soon as the worksheets are ungrouped, it disappears.
The word Group is added to the name of the workbook.

How to ungroup worksheets in Excel

After you've made the desired changes, you can ungroup the worksheets in this way:

  1. Right-click any sheet tab in the group.
  2. Choose Ungroup Sheets in the context menu.
    Ungroup worksheets in Excel.

Or you can simply click any sheet tab outside the group to ungroup tabs.

That's how to group and ungroup worksheets in Excel. I thank you for reading and hope to see you on our blog again next week!

You may also be interested in:

5 Responses to "How to group and ungroup worksheets in Excel"

  1. MMohamed says:

    I have an Excel file, this file includes 4 sheets, each sheet includes a data in few columns.
    My question is;
    How can I join a particular column from every sheet with the same data in another column in another sheet, then another column from another sheet etc.
    For example; there is a column entitled in every sheet by ``voltage'', I want to join all columns to be one column.
    Many thanks

  2. Davidlag says:

    Thanks for the post!

  3. farhad says:

    Anyway, you are so beautiful

  4. Yin says:

    Thank you for your post.

  5. Scarlett.M says:

    Great! I know how to group up the excel cells but now learn how to group sheets in excel. I work at designproficient and manage lots of projects. These tips will surly help me with the work.

Post a comment

Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
60+ professional tools for Excel
60+ professional tools for Excel
2019–2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Sheila Blanchard