Excel Summarize Data From Multiple Sheets

Excel

Left click on the Jan sheet with the mouse. Hold Shift key and left click on the Dec sheet. Now select the cell C3 in the Dec sheet. Add a closing bracket to the formula and press Enter. Your sum formula should now look like this. =SUM(Jan:Dec!C3) The formula will sum up C3 across each of the sheets Jan to Dec. You can also use this technique with other formulas like COUNT or AVERAGE etc. Press the Enter key to complete your formula. You will end up with a formula that looks something like =SUM (Sheet1:Sheet12!B2). This tells Excel to sum cell B2 on sheets 1 through 12. You can now copy the formula just like any other formula to finish totaling all of the cells. HOW THE RANGES WORK.

Microsoft Excel provides the ability to sum across multiple worksheets even if the cell references in these sheets differ. We'll look at 2 methods in this tutorial. To sum across multiple worksheets when the cell references are the same, see this tutorial for a great shortcut formula.

○ This tutorial contains affiliate links. Read our disclosure policy to learn more. ○

If new to linking data in Excel, see our overview tutorial. As a review, the worksheet that brings in data from other sheets is the destination worksheet and the sheets that provide the data are the source worksheets. A cell from a different worksheet is referenced: SheetName!CellReference.

Link and Sum Example

Now, you are ready to summarize the results from all the four worksheets – East, North, South and West. Summarizing Data in Multiple Worksheets. The following steps will show you how to summarize data from multiple worksheets. Add a worksheet and name it Summary. Create the structure for Summary worksheet. The built-in Excel Consolidate feature can summarize data from different sheets, but it cannot combine sheets by copying their data. For this, you can use one of the merge & combine tools included with our Ultimate Suite for Excel. Combine multiple worksheets into one with Copy Sheets. These are transactional data sheets, which means the supplier name will be repeated throughout the data. To summarise the total spend per supplier, we will use the SUMIFS function. In this simplified example, the sheets only have the two rows of data shown in Figure 1. SUMIFS (Sumrange,Criteriarange1,Criteria1,).

In our example, we have a Totals worksheet and 3 city sheets. We are writing the formula to sum the 'Units' sold for Item #400. As each store sells a different mix of products, Item #400 resides in different cells in their worksheets.

We show two different ways of creating the sum link formula. If you'd rather write code than click and jump between worksheets, use Method #1. If you do not like typing formulas, use Method #2.

  • 1. Method #1: Writing the Formula Manually
  • 2. Method #2: Using the Mouse and Keyboard

As each store sells a different product mix, the item's data resides in a different cells in each sheet: B5 for Vienna, B4 for Toronto, and B6 for London. We'll show two different ways of creating a link formula to sum the data in these disparate cells.

Note: Link formulas reference actual worksheet names; not the titles of a worksheet. Often they are the same, so be aware. {See Managing Workbooks and Worksheets for information on renaming worksheets and more.)

Method One: Write Formulas Manually

This method uses the SUM function of Excel. The SUM function adds the arguments listed inside the parentheses; e.g. =SUM(number1,number2,...). Study the image below.

  1. Open the workbook containing the source and destination worksheets, and format any cell that will contain a link formula.
  2. In a text editor like Notepad, create a SUM function: =SUM( ). Inside the parentheses type the cell references separated by commas (see example below). Recall that the format is SheetName!CellReference.
  3. Copy and paste the SUM function into the destination cell and press Enter.

Excel Chart With Multiple Data

The link formula in our example is:
=SUM(Vienna!B5,London!B6,Toronto!B4)

The link formula in our example is
=SUM(Vienna!B5,London!B6,
Toronto!B4)
.

To create link formulas to total data from other columns, copy the formula you just created, change the cell addresses, and paste into the destination cell.

If the source worksheet name has a space or special character, it must be wrapped in single quotes. For example: ='New York'!B7. (Note: Excel automatically wraps any worksheet named C or R in single quotes for internal reasons.)

Method Two: Use the Mouse and Keyboard

You can use the mouse and keyboard to create the link formulas as detailed in the instructions below. Always format the cell containing the link formula before beginning.

Summarize
  1. Open the Excel workbook containing the worksheets.
  2. In the destination worksheet, click in the cell that will contain the link formula and type an equal sign, but do NOT press Enter (figure 1 below).
  3. Go to the first source worksheet (Vienna), click in the cell that contains the data to link (B5) and squiggly lines will surround it (figure 2). Press Enter.
  1. Excel returns to the destination sheet (Totals) and highlights the cell below the link formula we just created in B6 (figure 3). The formula's value, thus far, displays.
  2. Click in the cell holding the link formula (B6). In the formula bar, place the cursor at the end of the formula and type a + sign, but do NOT press Enter (figure 4).
  1. Go to the next source worksheet (London), click in the Units cell (figure 5), and press Enter. Excel returns to the destination sheet, having updated the formula value (24) and the formula itself: =Vienna!B5+London!B6.

Excel Chart From Multiple Sheets

  1. Repeat steps 5 and 6 for the rest of the worksheets to be included in the link formula.

Summary

Data

To learn how linking formulas are affected when the worksheets reside in different workbooks, or if worksheets and workbooks are moved, see our overview tutorial How to Link Excel Spreadsheet Data. That tutorial also discusses steps to take to insure that automatic calculation is turned on so the destination worksheet is always updated if the data in any source worksheet changes.


We hope our Linking Worksheet Data tutorial has been helpful. Cheers!

↑ Return to the top

You probably know that you can use the Sum function to add up a range of cells. But did you know that you can specify a range that covers multiple tabs (worksheets) in your Excel workbook?

EXAMPLE

Let’s say you have a workbook that lists the month’s sales of each of your products. There is a row for each product and a column to shows sales for every location. Each month has its own tab in the workbook. In addition to the totals on each tab, you can easily enter a formula that will total the numbers from all of the worksheets (months).

Go to the tab that will hold the totals and click on the cell you want to first total to appear in. Now click the AutoSum button to start building your formula. Click on the tab for the first month you want included in the total but don’t select a cell yet. Instead, hold down the shift key on your keyboard and click on the last tab you want included in the total. Release the shift key. Both tabs and all the tabs in between will be highlighted. Now click on the cell(s) you want to have totaled. Press the Enter key to complete your formula. You will end up with a formula that looks something like =SUM(Sheet1:Sheet12!B2). This tells Excel to sum cell B2 on sheets 1 through 12.

You can now copy the formula just like any other formula to finish totaling all of the cells.

How Do You Summarize Data From Multiple Worksheets In Excel

HOW THE RANGES WORK

Creating Multiple Sheets In Excel

In the formula above Sheet1:Sheet12! designates a range from Sheet1 through Sheet12. This range refers to the actual arrangement of the tabs in your workbook and is not affected by the numbers (1 and 12) that are part of the sheet names. For example, if you moved the tab named Sheet6 so it was to the right of Sheet12, it would not be included in the sum. Or, if you renamed Sheet4 as Sheet44, it would still be included in the sum as long as it was positioned between Sheet1 and Sheet12.

Excel Summarize Data From Multiple Sheets Spreadsheet

And of course you can do the same thing with other functions. This isn’t limited to Sum.

Excel Summarize Data From Multiple Sheets


If You Liked This Post: