Excel 3D reference: refer to the same cell or range in multiple worksheets

This tutorial explains Excel's powerful 3D referencing feature, enabling efficient data manipulation across multiple worksheets. Learn how to reference identical cells or ranges across selected sheets, and build formulas for aggregating data from various worksheets – for instance, summing the same cell across numerous sheets using a single formula. This offers a streamlined alternative to Excel's Consolidate function.

Excel's 3D References: A Deep Dive

A 3D reference (also known as a dimensional reference) in Excel simultaneously refers to the same cell or range of cells across multiple worksheets. This significantly simplifies calculations across worksheets with consistent structures.

Consider this scenario: you have monthly sales reports spread across four sheets:

To find the grand total (summing subtotals from each sheet), you could manually add each sheet's subtotal:

=Jan!B6 Feb!B6 Mar!B6 Apr!B6

However, with numerous sheets, this becomes cumbersome. A 3D reference simplifies this:

=SUM(Jan:Apr!B6)

This formula efficiently sums cell B6 across all sheets between "Jan" and "Apr".

Tip: To prevent cell reference changes when copying formulas, use absolute references (e.g., =SUM(Jan:Apr!$B$6)).

You can also directly sum cell ranges:

=SUM(Jan:Apr!B2:B5)

For product-wise sales totals, create a summary table mirroring the monthly sheet order, and use this 3D formula (in cell B2, for example):

=SUM(Jan:Apr!B2)

(Use relative references – no '$' – for automatic adjustment when copying down.)

Excel 3-D Reference Syntax:

First_sheet:Last_sheet!cell or First_sheet:Last_sheet!range

Excel 3-D Formula Syntax:

=Function(First_sheet:Last_sheet!cell) or =Function(First_sheet:Last_sheet!range)

All sheets between First_sheet and Last_sheet are included in calculations. Note: Not all Excel functions support 3D references (see the complete list below).

Creating and Managing 3-D References

Creating a 3-D Reference:

1. Select the formula cell.
2. Begin with =FunctionName(, e.g., =SUM(
3. Shift-click the first and last sheet tabs.
4. Select the desired cell or range.
5. Complete the formula and press Enter.

Adding New Sheets: 3D references are dynamic. Inserting a new sheet within the reference range automatically includes it in calculations.

Named 3-D References: For easier management, create a named range:

1. Go to Formulas > Defined Names > Define Name.
2. Enter a name (e.g., "my_reference").
3. Define the 3D reference (e.g., =Jan:Apr!B:B).
4. Click OK.

Now use =SUM(my_reference) for simplified calculations.

Compatible Excel Functions

The following functions support 3D references: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, TEXTJOIN, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, VARPA.

Impact of Sheet Modifications

Modifying sheets affects 3D references based on the starting and ending sheets (endpoints).

• Inserting/Moving Sheets Within Endpoints: New sheets are included.
• Deleting/Moving Sheets Outside Endpoints: Sheets are excluded.
• Moving Endpoints: The reference range adjusts accordingly.
• Reversing Endpoints: One endpoint is excluded; restoring the original order doesn't restore the original range.
• Deleting Endpoints: The reference endpoint shifts to the adjacent sheet.

Conclusion

Excel's 3D references provide a powerful and efficient method for managing and calculating data across multiple worksheets. Mastering this feature significantly streamlines your workflow.

The above is the detailed content of Excel 3D reference: refer to the same cell or range in multiple worksheets. For more information, please follow other related articles on the PHP Chinese website!