MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2020: Improve Your Macro Recording

November 09, 2020 - by Bill Jelen

Excel Improve Your Macro Recording. Photo Credit: Manuel Sardo at

I loved the 1985 version of the Macro Recorder in Lotus 1-2-3. The code was hard to understand, but it worked. There are some defaults in the Excel Macro Recorder that cause misery for anyone trying to record macros. Here are three tips to make the macro experience possibly better.

Tip 1: Turn on Relative Reference for Every Macro.

Say that you start in A2 and record a simple macro that moves to A4. I would call that macro MoveDownTwoCells. But if you run this macro while the cell pointer is in J10, the macro will move to cell A4. This is rarely what you want to have happen. But you can change the behavior of the macro recorder by selecting View, Macros, Use Relative References before you record the macro.

There is a Macros drop-down on the View tab. The third choice is called Use Relative References.

If you record the MoveDownTwoCells macro with this setting turned on, the macro will work from any cell.


The setting stays turned on only until you close Excel. Tomorrow, you will have to turn it back on again.


What if you actually need to record a macro that always jumps to cell A4? Even with Relative References enabled, you could press F5 for Go To and use the Go To dialog to go to A4. The macro recorder will record code that will always jump to A4.

Tip 2: Use Ctrl+Shift+Arrow to move to the end of a data set.

Say that you want to add a total at the bottom of yesterday‘s invoice register. You want the total to appear in row 9 today, but tomorrow, it might need to be in row 17 or row 5, depending on how many rows of data you have.

Find a column that is reliably 100% filled. From the top of that column, record the action of pressing Ctrl+Shift+Down Arrow. Press the Down Arrow key one more time, and you will know you are in the row where the totals should be.

If this is a CSV file, you will always be in cell A1 when it is open. Press Ctrl+Shift+Down Arrow in the macro will get you to the last row with data today.

Tip 3: Type =SUM(E$2:E8) instead of pressing the AutoSum button

The macro recorder will not record the intent of AutoSum. When you press AutoSum, you will get a sum function that starts in the cell above and extends up to the first non-numeric cell. It does not matter if you have Relative References on or off; the macro recorder will hard-code that you want to sum the seven cells above the active cell.

Instead of using the AutoSum icon, type a SUM function with a single dollar sign before the first row number: =SUM(E$2:E8). Use that formula while recording the macro, and the macro will reliably sum from the cell above the active cell all the way up to row 2, as shown below.

With numbers in E2:E8, you would normally use a formula of =SUM(E2:E8). However, if you record the macro to use =SUM(E$2:E8), the formula will work for any size data set.

Title Photo: Manuel Sardo at

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.

Bill Jelen is the author / co-author of
Excel Insights – A Microsoft MVP Guide to the Best Parts of Excel

There are fewer than 100 Excel MVPs worldwide. 24 of them have contributed to this book. Written, edited, reviewed and printed by Excel MVPs, this is practical Excel passion undiluted, with each MVP highlighting some of their favorite topics.