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:
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.