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)

Consolidate with 3D Formulas in Excel | MyExcelOnline
STEP 1: Make sure you have a SUMMARY Sheet and several sheets where you want to sum your data from;

3D Formulas


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

3D Formulas

3D Formulas

3D Formulas 3D Formulas

3D Formulas


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

3D Formulas


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

3D Formulas


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;

3D Formulas


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

3D Formulas


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

3D Formulas

3D Formulas

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.






Click here to access these FREE Excel courses!

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