Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Relative References when Recording Macros.

Relative References when Recording Macros

by Allen Wyatt
(last updated June 8, 2018)


One of the most common ways of creating a macro is to use the macro recorder built into Excel. The recorder allows you to record your keystrokes and play them back again later. When you record your macros, Excel is very literal about recording what you do. For instance, if you start recording while cell B7 is selected, and then you press the Down Arrow key, cell B8 is now selected.

When you later select cell E12 and play back this macro, you might expect that the macro would move down one cell, to E13, as if you had pressed the Down Arrow key. Instead, when that line of the macro is executed, cell B8 is selected.

The reason this happens is that Excel memorized your absolute steps. It didn't record the press of the Down Arrow key, but instead recorded the movement to cell B8. This exemplifies the default condition of the macro recorder—to record all movements and cell references absolutely.

If you instead want your macros to be recorded relatively (so that the macro moves down one cell instead of moving to cell B8), then you need to instruct Excel to do so. You do this by clicking Use Relative References on the Developer tab of the ribbon. After clicking, all your subsequent actions are interpreted relative to the currently selected cell. Click the tool a second time, and you are back to subsequent actions being interpreted absolutely.

It is important that you remember to click the appropriate tool before you take an action that is recorded. The tool's state (on or off) affects only the recording of future actions, not what has been already recorded.


If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3331) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Relative References when Recording Macros.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...


Setting Line Spacing in the Equation Editor

The Equation Editor can be a great boon when you need to include complex mathematical equations in your document. The ...

Discover More

Displaying Toolbars

Toolbars allow you to access common tasks quickly and easily. Excel provides a wide variety of toolbars, and you can even ...

Discover More

Selecting All Visible Worksheets in a Macro

Do you need your macro to select all the visible worksheets (and just the visible ones)? It's not as easy as it sounds, ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Continuing Macro Lines

Sometimes a macro command line can get very, very long. This can make it hard to understand when you look at it a month ...

Discover More

Finding the Path to the Desktop

Figuring out where Windows places certain items (such as the user's desktop) can be a bit frustrating. Fortunately, there ...

Discover More

Determining the RGB Value of a Color

Excel allows you to fill a cell's background with just about any color you want. If you need to determine the RGB value ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 4?

2017-06-24 04:19:17

Alan Elston

Hi Pete,
I mostly now use the recorder now just to get any tricky syntax which I have forgotten.
It is a good thing to learn to use at an early stage, but can lead to bad habits, - as you say picking out the key parts can be a bit difficult and tedious sometimes if you record too much. - I still have some old code I did with the recorder when I was a beginner, .. and I have never got around to tidying it up and removing all the unnecessary stuff and putting bits into a loop, etc.. ..
I think a good initial lesson in learning VBA would be to learn the recorder, but very importantly also to re write all that the recorder gives in a more efficient form. I think that last point is what is often neglected.

It is annoying when Microsoft change things for no apparent useful reason. Maybe they are just running out of useful new ideas, Lol… :)

Here a few discussions on the macro recorder and relative recording option:


2017-06-23 11:22:24

Pete Zicari

HI, Alan. I think I compressed too many ideas into a short space and gave the wrong impression. I often use the macro recorder to learn how to code up a function; I didn't want to imply it isn't useful or that macros aren't useful -- quite the contrary. But it's only worth the time to build a macro if a task involves many steps or, especially, many repetitive steps. Since the recorder saves every single step, it can become quite a job to select the key parts of the resulting code and wrap one or more loops around them. But the coding would still be that much simpler if the relative-references option were available on the Mac. Indeed, the coding would be much easier if the IDE retained the features it had in Excel 2011! Thanks for the links you provided. I'll look through them!

2017-06-23 04:59:59

Alan Elston

Hi Pete Zicari,
Are you saying that macros are not possible with Excel 2016 for the Mac, or Recording Macros or just Relative References when Recording Macros.
Here some references from Microsoft on macro recording.
At first glance I do not see the relative referencing button, so possibly that is what you are referring to as missing in mac Excel 2016.

I think this following article is suggesting the relative referencing has disappeared with version 2016:
How very strange of Microsoft to do that. ???
But not a lot of people use that in macro recording. I have reminded a few professionals about the relative recording option in macro recording. Possibly a Microsoft software engineer responsible for something in the mac 2016 Excel forgot about the relative recording option in macro recording and so it got left out by mistake!!!

The macro recorder is actually still used , I believe, by many very experienced professionals. It can be very helpful to get a start, or , in particular, to get tricky syntax correct. It can save a lot of time, and avoid having to memorise or keep a record of the almost infinite syntaxes.
I am not sure what you mean by converting a macro recording into something useful. ?? The basic idea is you use it to record the steps of something as you do it manually. Then either, as that recorded code is, or after some modifying of it, it will do that task for you. So whether it is useful or not will depend to some extent on how useful it was what you did manually.
As regards modifying / converting a macro recording to a better code… - Just bear in mind that the recorder simply copies exactly what you do. As humans we need to see and select things. Excel / VBA doesn’t.
You will typically see recordings looking like, pseudo code:-
To improve that you would simply modify / convert it to

In brief, the macro recorder is , in my opinion very useful. It seems that a lot experienced computer programmers find it useful also.

2017-06-22 08:22:45

Pete Zicari

This feature isn't available in Excel 2016 for the Mac -- where Microsoft evidently assumed that the artists and snowflakes who buy Macs wouldn't want to use macros. If you have a lesson on how to convert macro recordings into something that might be useful, I'd love to read it!

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.