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 1, 2013)

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

Changing Styles

Styles are a great boon to making your documents look better and making them easier to update. You can change the formatting ...

Discover More

Turning Off Automatic Capitalization

When you start typing, do you ever notice those times that Word starts capitalizing words at the start of what it thinks are ...

Discover More

Store Common Addresses in AutoText Entries

Do you write letters to lots of different people? One good place to keep those addresses is in AutoText entries. They are ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

MORE EXCELTIPS (RIBBON)

Debugging a Macro

Part of writing macros is to make sure they work as you expect. This involves a process known as debugging. Here's how you ...

Discover More

Creating and Naming a Worksheet Using a Macro

You can use macros to make your common Excel tasks easier and faster. For instance, if you routinely need to create new ...

Discover More

Mouse Click Event in VBA

Need to know if a particular cell is clicked with the mouse? Excel has no particular event handler for clicking in this way, ...

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 for this tip:

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. 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 two more than 9?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


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.

Links and Sharing