Microsoft

Use this 3D reference technique to summarize a set of Excel worksheets

Summarizing multiple Excel worksheets can be accomplished several ways, but Susan Harkins has a clever alternative to show you.

Excel users take full advantage of the multiple sheet environment by constructing related sheets. For instance, you might maintain an individual sheet for sales or inventory by the month - one sheet per month. Then, you add a final sheet to summarize the months. If you know ahead of time that you're going to do this, everything works fine. If, on the other hand, you insert sheets after the fact, you might run into difficulty - updating all those summarizing formulas and functions to include the data on the new sheet(s)!

Excel's Consolidate feature could be helpful, but it would be time-consuming - plain tedious -in a complex summary sheet with oodles of complex formulas and references. However, once you've set it up, adding a new sheet would be simple enough.

Another solution

Fortunately, there's another solution. It's not foolproof, but it's close, and requires far less setup than Consolidate. This solution calls for two new sheets. You'll add one to the beginning of your sheet list and name it First. You'll add a second to the end of the sheet list, but before the summary sheet, and name it Last. Then, you'll update your formulas using the following referencing syntax:

'First:Last'!cellreference

When you add a new sheet anywhere in-between First and Last, the 3D summarizing references will automatically include the new sheet.

Now, let's apply this solution to the simple sheet setup shown below. The first two sheets, Farm1 and Farm2, track the number of animals on each farm. The third sheet, Summary, uses the following (relative) formula in column B to sum the animals on both farms:

=SUM(Farm1!B2,Farm2!B2)

2013142.JPG

Now, what happens if you add a third sheet for a third farm - Farm3. You could modify each formula in Summary to reflect the new sheet, and in this case, doing so would be easy. You'd modify the formula in cell B2 and copy it to the remaining cells in column B and you're done. You know, though, that most summary sheets are not so accommodating!

2013143.JPG

When the summary sheet presents more work than our simple example sheet, you can employ the First:Last solution as follows:

Insert a sheet before the first sheet in the workbook. In this case, that's Farm1. Right-click Farm1's tab, choose Insert, and double-click Worksheet (on the General tab).

Rename the new sheet First.

Right-click the Summary sheet's tab and insert a new sheet. Name it Last. The summary sheet doesn't have to be the last sheet in the workbook, but the sheets you're summarizing must fall between First and Last. To move a sheet, right-click its tab, choose Move, and continue as appropriate for your needs.

After adding the two boundary sheets, modify the summarizing functions on your summary sheet. In this case, select cell Summary!B2 and replace the Farm1 and Farm2 sheet references with the 3D reference 'First:Last': =SUM(First:Last!B2). Then, copy it to the remaining cells in column B.

2013144.JPG

2013145.JPG

Updating all the functions and formulas might be tedious, but you'll only have to do it once, as long as you insert new sheets you want summarized between First and Last. The 3D reference will evaluate all sheets between First and Last - even those sheets you add after entering the 3D references.

A sample Excel worksheet is provided as a tutorial to further explore this technique.

Also read:


About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

1 comments
Barry Goldman
Barry Goldman

These fixes are all clever and sometimes innovative and I've learnt a lot of neat tricks from this column. 

But somewhere along the line one must migrate from spreadhseets to databases - which handle these sorts of problems automatically.