Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you record a macro in Excel, Excel records the cells that you click, and it remembers the addresses so it can't affect the same cells the next time you run the macro. It is possible, however, to record a macro that uses what are called relative references. In this movie, I will show you how to do both: to affect the specific cells that you click and to remember how far you moved in the worksheet using relative references. As a quick recap, a formula that contains an absolute reference to a cell won't change when you copy it to another cell, but a formula with a relative reference will.
For example, take the two formulas that I have here in cells D2 and E2. The cell in D2 uses absolute references, and you can see that I have dollar signs next to the column letter and row number, which means that regardless of how I copy the formula it will not change. So, for example, if I were to click cell D2, Command+C to Copy and Command+V to paste, I paste the exact same formula, again because of the absolute references. And I just press Escape to remove the selection from D2.
The formula in cell E2 uses relative references, as you can see here. I will just simply have the column letter and row number for each of the two references, so when I press Command+C to copy and Command+V to paste, I get the number 51, and the reason when you look at the formula, is that it is now finding the sum of B3 through B5. So in another words, this formula, which I copied from above with absolute references, finds the total for B2 through B4. The formula here finds the total for B3 through B5.
So in other words, when I copied the formula and moved it down one cell by pasting it, it moved all the references down one as well, so instead of summarizing these three cells, it summarizes these three cells. Now there are some times you want that to happen and some times you don't. So in this movie, I am going to show you how to do both: record a macro with absolute references and with relative references. To do that, I will go to Sheet2, where I have my data laid out in a series of cells that are two columns to the right and two rows below the previous value: so, A1, C3, E5, and G7.
I am going to record a macro where I will make the value in cell C3 18 point and change the font of the value in cell E5, and I am going to start with cell A1 selected. And I am going to record the macro in two ways: the first with absolute references and the second with relative. So again I have displayed the Developer tab of the Ribbon by going to the Ribbon page of the Excel Preferences dialog box. So I will just click there, and now I can start recording my macro. I want to record it using absolute references, so I don't need to make any changes right now.
I'll click Record, and I'll type in the macro name of Absolute, click OK, and I am ready to go. First thing I am going to do is select the cell C3, which is two rows below and two columns to the right of the current active cell. And then on the Home tab, I'm going to change the font size to 18. Now I am going to click cell E5 again two below and to the right of the previous selected cell, and I am going to change the font to Cambria. Okay.
So we have that change. I am done making my changes, that's all I want to record, so I will go back to the Developer tab and click the Stop button. Okay. So I will undo my changes by pressing Command+Z, and I will click cell A1, and I am going to record the same macro, but this time I am going to use relative references, and I do that by selecting the Relative Reference button. When the Relative Reference button is highlighted, Excel keeps track of the offset from the previous cell that you selected when it records a macro, instead of the actual address of the cells.
So I am back in cell A1, I am ready to record, and again I am just going to do exactly the same actions. I will call this relative and press Return, which is equivalent to clicking OK in that dialog box. Go back to the Home tab, click cell C3, change the font size to 18, and then in cell E5 I am going to change the font to Cambria. That's all I want to do. And I will stop recording. And then I will undo my changes by pressing Command+Z and Command+Z, and I will click cell A1 again.
Now I am going to run both of the macros to show you how they are different. The first macro I will run will be the absolute macro, so instead of starting with cell A1 selected, I am going to start with cell C3 selected and then run the absolute macro to show you how it affects the worksheet. I will click macros, and then I want to run Absolute, click Run, and Excel affects the exact two same cells. I will now change the formatting of these cells back. If you affect a worksheet using a macro, you can no longer press Command+Z to back up your changes.
You can't undo them, in other words. So that means that you need to undo them by hand, so I will change the cell's formatting back to Calibri and the cell's formatting so that the font is 12 point. Great. I still have cell C3 selected. So let me show you what happens when I run the relative macro. On the Developer tab, click macros and click Relative - that's the macro I want to use - and I will click Run. When I do, you will see that instead of formatting cell C3 in 18 point and E5 in a different font, when I record it using relative references, Excel said, "Oh! Well, this is the starting cell, and then I need to move two rows and two columns to the side, so I will affect that one, and then I will move again and affect this cell." So that's the difference between relative and absolute references.
If you want to record a macro and you know the exact address of the cells you want to affect, you should use absolute references. If the cells you affect are always the same distance apart, you should use relative references. In the event you need to mix the two techniques as part of a longer procedure, you should record separate macros for the elements that require one or the other technique.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 76701 Viewers
80 Video lessons · 130883 Viewers
52 Video lessons · 64776 Viewers
59 Video lessons · 50605 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.