Pinterest Pixel

Consolidate with 3D Formulas in Excel

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

Download Excel Workbook
John Michaloudis
Posted on

Overview

Consolidate with 3D Formulas in Excel | MyExcelOnline Consolidate with 3D Formulas in Excel | MyExcelOnline

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)

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;

Consolidate with 3D Formulas in Excel

 

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

Consolidate with 3D Formulas in Excel

Consolidate with 3D Formulas in Excel

Consolidate with 3D Formulas in Excel Consolidate with 3D Formulas in Excel

Consolidate with 3D Formulas in Excel

 

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

Consolidate with 3D Formulas in Excel

 

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

Consolidate with 3D Formulas in Excel

 

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;

Consolidate with 3D Formulas in Excel

 

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

Consolidate with 3D Formulas in Excel

 

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

Consolidate with 3D Formulas in Excel

Consolidate with 3D Formulas in Excel

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 workbook3D-Formulas.xlsx

 

 

 

If you like this Excel tip, please share it
Consolidate with 3D Formulas in Excel | MyExcelOnline Consolidate with 3D Formulas in Excel | MyExcelOnline
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

See also  Add 10 Days From Today in Excel: Quick Date Guide

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...