Page 3 / 13

Editing Multiple Sheets at a Time

Often, your workbook has several similar worksheets. Sometimes you want to make the same changes to all of them. For example, you are preparing a report, and you want all the sheets to be stylish and, on the other hand, look the same. Maybe there are up to ten spreadsheets, and you don't have all day to edit each of them separately.

For this common problem, Excel has a so-called group mode. In practice, you select several sheets in the group mode, and the changes you make apply to all of them.

Let's first learn how you can select several sheets to edit, and then we'll practice it.

The Objective

Two new rows (rows 1-2) have been added to all four worksheets January, February, March and Q1 total, and “Sales” has been written to cell A1. The change according to the example is repeated in all sheets. The following picture shows one sheet after the changes were made; in the group mode, such changes directly affect the other sheets in the group as well.

Task: Choose the sheets for the group

Choose the sheets (some also talk about grouping them) January, February, March and Q1 Total. Below we will get to learn two different methods.

Nice

Choosing separate sheets

First, choose the first sheet for the group: January, by clicking on its selector.

After this, add the rest of the sheets (February, March, and Q1 Total) to the active selection by holding down Ctrl while clicking on their selectors.

Choosing successive sheets

First, choose the first table sheet for the group, January, by clicking on it.

Now click on the last table sheet on the row you want to add to the group (Q1 Total) while holding down the Shift key.

Guide

### Selecting all sheets

If you want to select all sheets in one go, right-click on a sheet selector and choose Select All Sheets.

Info: Am I in the group mode?

When several sheets are selected, the text Group appears in the top bar of the Excel window after the file name. This indicates that when changes are made to one of the selected sheets in this mode, the change will be applied to all selected sheets.

Task: Add rows and a title to the sheets

Add two new rows in the beginnings of the chosen sheets, and write “Sales” into the cell A1.

Task: Remove the grouping

Now you have learned how to use Excel groups to select and edit several sheets at a time. Grouping is also one of those features that people don't necessarily know about. The feature in question may not be necessary every day, but sometimes knowing the feature can save you hours or even work days.

Next, we'll look at how calculations such as calculating sums are done between different spreadsheets.

If you are ready, we can move on.

Should we go?

Mark as complete: