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

Vlookup in Multiple Excel Sheets What does it do?Searches for a value in the first column of a table array and returns a value in the same row from another sheet´s column (to the right) in the table array.Formula breakdown:=VLOOKUP(lookup_value, table_array, col_index_num, )What it means:=VLO...
SUMIFS Function: Introduction The SUMIFS function allows you to Sum multiple criteria.For example, you can select one Sales Rep from a list of Sales Reps and select one Region from a list of Regions and return the Sum of those arguments from a Sales list.  See how easy it is...DOWNLOAD WORKBOOK...
IFERROR Function: Introduction If you have a calculation that results in an error like, #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, then you can clean it up by using the IFERROR function which allows you to replace the error it with a 0 or a blank cell.I show you both IFERROR examples below.DOWNLOAD ...
VLOOKUP Multiple Columns What does it do?Searches for a value in the first column of a table array and returns the sum of values in the same row from other columns (to the right) in the table array.Formula breakdown:{=SUM(VLOOKUP(lookup_value, table_array, {col_index_num1,col_index_num2}, ))}...