Knowing how to sum across multiple worksheets is a necessity when dealing with large data sets, especially when you need to consolidate results into a single worksheet. In this post, we would take you through the quickest way to sum across multiple worksheets. But first, Let’s find out why knowing this could prove extremely useful in your financial modeling journey
Why Know How to Sum Across Multiple Worksheets?
I believe the most obvious reason is convenience. Imagine you have about 50 worksheets and you need to consolidate the results of all these sheets into a single sheet, it would be very laborious going into each one of these worksheets to add up the numbers. As an analyst, it is always great to equip yourself with skillsets that can help you save time at work in order to allow some free time to focus on personal and developmental goals. In this scenario, knowing how to sum across worksheets saves you an easy 20 -30mins of your time.
The second major reason is for presentation purpose. Your model looks more professional and easy to read with short and precise formulas. Summing across worksheets with our guide below would make your model neater and more professional.
Guide to Summing Across Multiple Worksheets
In our sample data for today, we have prepared simple budgets for different parties that would be thrown over the course of a year at MBT Inc. Our mission (should we choose to accept), is to consolidate all budgets into a single workbook, and to have a consolidated budget worksheet that sums budget estimates across all individual budget worksheets.
Yes it may be tricky, but we accept the mission. Let’s begin:
Step 1: To successfully pull this off, we need all individual budget worksheets to have the same structure. So if you are just getting started, you can prepare one template and duplicate by copying the same sheet multiple times. Please see our post on Worksheet operations in Excel for how to copy worksheets.
Step 2: Designate one of the recently copied sheets as the consolidated sheet or whichever name you prefer.
Step 3: Identify the first and last worksheet you intend to sum up in the array of same structure worksheets you just arranged, and note their names down on notepad application or a physical notebook because you would need them for the subsequent steps.
Kindly note that the first and last sheet can’t be the name of the consolidated worksheet (or whichever worksheet you have specified in step 2 above). In our workbook, the first individual budget worksheet is “CEO Awards” and the last is “Valentines”, so we note it down somewhere.
Step 4: Select the consolidated sheet (or whichever sheet you specified in step 2) and edit the formula below with the actual first sheet and last sheet names you took note of in step 3.
As stated in step 3, the FirstSheet name is CEO Awards whilst our LastSheet name is Valentines . All we need to do is to tweak the formula above by replacing the FirstSheet and LastSheet with your actual first sheet name and last sheet name.
Also, please note that because our first sheet name contains a space, we must enclose both sheet names in single quotes for excel to recognize them as sheet names. If there is no space between any of the sheet names (for example if the sheet name CEO Awards was written as Ceoawards instead,) then we do not need to include the single quotes at the beginning and end of the sheet names.
Step 5: Copy and paste the formula to other cells that you want to add up. For the sake of our sample data, other cells we need to add up are Cells D9:D10, D13: D16, D19:D22 and finally cell D25. Kindly note that we didn’t anchor the column or row of D8 because we intend to copy and paste the formula to other cells.
All done! If you are still confused, just follow the animated guide above. I am also uploading the budget template below in case you need something to practice with or need the easy to use template. Hope to see you in other posts, bye.