Excel - Excel in Depth 27 - Macro Recording - Episode 1211.127

2,227 views · Published 15 July 2010 · 3:05 · Indexed 16 November 2026

Channel: MrExcel.com · 2010 · Education

Watch on YouTube

Microsoft Excel Tutorial: Excel in Depth 27 - Macro Recording.

Welcome back to the MrExcel netcast! In this episode, we dive into Chapter 27 of Excel in-depth, where we discuss VBA, Macros, and a not-so-great decision made by Microsoft in the Macro Recorder.

Have you ever received a data set that was supposed to be used for a mail merge, but the person who set it up didn't quite understand how it should be formatted? That's exactly what happened to me. But instead of manually fixing all the names, I thought, "Hey, I'll just record a macro!" So I recorded a macro called "Fix One" with a shortcut key of Ctrl+A, thinking it would make my life easier.

But here's the problem with the default state of the Macro Recorder - it literally records every single action you take. So when I pressed Ctrl+A again, it went to cell A2, cut the data, and pasted it in cell A1. And it did the same thing for the next few rows, resulting in a complete mess. I quickly realized that this was not the solution I was looking for.

But fear not, there is a setting that can fix this issue. It's called "Use Relative Reference" and it can be found on both the View and Developer tabs. Once turned on, the Macro Recorder will record actions relative to the starting point, rather than recording the exact actions taken. So instead of going to cell A2, it will go down one cell from wherever you started. This makes the macro much more efficient and accurate.

It's a shame that Microsoft doesn't have this setting turned on by default, as it would save a lot of frustration for users. But don't worry, I've got you covered. In this video, I'll show you how to turn on "Use Relative Reference" and how it can greatly improve your experience with the Macro Recorder. So make sure to watch until the end and don't forget to hit that subscribe button for more Excel tips and tricks. Thanks for tuning in and we'll see you next time for another netcast from MrExcel.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Macro Recording 
(00:10) The issue with Microsoft's Macro Recorder 
(00:21) Attempting to fix the data with a Macro 
(00:41) The problem with the default state of the Macro Recorder 
(01:05) Solution: Turn on "Use Relative Reference" 
(01:48) Why this setting should be turned on by default 
(02:50) Clicking Like really helps the algorithm


Record an Excel macro using relative references

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/1152865/

More from this channel