Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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 22, 2017)


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 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.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3331) applies to Microsoft Excel 2007, 2010, and 2013. 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. ...


Numbering Equations

Word allows you to easily number a variety of elements in your document. Not so with equations; there is no automatic ...

Discover More

Blank Page Printing after Table at End of Document

When you print, do you get an extra blank page printed at the end of the document? It could be because of the final ...

Discover More

Saving Personalized Copies of a Document

Need a series of documents that include an individual's name or a company name? Here's a handy little macro that will ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Macro Runs Slowly, but Steps Quickly

When you have a macro that processes a huge amount of data, it can seem like it takes forever to finish up. These ...

Discover More

Calculating the Distance between the Top of the Window and Row 1

Normally Excel positions a UserForm in the center of your screen. You may want to position the form elsewhere, more ...

Discover More

Understanding the Select Case Structure

One of the powerful programming structures available in VBA is the Select Case structure. This tip explains how you can ...

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 three more than 8?

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.