Working on Multiple Excel Worksheets – 3 Tricks to know!

  Working on Multiple Excel Worksheets – 3 Tricks to know!

Intro – Working across multiple worksheets at once!

Excel’s workbook can be divided in several worksheets and by design its quite efficient as worksheets can act and work as independent unit and still easily interact in other worksheets or workbooks if required.

Although each worksheet is worked upon separately i.e. one at a time, but need can arise to work on and edit in multiple worksheets simultaneously. Today we are exploring three tricks that can make you Excel multi-worksheet-pro!

Group Worksheets with CTRL key

Hold down CTRL > Select tab and make changes to edit multiple worksheets at once.

This will group the worksheets together and whatever operation or task is performed on active worksheet will affect other worksheets in the group also. Best for formatting multiple worksheets at once if data is similarly structured. Or if you have chunk of data to be deleted from several worksheets at once.

Once done, ungroup worksheets by right-clicking on any worksheet > ungroup worksheet to deactivate.

Know 3D reference system

3D referencing system is a little known feature of Excel and can immensely helpful if you have to perform simple mathematical operations on data from multiple worksheet. Assume you have tens of worksheets and want to add numbers from particular range of cells.

Following is an example with three worksheets and we had to sum sales of January month:

For this I used this formula:

=SUM(‘A:C’!B2)

A:C reference means consider all worksheets between worksheet named “A” and “C” i.e. A, B and C worksheets are considered.

“!” sign is used to refer worksheets and B2 is a cell reference. So Excel is asked to sum B2 cell of all three worksheets.

Bonus: Placement of tab matters!

As worksheet B falls between A and C worksheets, Excel considered B in A:C reference as well. Had the worksheet B not in the middle of A and C, it could have been left:

As you can see when I moved the worksheet B to the right and now A and C worksheets are together, the sum total has reduced from 1110 to 1100.

Use 3D Named range

Liked the 3D Sum? But terrified by the syntax and not sure how to write the formula? Use named range to simplify it. Define a 3D Named range and use it perform operations like SUM or AVERAGE etc.

Using the same concept we learnt in 3D sum, we can define a named range. Its done as following:

Say we want to sum the sales of all three months now from all three sheets:

See how easy it was?

So here you have three tricks up your belt now to deploy anytime if multi-worksheet challenge arises!