How to Group Worksheets in Excel

By 2 months ago

When your Excel workbook contains numerous sheets with the same layout and structure, combining comparable sheets can help you save time. To be more explicit, once you understand how to group worksheets in Excel, you can make changes to matching cells in all of the grouped sheets by modifying just one of them.

If you modify the row height and width for one sheet, the row height and width for the grouped sheets will also change.

Why Would You Want to Group Worksheets in Excel?

You can perform several tasks on grouped sheets, such as:

  • Modify or add data and insert formulas.
  • Print them at the same time.
  • Move, copy or delete information simultaneously.

Because Excel doesn’t enable you to add several sheets at once, you could group 10 sheets together, right-click on them, and choose Insert from the menu.

How to Group Worksheets in Excel

To demonstrate how to group worksheets in Excel, we’ll use the following example.

Assume you’ve separated your sales crew into three groups based on the products they offer, and you’ve collected sales data for each group. The sales data is divided into three worksheets, each of which contains data for a single product. You want to compute each employee’s commission without having to manually enter calculations on many sheets.

You may group the spreadsheets instead of calculating commissions on each one separately.

  1. ress-hold the Ctrl button.
  2. Click on the sheets you want to group. The grouped sheets turn white, while ungrouped sheets continue to appear grey. You’ll see the word Group added to the title bar when you’re in one of the grouped sheets.
  1. Add the formula to calculate the commission for one employee (column C, row 2), preferably in the first row, so that you can drag the formula into subsequent cells.

Notice that we’ve grouped all three sheets since we’d like to apply the changes to all sheets. 

  1. Select the cell you just entered the formula in and drag the border selection to apply the formula to the entire cell range. Doing so will apply the same changes to all the grouped sheets.
  1. Verify that the changes also appear in other grouped worksheets. If you did everything correctly, all three sheets should have their D column populated with the commission amount due to the employees.

How to Group All Worksheets in Excel?

If you’re working with a workbook with a lot of worksheets, selecting each one separately could take a long time (and possibly an aspirin). Instead, you can use Excel’s Select All Sheets option to group all of your sheets at once.

All you need to do is right-click on any one sheet tab and tap Select All Sheets

Note: Navigating between worksheets with this option automatically deselects (or ungroups) all worksheets, whereas with the previous approach (where we selected individual worksheets), you may flip between sheets without ungrouping them.

How to Move, Copy, Delete, Print, or Hide Grouped Worksheets

You may do a variety of Excel operations on grouped sheets, including moving, copying, and hiding sheets. It usually operates in the same way as regular worksheets do.

Move or Copy Grouped Worksheets

Once you’ve grouped the worksheets:

  1. Right-click on one of them and select Move or Copy.
  1. Select the target book from the drop-down and select the position where you want to move or copy the grouped sheets. If you want to copy, select the Create a copy checkbox at the bottom and then select OK.

Delete Grouped Worksheets

You can also delete grouped sheets the same way. When the worksheets are grouped, right-click on one of the grouped sheets and select Delete.

Print Grouped Worksheets

When the worksheets are grouped, press Ctrl + P. You’ll now see the Print options. Scroll down to Settings and select Print Active Sheets.

Before you select Print, look at the preview on the right to make sure you’re printing the correct sheets. Once you’ve confirmed this, select Print

Hide Grouped Worksheets

You can also hide grouped sheets at once. 

  1. Start by grouping the sheets.
  2. From the top ribbon, select Home, and select Format from the Cells group.
  1. Once you select Format, you’ll see a drop-down menu. Select Hide & Unhide > Hide Sheet.

This will hide all the grouped sheets.

How to Ungroup Worksheets in Excel

If you want to ungroup all the grouped worksheets, just right-click on any worksheet and select Ungroup Sheets

If you haven’t grouped all worksheets in the workbook, clicking on any of the ungrouped worksheets will also ungroup the grouped worksheets.

If you want to ungroup a few worksheets, press-hold Ctrl and click on the sheets you want to ungroup. 

Save Time by Grouping Worksheets in Excel

You can often save time by grouping worksheets instead of copying and pasting formulas across sheets or performing other repetitive operations. Excel, on the other hand, is a feature-rich tool with plenty of shortcuts to make things easier. If you’re working on a workbook with a lot of sheets, for example, there are numerous ways to swiftly flip between them.

Share
asktechnicians

Recent Posts