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:

SUM(Sheet1:Sheet4!A1)

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;

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.

DOWNLOAD EXCEL WORKBOOK

3D Formulas

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

 

 

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

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}, ))}...
Index Match 2 Criteria with Data Validation We can use the INDEX-MATCH formula and combine it with Data Validation drop down menus to return a value based on 2 criteria. This is a little advanced so you will need to drop what you are doing and really focus.  Let's go... First we need to convert our data into an Excel...
Vlookup in an Excel Table 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 column (to the right) in the table array. Formula breakdown: =VLOOKUP(lookup_value, table_array, col_index_num, ) What it means: =VLOOKUP(thi...
Summarize Data With Dynamic Subtotals What does it do? It returns a Subtotal in a list or database Formula breakdown: =SUBTOTAL(function_num, ref1) What it means: =SUBTOTAL(function number 1-11 includes manually-hidden rows & 101-111 excludes them, your list or range of data) ***Go to the bottom...