Have you ever had to sum the same cell across multiple sheets? This often occurs where information is held in numerous sheets in a consistent format. For example, it could be a monthly report with a tab for each month (see screenshot below as an example). Show Watch the video
I see many examples where the user has clicked the same cell on each sheet, putting a “+” symbol between each reference. If there are a lot of worksheets, it takes a while to click on them all. Also, if the sheet names are long, the formula starts to look quite unreadable. The screenshot below shows an example of this type of approach. The formula in cell C5 is: =Jan!C5+Feb!C5+Mar!C5+Apr!C5+May!C5+Jun!C5+ Jul!C5+Aug!C5+Sep!C5+Oct!C5+Nov!C5+Dec!C5 How do you know if you’ve clicked on every worksheet? What if you happened to miss one by accident? There is only one way to know – you’ve got to check it! The chances are that you don’t need to do all that clicking. And just think about the time you will waste if there is a new tab to be added. The good news is that there is another approach we can take that will enable us to sum across different sheets easily. I still remember the first time a work colleague showed me this trick; my jaw hit the ground in amazement. I thought he was an Excel genius. That’s why I’m sharing it here; by using this approach, you can look like an Excel genius to your work colleagues too 🙂 To sum the same cell across multiple sheets of a workbook, we can use the following formula structure: =SUM('FirstSheet:LastSheet'!A1)
With this beautiful little formula, we can see all the worksheets included in the calculation just by looking at the tabs at the bottom. Take a look at the screenshot below. All the tabs from Jan to Dec are included in the calculation. The formula in cell C5 is: =SUM(Jan:Dec!C5) SUM across multiple sheets – dynamicWe can change this to be more dynamic, making it even easier to use. Instead of using the names of the first and last tabs, we can create two blank sheets to act as bookends for our calculation. Take a look at the screenshot below. The Start and End sheets are blank. By dragging sheets in and out of the Start and End bookends, we can sum almost anything we want. The formula in cell C5 is =SUM(Start:End!C5) We can also create sub-calculations. For example, we could add quarters as interim bookends too. There is the added advantage that the tabs also serve as helpful presentation dividers. The example below shows the calculation of just Jan, Feb, and Mar sheets. The formula in cell C5 is: =SUM('Q1:Q2'!C5) Excel could mistake Q1 and Q2 as cell references; therefore, adding single quotes around the sheet names is essential. Which other formulas does this work for?This approach doesn’t just work for the SUM function. Here is a list of all the functions for which this trick works.
The drawbacksThere are a few things to be aware of:
About the author Hey, I’m Mark, and I run Excel Off The Grid. My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began. In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love). Do you need help adapting this post to your needs? I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs. But, if you're still struggling you should:
What next? How do I create a formula in Excel to pull data from another sheet?To pull data from another sheet by using cell references in Excel:. Click in the cell where you want the pulled data to appear.. Type = (equals sign) followed by the name of the sheet you want to pull data from. ... . Type ! ... . Press Enter.. The value from your other sheet will now appear in the cell.. |