Most Excel formulas refer to one or more worksheet cells. In this video, learn how to create cell references that change—or don’t change—when a formula is copied to another cell.
- [Presenter] Most Excel formulas will refer to one or more worksheet cells. In this movie, I will show you how to create cell references that change or don't change when a formula is copied to another cell. My sample file is the relative and absolute workbook. You can find it in the chapter five folder of the exercise files collection. This workbook contains a single worksheet and on it I have a growth rate and a base investment amount. What I'd like to do is to calculate the value of the investment as is grows over time, by the growth rate in B1.
To do that, I will multiply the value in cell B2 by one plus the growth rate. So in other words, instead of having $180,000 I will have 105% of $180,000 in cell B3 after the year 2019. So in cell B3, I'll type an equal sign and then I'll multiply the value in B2. So B2 and then an asterisk by in parentheses, one plus the value in B1.
So I'll be multiplying B2 by 105%. Press return. And I get my value. Now let's say that I want to copy the formula down. In other words, I want to get the value for 2020. So I will click cell B3, press command + C to copy, click cell B4, press command + V, and I get a bunch of hashtags, or number signs. And the reason I get that, if we take a look at the formula in cell B4, is that I'm multiplying B3, 188,550, by one plus the value in B2, which is the original investment rather than the growth rate.
So what happened when I copied this formula, is that the cell references changed. You can see that B2, the investment, became B3, the value after 2019, which is correct. But the growth rate, instead of being referred to in cell B1, was changed to B2. So that means I need to edit my formula. I'll press escape to get rid of the selection around B3, press delete, and I'm going to edit my formula in cell B3.
So I click cell B3 and I'll just retype it. So I'll type an equal sign and I'm multiplying B2. I do want the cell address to change, in other words I want to multiply based on B2 then B3 then B4. So I won't edit that. Then I will type an asterisk for multiplication, left parentheses, and then it'll be one plus B1, but now I want to make the reference unchanging, or absolute. To do that, I will press command + T.
And you can see that the reference to B1 now has dollar signs in front of the column and the row. Dollar signs mean that those elements of the reference are unchanging. They have become instead of relative, absolute. If I were to press command + T again, you would see that I now have relative or changing columns and absolute rows. Press it again. Now I have an absolute column and relative row. Command + T again, I'm back to my original both relative reference.
And then command + T again and I'm back to them both being absolute. I'll stay with this. I could have the column be relative because I'm not changing or copying the formula side to side, but what I have here will work. So I'll type a right parentheses and enter. And I get the value I had before. But now, if I click cell B3 and then move the mouse pointer over the fill handle, which is the green square you see at the bottom right corner. I know my mouse pointer is in place when it changes into a black cross hair.
And now I can drag down to cover all the cells down to B11. And I get my values. So as you can see, knowing when to use relative and absolute references makes creating formulas a lot easier.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks
Skill Level Beginner
What you should know1m 11s
1. Getting Started with Excel
2. Managing Workbooks
3. Working with Worksheets, Cells, and Cell Data
4. Sorting, Filtering, and Managing Worksheets
5. Summarizing Data Using Formulas and Functions
6. Formatting Worksheet Elements
7. Working with Charts
8. Working with External Data and Objects
9. Exploring PivotTables
10. Reviewing and Sharing Spreadsheets
Further information1m 2s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.