Q. I have an Excel workbook that contains many tabs. We have a "Total" tab, but the formulas to sum all of those tabs gets very complicated. Plus, new worksheets get added and the formulas have to be updated. Could you suggest the best way to handle a workbook like this?
A. Assuming the cells you are totaling are in the same place on each tab, there is a much easier way to manage these types of files than summing each individual spreadsheet tab. We often have Excel workbooks where we separate out categories of similar data into separate tabs. However, using formulas on cells in every tab can get very cumbersome. And if you add a new tab, the formula(s) on the combined tab must be manually updated each time. Instead, it is much more efficient to use 3D formulas. You can download an Excel workbook and access an accompanying video with the online version of this article at journalofaccountancy.com.
Note that the content that follows is based on Microsoft Excel 365 for PCs. Other versions of Excel may work differently.
Let's look at an example. In the screenshot below, I have an Excel workbook that contains daily sales data for each of my products on different tabs. I would like to sum up the daily sales amounts for products A, B, C, and E on the Total Product Sales tab.
Most users would use the following formula to complete this task for the Jan. 2, 2022, sales in cell B2 of the Total Product Sales tab:
='Product A'!C2+'Product B'!C2+'Product C'!C2+'Product E'!C2
See the screenshot below.
This isn't too daunting unless you have many tabs. And you also have to update the formula. Let's do this same task using a 3D formula. Again, select cell B2 of the Total Product Sales tab. Type =sum( to start the sum formula. Select cell C2 on the Product A tab, hold down Shift, and click the Product E tab (do not click a cell, just the tab). Close the parentheses and click Enter to finish the formula. See the screenshot below.
You get the same sum as the way we did it originally. So, what are the advantages of using the 3D formula? You only use those few steps whether you have four tabs or 40 tabs. The original way includes selecting all tabs individually. So, if you have many tabs, linking them can be cumbersome. Also, if you add a new tab between your start tab and end tab, your 3D formulas will include the information from the new tab(s). The original way would not include that new information, and you would have to manually edit the formulas on the total tab every time a new tab was added. Let's see how that works.
For comparison purposes, I have shown both the 3D formula (B2) and the original formula (D2) in the first screenshot below. Both show the same daily total sales.
Now I am going to add the Product D tab in between the Product C and Product E tabs. See the screenshot below for the changes to the Total Product Sales tab.
The 3D formula (B2) reflects the new information for product D, but the formula in D2 does not. The formula in D2 would need to be manually updated each time a new tab was added. Note that the new tabs have to appear between the start tab and end tab for them to automatically be updated in the 3D formula(s).
You can access a video demonstration of using 3D formulas in Excel at the bottom of this page.
About the author
Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org.We regret being unable to individually answer all submitted questions.
