Relative References when Recording Macros

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 using the Relative Reference tool, on the Stop Recording toolbar. (This toolbar contains only two buttons and appears when you first start recording a macro.) Click the Relative Reference tool, and all your subsequent actions are interpreted relative to the current 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 Relative Reference tool before you take an action that is recorded. The tool's stated (on or off) affects only the recording of future actions, not what has been already recorded.