When we are working with a set of identically structured worksheets, grouping them together can save you a lot of time. You can quickly perform tasks on multiple worksheets at the same time by grouping worksheets together. If you group a set of worksheets, any changes made on one worksheet are made in all the other worksheets in the group in the exact same location.
Grouping worksheets works best if the worksheets already have identical data structures. This article shows how to group and ungroup worksheets in Excel.
Group Worksheets
Our workbook contains three empty worksheets i.e. North, East and South region. Each worksheet contains revenue of items sold in each quarter in given region. To group worksheets,
- Hold down CTRL and click the sheet tabs of the sheets you want to group.
- Release CTRL.
- All the selected worksheet tab has white background as shown in below figure.
Now you can edit multiple worksheets at the same time. For example, on the North sheet, change the value of cell B1, C1, D1 and E1 as shown in the below rightmost figure. Go to the other worksheets and you’ll see that these worksheets have been edited as well.
Now, suppose you want to add the same formula to cell B7 on both the North and South worksheets. When grouped, the change will happen on both worksheets when either worksheet is modified. For example, a formula change to the North worksheet is automatically made to the South worksheet.
To group all the worksheets in a workbook
- Right-click any sheet tab.
- Choose Select All Sheets in the context menu.
Ungroup Worksheets
If you don’t want to apply specific tasks to a particular set of worksheets, to ungroup selected worksheets.
- Press and hold the Ctrl key
- Click the worksheet tabs you want to ungroup.
To ungroup all worksheets
- Right-click any worksheet tab in the group.
- Click Ungroup Sheets.