In any introductory Excel class training you are taught to sum (add) rows and columns. That’s called a one or possibly a two dimensional formula. In the real world (outside a class) that technique is insufficient to build robust Excel models. In my Excel class we also learn about Excel 3-dimensional formulas. A three dimensional formula sums cells across multiple worksheets. This Excel technique is called data consolidation.
An example of a 2-dimensional formula is to add expenses by month (by row and by column). An example of a 3-dimensional formula is to add expenses by month by department, or to add expenses by month by region. In other words, if you view your data “by category by category” it’s probably 3-dimensional. As you build Excel models, a little planning goes a long way. By spreading data out across multiple sheets you build more robust models that simplify the task of consolidating data by month, quarter, or year.
Download the lesson workbook here.
After opening the workbook note the presence of 5 worksheets labeled Jan, Feb, Mar, Blank, and totals. Every sheet is structured so that USA travel always appears in cell B4 (there are other data consolidation techniques to consolidate data across sheets when USA travel is not always in cell B4, more on that technique in a future Excel blog.
To add USA travel for January through march, we always begin a formula in Excel by selecting the cell where you want the answer to appear. Select the totals worksheet, then select cell B4. Now click the autosum button. Autosum does a lazy search, looking for numbers to add, but doesn’t see any. Click on the Jan worksheet tab to activate that sheet, then click on the number in cell B4. To add cell B4 for February and march, press and hold the shift key, click on the blank sheet tab, then release the mouse, and then release the shift key. Note the formula in the formula bar reads =sum(Jan:Blank!B4)
The 3-dimensional formula reads as sum cell B4 across the range of sheets from Jan to blank. The most common mistake I’ve seen in 12 years of teaching, is that your next reflex is to click on the totals sheet to see the answer. That would create a circular reference so you’ll want to resist that temptation. To finish the formula (commit it) press either enter or ctrl enter (Ctrl Enter accepts the edit without navigating down one row). Autofill or copy and paste the formula to cells C5, D5 and E5.
Shift clicking to select a range of sheets is called grouping. The benefit of grouping sheets is that anything you type or format will appear across all selected (grouped) sheets. When you finished the formula by pressing the enter or Ctrl+Enter keys you should ungroup the sheets. Before continuing, verify that only one worksheet tab is white (selected). To ungroup a multi-sheet selection either select a worksheet outside of the group, or right click any sheet in the group and select the ungroup command. If you leave the sheets grouped, many Excel features are not available, like database features (sort/filter/subtotal), insert features (chart/table/etc.), and review features.
If you are wondering what the bank sheet is for, it’s a placeholder that will allow the calculations on the Totals worksheet to expand as you add more sheets. Imagine now April comes and you update the model by duplicating the January sheet and renaming it. A keyboard shortcut to make a copy is to ctrl +drag an object. You can learn about other cool Excel keyboard shortcuts here
While holding the Ctrl key, drag the Jan worksheet between the Mar and Blank sheets. Release the mouse before you release the Ctrl key. To rename the Jan (2) sheet Apr double-click the tab text, type Apr, and commit the edit by pressing the enter key. Now look at the formulas on the totals sheet, they’re updated to include the Apr data, how cool is that!
I encourage you to add comments that improve the clarity of this free excel tutorial blog. You can find announcements about our free Excel training tutorials here.
