3D Formulas or References in Excel are a great way to consolidate data from multiple sheets.

3D Formulas reference several worksheets that have the same structure which allows you to consolidate by using the SUM function.

Formula breakdown:


What it means:

SUM(from this Sheet#:up to this Sheet#!return the sum of these cells)

Step 1: Make sure you have a SUMMARY Sheet and several sheets where you want to sum your data from;

Step 2: All Sheets have to have the same structure…so the same number of columns, rows and cells;

Step 3: Enter a SUM formula in your SUMMARY Sheet, preferably in the top left hand corner;

Step 4: With your mouse select the first Sheet you want to consolidate;

Step 5: Hold down the SHIFT key;

Step 6: Whilst holding the SHIFT key, select the last Sheet you want to consolidate with your mouse key;

Step 7: In the formula bar, type in the active cell that you are in (from Step 3) after the ! and press Enter

Step 8: Drag the formula across all your cells.  That’s it!

Note: You can change the Sheet names and this will be reflected in your 3D formula.

You cannot move the positioning of the Sheets after you create the 3D formula as this throw out the formula.


3D Formulas




If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn


Share on Google+


Related Posts

IF Function: Introduction The IF function is probably one of the most used Excel functions because it is easy to understand and very flexible when you apply it to real life situations. Here I will show you a couple of ways that you can use the IF function to get you up and going. DOWNLOAD WORKBOOK ...
Sum the Last 7 Transactions with the Offset Functi... What does it do? It returns a reference to a range, from a starting point to a specified number of rows, columns, height and width of cells Formula breakdown: =OFFSET(reference, rows, columns, , ) What it means: =OFFSET(start in this cell, go up/down a number of ro...
INDEX Function with Data Validation To make the INDEX function in Excel interactive we can add a Data Validation drop down list. By referencing the second argument in the INDEX function - the Row Number - to a data validation list, will allow a user to choose the nth position of a list and return their respectiv...
Evaluate Formulas Step By Step in Excel This is one of the coolest tricks I have seen in Excel, as there are countless times wherein I had a hard time understand formulas. Especially long and complex ones! Excel provides the way to evaluate your formula, and break it down step by step so that you can understand it! ...