Web Analytics


In this guest article, Joseph Reese shows us some Excel formulas to help us summarize data across worksheets. Find out more about Joseph at the end of this post.

In this article you will discover a neat way to summarize data across Excel sheets without having to reference each of them. This technique will work even if you add a new sheet.

Defining a Range of Sheets

Consider the following example: The fictitious expense reports for the equally fictitious XYZ Widget Company’s various offices. Each sheet in the workbook contains the expense data for each of the company’s offices. Below you can see the expense data for the New-York office:

The Expenses workbook holds a similar sheet for the Chicago and LA offices.

Now, let’s create a total expense report in the worksheet called ‘Country Wide Expenses’. The sheet already exists, but we have to actually fill it with data:

We’ll use the SUM function to add the contents of the corresponding cells on each sheet by doing the following:

  • We’ll select cell B5
  • Then we’ll type in =SUM(
  • We’ll click on the NYC Office worksheet tab and Excel will add ‘NYC Office’! to the formula in the formula bar
  • We’ll then click on cell B5 in the ‘NYC Office’ sheet and B5 will be added to the formula, so now, the formula bar will show: =SUM(‘NYC Office’!B5
  • Finally, while holding the Shift key, we’ll click on the ‘Chicago Office’ tab; this will insert a range of sheets into the formula which will be shown in the formula bar in the following way:

=SUM(‘NYC Office:Chicago Office’!B5

  • We’ll finish up the formula by closing the parenthesis and pressing the Enter key

Excel will return to the ‘Country Wide Expenses’ sheet, and this is what we get:

What we did here was to create a range of cells across sheets. Pretty neat isn’t it?

Excel uses the colon sign – : – as the range marker; if put between two cell references, Excel will consider the reference to be for all the cells in the range. For example, A1:B3 contains 6 cells: A1, A2, A3, B1, B2 and B3. But when the colon sign is found between worksheet names, the range covers all the sheets between the names, so in our case, the range includes cell B5 on all the office expenses sheets (as we selected the first and last one).

Drag to Fill

All that’s left is to use the same formula to fill up the table:

  • We’ll grab the fill handle and drag it to the right so it will cover the 12 month columns of the table
  • Then we’ll grab the fill handle again and drag it down so it will cover the whole table

And so we’re done in about a minute of work:

Summary

In this article we’ve dealt with using ranges across worksheets which is a very powerful but rarely used technique. It is also important to remember that this technique is not limited to the SUM function; it works with every Excel function that handles ranges. You can use it to average data, find the lowest or the highest number across sheets and much more.

About the Author: Joseph Reese is the founder of Excel-Formulas.com an Excel consulting firm.Head over to his website if you want to improve your performance with Excel.

About Rich

Rich is the owner and creator of Windows Guides; he spends his time breaking things on his PC so he can write how-to guides to fix them.

Free PC tips by email

Search Windows Guides




Comments

One thought on “Excel Formulas for Summarizing Data across Worksheets”

  1. Michelle says:

    Great example. I love the auto fill function Excel. Very interesting use of range of sheets in a formula and very powerful. Is this assuming that NY is the first sheet, Chicago is the last sheet and LA is located in between those? (there is no visual for this)

Comments are closed.


Computer tips in your inbox
Sign up for the Windows Guides newsletter to get PC tips and access to free Windows books (More details)

Subscribe now
Popular Guides

See which sites have been visited on your PC (even if private browsing mode is used)

Create a Windows 7 System Repair Disc

Best Free Anti-malware

Hibernate vs. Sleep vs. Shut-Down

i3, i5, and i7; Dual, Quad, Hexa Core Processors. How to they Differ?

Intel's Ivy Bridge Processor: new Features

Windows Guides on Facebook