Cell references are often used when creating Excel worksheets and writing formulas.
This lesson will discuss the different types of cell references, such as absolute and relative. We will also look at examples of each one.
Relative, Absolute and Mixed
A key element of a formula is the cell reference, and there are three types:
The type of cell reference is important when writing a formula, since each behaves differently when copied or moved to another cell. This lesson will first discuss what is meant by a cell reference. Next, you will learn the differences between the three types and look at examples of using each one.
Understanding Cell References
Cell reference means the cell to which another cell refers. For instance, if in B2 you have =B3, cell B2 is referring to cell B3. The cell reference in this example is B3. Confused? Let me explain.If we look at a formula such as =sum(B2:B10), the cell reference is B2:B10. The formula is telling Excel to sum or add all of the numbers starting in cell B2 through cell B10.
Sometimes the cell reference may be referred to as the cell range because most references in a formula refer to multiple cells within a range. Okay, now that we understand cell references, let’s take a detailed look at each type.
When you enter a cell reference or range into a formula, by default, the reference is relative. And, no, I am not referring to a member of the family. Remember in the beginning of the lesson when I told you that each type behaves differently when copied to other cells? Relative cell references, when copied to another cell or across multiple cells, will change based on where you copy them. Let’s use an example.We have a sample worksheet with income, expenses and profit numbers for five different stores.
Column B is titled Income, column C is titled Expenses, and column D is titled Profit. The formula in D3 for Store 1 calculates the profit. The formula is =sum(B3 – C3). This is a very basic formula that takes the income and subtracts the expenses.
Imagine you want to copy the formula in D3, down the column for the other stores. When you copy the formula, the cell references will automatically change to reference the new row, row four, five, six and so on. Instead of =sum(B3 – C3), you will get =sum(B4 – C4). Relative references are especially convenient whenever you need to repeat the same calculation across multiple rows or columns.
So, there may be times when you don’t want the cell reference to change when copied to another cell or range of cells. This is where absolute cell references come in handy.
How do you tell Excel that the cell reference needs to be absolute? Well, that part is easy – just add a dollar sign before the letter and number. For instance, =sum($B$3 – $C$3). Unlike relative references, absolute references do not change when copied.
You can use an absolute reference to keep a row or column constant. Let’s use it in an example.Let’s go back to our sample spreadsheet with the five stores. Because all five stores have great profit numbers for the last quarter, the GM for each store will get a 3% bonus.
A column has been created to add the formula that will calculate the bonuses. The formula will take the profit number in column D and multiply it by the percentage number in E1, like this: =sum(D3 * $E$1).Using the absolute cell reference, $E$1, the formula will always use 3% as the multiplier for the bonus. Since D3 is relative, it will change. As a result, we can copy the formula down the row to the other stores and calculate the bonuses for each GM. Now, you may be asking yourself, why we wouldn’t just enter 3% directly into the formula? For instance =sum(D3 * .03).
Why do we need to reference another cell?Well, imagine you use the spreadsheet for each quarter. And next quarter, the bonus may change to 5%! Using the absolute cell reference, you need only to change the one number (the bonus percentage in E1) and the formulas will automatically update. Otherwise, you will need to edit every formula each quarter.
Cell references that are mixed are just what the term implies – part relative, part absolute.
For example, you could have a cell reference such as A$2. This would mean that A is relative and will change; however, 2 is absolute and does not change. Here are three rules to consider when you copy or move cell references:
- $A$2 – this would mean that the column and row do not change.
- $A2 would mean that the column does not change.
- A$2 means that the row does not change.
You will generally use the $A$2 format when creating formulas that contain absolute references. The other two formats are used much less frequently.
This lesson discussed the different types of cell references in Excel.
You learned that cell references can be relative, absolute or mixed. The type you use in a formula is important, as each behaves differently when copied or moved. You learned that relative cell references change based on where you copy them and that absolute references do not change. You can also have a cell reference with both types, or mixed. Keep the different types in mind the next time you are working with cell references and/or formulas.
Once you’ve finished with this lesson, you should have the ability to:
- Describe the three types of cell references in Excel
- Explain how each type behaves when copied or moved