Excel in Depth - Macro Recording: Episode 1244

Microsoft Excel Tutorial: Mastering Macro Recording in Excel: Avoiding Mistakes and Using Relative References. Welcome back to the MrExcel netcast! In this episode, we will be diving into Chapter 27 of Excel in Depth, where we will be discussing the topic of Macro Recording. This is a crucial skill to have in Excel, but unfortunately, Microsoft made a bad decision when it comes to the Macro recorder. But don't worry, we will show you how to work around this issue and make the most out of your Macro recordings. Have you ever received a data set that was supposed to be used for a mail merge, but the names and addresses were all over the place? This can be a frustrating situation, but fear not, we have a solution for you. In this video, we will be using a classic example where someone sent me a data set with names in Column A, addresses in Column B, and city, state, and zip in Column C. However, the data was not set up correctly and needed to be fixed before it could be used for a mail merge. My initial thought was to record a Macro to fix all the names and addresses quickly. I recorded a Macro called "Fix One" with a shortcut key of [ctrl + a] and stored it in the workbook. However, when I ran the Macro, it did exactly what I did, which was not what I wanted. This is because the default state of the Macro recorder is very literal and will repeat the exact actions you take. This can lead to disastrous results, as I found out when I accidentally destroyed my data while trying to fix it. But fear not, there is a setting that can fix this issue. It's called "Use Relative Reference" and can be found on the View tab or the Developer tab. By turning this on, the Macro recorder will use relative references instead of absolute references, making your Macro more flexible and less prone to errors. I highly recommend turning this setting on before recording any Macros. I hope this video has helped you understand the importance of using relative references in your Macro recordings. It's a simple setting that can make a huge difference in your Excel experience. Thank you for watching, and don't forget to subscribe to our channel for more Excel tips and tricks. 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-... Table of Contents: (00:00) Introduction and Sponsorship (00:10) The Problem with the Default State of the Macro Recorder (00:41) Attempting to Fix the Problem with a Macro (01:02) The Default State of the Macro Recorder (01:35) The Solution: Turning on Relative Reference (01:57) Recording the Macro with Relative Reference On (02:28) Using the Fixed Macro (02:38) Common Problems with the Excel Macro Recorder (02:55) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: Better experience with macro recording Data set for mail merge Default state of macro recorder Excel in Depth, chapter 27 Fixing names in data set Macro Recording Microsoft's decision on Macro recorder Turning on relative reference setting Use of relative reference in macro recording VBA Macros Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads... Using the Microsoft Excel Macro Recorder. In Episode #1244, Bill shows you an example of how to record an Excel Macro using Relative References. ...This is the video podcast companion to the book, "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.