- 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
Understanding Cell Referencing
Each cell, the intersection of a column and row on a worksheet, has a unique address, or cell reference, based on its column letter and row number. For example, the cell reference for the intersection of column D and row 4 is D4.
Cell References in Formulas
The simplest formula refers to a cell. If you want one cell to contain the same value as another cell, type an equal sign followed by the cell reference, such as =D4. The cell that contains the formula is known as a dependent cell because its value depends on the value in another cell. Whenever the cell that the formula refers to changes, the cell that contains the formula also changes.
Depending on your task, you can use relative cell references, which are references to cells relative to the position of the formula, absolute cell references, which are cell references that always refer to cells in a specific location, or mixed cell references, which use a combination of relative and absolute column and row references. If you use macros, the R1C1 cell references make it easy to compute row and column positions.
Relative Cell References
When you copy and paste or move a formula that uses relative references, the references in the formula change to reflect cells that are in the same relative position to the formula. The formula is the same, but it uses the new cells in its calculation. Relative addressing eliminates the tedium of creating new formulas for each row or column in a worksheet filled with repetitive information.
Absolute Cell References
If you don’t want a cell reference to change when you copy a formula, make it an absolute reference by typing a dollar sign ($) before each part of the reference that you don’t want to change. For example, $A$1 always refers to cell A1. If you copy or fill the formula down columns or across rows, the absolute reference doesn’t change. You can add a $ before the column letter and the row number. To ensure accuracy and simplify updates, enter constant values (such as tax rates, hourly rates, and so on) in a cell, and then use absolute references to them in formulas.
Mixed Cell References
A mixed reference is either an absolute row and relative column or absolute column and relative row. You add the $ before the column letter to create an absolute column or before the row number to create an absolute row. For example, $A1 is absolute for column A and relative for row 1, and A$1 is absolute for row 1 and relative for column A. If you copy or fill the formula across rows or down columns, the relative references adjust, and the absolute ones don’t adjust.
3-D References
3-D references allow you to analyze data in the same cell or range of cells on multiple worksheets within a workbook. A 3-D reference includes the cell or range reference, preceded by a range of worksheet names. For example, =AVERAGE(Sheet1:Sheet4!A1) returns the average for all the values contained in cell A1 on all the worksheets between and including Sheet 1 and Sheet 4.