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)

4

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

MORE FROM ALLEN

Pasting Multiple Paragraphs Into a Single Cell

Copying information from one program (such as Word) to another (such as Excel) is a common occurrence. If you want to paste ...

Discover More

Extracting Street Numbers from an Address

Want to know how to move pieces of information contained in one cell into individual cells? This option exists in using ...

Discover More

Spreading Out a Table

If someone sends you a worksheet that has lots of data in it, you might want to "spread out" the data so you can have some ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Pulling Apart Characters in a Long String

You can easily use formulas to pull apart text stored in a cell. For instance, if you need to pull individual characters from ...

Discover More

Adjusting Values with Formulas

Paste Special is a great tool that allows you to modify the values in a range of cells in your worksheets. You may want, ...

Discover More

Inserting Worksheet Values with a Macro

Macros are often used to process information in a worksheet. You may need your macro to change the values stored in cells; ...

Discover More
Subscribe

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

View most recent newsletter.

Comments

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 6 - 0?

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:
https://www.excelforum.com/the-water-cooler/1097141-i-need-a-macro-that-is-so-simple-it-could-be-written-with-the-macro-recorder-8.html#post4195741
https://www.excelforum.com/the-water-cooler/1097141-i-need-a-macro-that-is-so-simple-it-could-be-written-with-the-macro-recorder-10.html#post4620960

Cheers,
Alan


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.
https://support.office.com/en-us/article/Create-or-delete-macro-in-Excel-2016-for-Mac-5bd3dfb9-39d7-496a-a812-1b5e8e81d96a
https://support.office.com/en-us/article/Enable-or-disable-macros-c2494c99-a637-4ce6-9b82-e02cbb85cb96
https://support.office.com/en-us/article/Run-a-macro-in-Excel-2016-for-Mac-957ba274-66cd-432f-ae30-120e9ad7115f
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:
https://excel.uservoice.com/forums/304933-excel-for-mac/suggestions/10323087-bring-back-relative-references-in-macro-recording
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:-
Something.Select
Selection.DoSomething
To improve that you would simply modify / convert it to
Something.DoSomething

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


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
Subscribe

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.