26 3-D References

3-D References

An Excel cell reference that refers to the same cell or range on multiple sheets is called a 3-D reference. A 3-D reference, sometimes referred to as 3-D formula, is a useful and convenient way to reference several worksheets that follow the same pattern and contain the same type of data—such as when a user consolidates budget data from different departments in their organization.

3-D references work brilliantly when a user desires to consolidate data into a summary sheet. For example, monthly sales data can be entered on separate sheets, then consolidated via 3-D formulas on a summary sheet that reference each monthly sheet.

Creating a 3D reference is straightforward once you understand the structure of your workbook. Here’s a simple guide to setting up a 3D reference:

1. Identify the Range: Determine which cell or range of cells you want to reference. Make sure this cell or range is consistently used across all worksheets for accurate results.
2. Initiate the Formula: Click on the cell where you want to display the result of your calculation. Begin typing the formula that will incorporate the 3D reference, such as =SUM( or =AVERAGE(.
3. Select the Worksheet Range: While still in the formula bar, click on the first worksheet tab that you want to include in the reference. Then hold down the Shift key and click on the last worksheet tab to include. This action selects all worksheets between and including the first and last tabs clicked.
4. Complete the Reference: After selecting the worksheets, select the cell or range of cells in the formula bar that you wish to calculate across the selected sheets. For example, if calculating the sum of values in cell B3 across sheets “Jan” to “Dec”, the completed formula would look like =SUM(Jan:Dec!B3).
5. Enter and Apply: Press Enter to complete the formula. Excel will now calculate the sum, average, or other aggregate measures of the cell or range across the selected worksheets.

In the workbooks below, a small business has monthly expenses in separate worksheets (Jan, Feb, Mar) that the manager would like to aggregate into a summary sheet, named Q1. Each sheet has the identical structure, but many of the cells that represent variable expenses, differ from month-to-month, while other fixed expenses stay consistent from month-to-month. The Q1 sheet below is ready for some 3-D references to consolidate data from sheets Jan, Feb, and Mar.

In cell B4 of the Q1 sheet the following formula will be inserted: =SUM(Jan:Mar!B4)

After using AutoFill to copy the formulas to the rest of columns B:F and rows 4:12, the Q1 worksheet now appears on the right. Any changes to the data in the Jan, Feb or Mar sheets will be reflected in the Q1 sheet.

3-D references are a powerful feature in Excel that allow for dynamic data management and analysis across multiple worksheets. By learning how to create and manage these references, users can significantly streamline their workflow, ensuring that their calculations are both efficient and accurate.