Consolidate data from multiple sheets using Excel 3D reference

Sharon maintains the sales register in an Excel workbook. The excel workbook has a sheet for every country that the company operates in and a sheet with the consolidated totals.

To get the totals in the consolidated sheet, she uses the old style of adding cells from all the sheets

=USA!A1 + ‘South Africa’!A1 + ‘France’!A1 + ‘India’!A1

where USA, South Africa etc are the names of the various sheets and A1 is the cell in each sheet with the sales figures

This formula can be quite cumbersome to write if there were a lot of sheets or a lot of cells with similar calculations. When she adds a new country she has to manually change all the formula. Similarly, when she deletes a sheet, all results change to #REF and she has to remove the reference to the sheet from all the formulas.

Watch this video to learn how you can easily write the formula using 3D references, that takes care of all the challenges above.

Leave a Reply

Your email address will not be published. Required fields are marked *