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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920