Excel® Tips and Tricks

Get the best in Excel® training anytime, anywhere.

Get Excel® Training

Excel Macro: Relative Reference

Excel records Macros in absolute mode by default. In absolute mode, a Macro will start working in the same cell it was in when it was recorded. If, when it was recorded, A1 was the active cell, then it will return to A1 and begin working from there no matter which cell was selected prior to starting the Macro.

Excel Macro Relative Reference - Image 1

When you turn on Relative Reference before recording a Macro, it will start the Macro “relative” to whatever cell is currently selected. This makes the Macro more flexible.

Example:

If you start recording the Macro in Cell A6 and insert a row:

With Absolute Reference – no matter what cell is selected the Macro will always insert a row at Row 6.

Excel Macro Relative Reference - Image 2

VBA Code in Edit Mode:

Excel Macro Relative Reference - VBA Code 1

With Relative Reference – the macro will insert a row relative to the cell selected. If Cell A9 is selected, the macro will insert a row at Row 9.

Excel Macro Relative Reference - Image 3

VBA Code in Edit Mode:

Excel Macro Relative Reference - VBA Code 2

Click the Use Relative Reference button on the Developer tab to toggle relative reference on and off.

Written by

Excel Tips & Tricks is written by the Microsoft® Excel® experts at Fred Pryor Seminars and CareerTrack. Known for our extensive Excel training, we offer some of the best in the business. Don’t waste precious time trying to figure things out on your own. Attend one of our outstanding Excel courses and gain the knowledge you need to use Excel more effectively and efficiently. Check out an Excel course coming to a location near you when you click here.

Leave a Reply

Your email address will not be published. Required fields are marked *