- Introduction
- Understanding Formulas
- Creating a Simple Formula
- Creating a Formula Using Formula AutoComplete
- Editing a Formula
- Understanding Cell Referencing
- Using Absolute Cell References
- Using Mixed Cell References
- Using 3-D Cell References
- Naming Cells and Ranges
- Entering Named Cells and Ranges
- Managing Names
- Simplifying a Formula with Ranges
- Displaying Calculations with the Status Bar
- Calculating Totals with AutoSum
- Performing One Time Calculations
- Converting Formulas and Values
- Correcting Calculation Errors
- Correcting Formulas
- Auditing a Worksheet
- Locating Circular References
- Performing Calculations Using Functions
- Creating Functions
- Creating Functions Using the Library
- Calculating Multiple Results
- Using Nested Functions
- Using Constants and Functions in Names
This chapter is from the book
This chapter is from the book
This chapter is from the book
Using 3-D Cell References
If you want to analyze data in the same cell or range of cells on multiple worksheets within a workbook, use a mixed 3-D reference. For example, =SUM(Sheet3:Sheet6!A1:A10) returns the sum for all the values contained in the range of cells A1 through A10 on all the worksheets between and including Sheet 3 and Sheet 6. 3-D references work with the following functions: AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVPA, VAR, VARA, VARP, and VARPA. However, 3-D references cannot be used with array formulas, the intersection operator (a single space), or the implicit intersection. If you move, insert, or copy sheets between the ones included in the range, Excel adds the values from the sheets in the calculations. If you move or remove sheets between the ones included in the range, Excel removes the values from the calculation.
Create a 3-D Cell Reference
Click a cell where you want to enter a formula.
Type = (an equal sign) to begin the formula.
Type the function you want to use followed by a ( (left bracket).
Type the first worksheet name, followed by a : (colon), and then the last worksheet name in the range.
Type ! (exclamation).
Type or select the cell or cell range you want to use in the function.
Type ) (right bracket).
Click the Enter button on the formula bar, or press Enter.
)
)