All Resources

How to Sum Across Multiple Worksheets in a Financial Model

At Marquee, we are strong advocates of building financial models in a vertical manner.  However, we do realize that there are some instances where building horizontally may also be a reasonable approach.  A simple example of building horizontal is modeling out a mining company’s portfolio of mines.

When trying to aggregate data from a model with a horizontal structure, there are a few approaches to summing up data from each worksheet.  This includes the lesser known variant of the SUM function, which allows you to SUM across multiple worksheets with one simple and short formula.

Using the mining example above, say you wanted to summarize the total production from each mine onto a separate Summary worksheet.  You could do so in the following ways:

  • Method #1: Create one long formula in one cell that adds up each desired cell on each worksheet (NOT recommended)
  • Method #2: Create a summary for the item with a label for each and link the numbers… and SUM (recommended)
  • Method #3: SUM across multiple worksheets (recommended)

For demonstration purposes, let’s assume that there are five mines across five worksheets, and that the worksheets are simply named “Mine1” through “Mine5”.  If the total production for each mine is contained in a different cell on each worksheet (e.g., Cell C25 on “Mine1”, Cell C28 on “Mine2”, and so on), you could use Method #1: create one long formula in one cell on the Summary worksheet to add up the total production.  We would recommend against this though, as the formula can become difficult to audit as the number of worksheets increase, and is prone to error in constructing it:

Instead, we would recommend using Method #2: Repeat and Link the production from each mine in multiple rows/cells on the Summary worksheet, and then simply use the SUM function to derive the total production:

However, if the total production for each mine is contained in the same cell on each worksheet (e.g., Cell C25 on “Mine1”, Cell C25 on “Mine2”, and so on), you could use Method #2 above, or you could use Method #3: create one short formula in one cell on the Summary worksheet to add up total production (please see the end of the article to learn how to create this formula using only the keyboard):

As is typical in Excel, there are multiple ways of arriving at the same solution.  However, there are pros and cons to each methodology.  We have already discussed the disadvantage to Method #1, but have not yet touched upon the differences between Method #2 and Method #3.

In the above example, if the total production for each mine is contained in a different cell on each worksheet, using Method #2 would be our recommendation.  Not only are the formulas simple to audit, but the production from each mine is properly labeled for any user of the model to instantly understand what information is being conveyed.

However, if the total production for each mine is contained in the same cell on each worksheet, it becomes less clear whether Method #2 or Method #3 is superior.  The answer is that it depends on the structure of your model, and also comes down to user preference.

For example, what if the Summary worksheet has the same template as the worksheets that you are summarizing data from?  In this instance, Cell C25 on each worksheet is each mine’s production, and Cell C25 on the Summary worksheet is total production.  Again, you could use Method #2 off to the side of the template (real estate in Excel is indeed free), and then link up the total to Cell C25 in the template.  However, you could also simply use Method #3 in Cell C25 on the Summary worksheet directly – which is easy to understand, more efficient to build, and since you know that Cell C25 is always production throughout the model, is fairly easy to audit.

What we like about Method #3, aside from it being a lesser known method in Excel, is that it offers a very simple, clean, quick, and elegant solution to a situation that many Excel users have been faced with at some point in their model-building endeavours.  Equally as important, it also allows additional flexibility with the way the formula is set up – worksheets can be added/deleted between the two worksheet “bookends”, and the formula will automatically update the total production.  This of course reduces potential model maintenance going forward.

In the formula above for Method #3, =SUM(Mine1:Mine5!C25), the two bookends were “Mine1” and “Mine5”.  To illustrate the flexibility of adding and deleting worksheets, we will use the bookends “Start >>” and “<< End”, as shaded in grey in the screenshot below.

In order to add up all of the production across the five mines, the formula with the “Start >>” and “<< End” bookends would be: =SUM(‘Start >>:<< End’!C25).  There are two quick items to note about this formula:

  • the syntax in the formula now includes single quotations, since the worksheet names have a space in them
  • the Start and End bookends should be devoid of numbers (they should simply just be blank worksheets with a title)

In the example above, if one of the Mine tabs between the Start and End bookends is deleted, the formula will automatically capture this and update the total production figure.  The opposite is also true – if a worksheet is added between the two bookends, the formula will also automatically capture the updated production.  As you can imagine, both Method #1 and Method #2 would require additional maintenance if either of these scenarios were encountered, as the user would then have to manually update formulas to account for both the deletion and addition of worksheets.

If you do decide to implement Method #3 into your model, you must keep the following in mind:

  • the desired data that you are trying to add up must live in the same cell on each worksheet
  • the SUM across multiple worksheets formula will not update if any of the cells on any worksheet move locations (e.g., from Cell C25 to Cell C26).

However, to ensure that the cells being added together do not move locations, you can always use the Protect Sheet function in Excel (Alt + R + PS) in order to ensure that the structure of each worksheet cannot be altered, thereby protecting the integrity of the formula on the Summary worksheet that is summing across all of the worksheets.

While Method #2 (Repeat and Link… and SUM) is versatile and can work in any situation, Method #3 (SUM across multiple worksheets) is most useful when your model has identical templates that also summarizes information into an identical template.  While keeping in mind the caveats of using this variant of the SUM function, it can be a very powerful tool that can help streamline your model and reduce future model maintenance, all while conforming to modelling best practices by ensuring that you lock-down your model as outlined above.

Keyboard Shortcut – SUM Across Multiple Worksheets Formula

The following are the steps to create the formula in Method #3, =SUM(Mine1:Mine5!C25), without using the mouse:

  • Move your cursor to the appropriate cell on the Summary worksheet and type =sum
  • Press the TAB button so that the syntax is automatically corrected to =SUM(
  • Press CTRL + Page Down to go to the “Mine1” worksheet and arrow down to Cell C25
  • Press CTRL + SHIFT + Page Down to highlight each Mine worksheet
  • Upon reaching the “Mine5” worksheet, arrow down to Cell C25
  • Press ENTER to complete the formula and return to the Summary worksheet