Look Who it Was Random video

Excel - Macro Recorder: Fixing Data Formatting with a Simple Trick - Episode 810

951 views · Published 13 January 2009 · 4:12 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial - Macro Recorder: Fixing Data Formatting with a Simple Trick.

Welcome back to the MrExcel netcast. In this episode, we continue our discussion on the macro recorder and its capabilities. Yesterday, we saw how the macro recorder worked perfectly for a simple task. However, today we have a slightly more complicated situation where we need to move the cell pointer in order to record the macro.

I have received some data from someone who wants to do a mail merge using Excel data in Word. However, the data came in completely the wrong format. The names were in column A, addresses in column B, and city, state, and zip in column C. I realized that I could write a quick macro to fix one record and then run it repeatedly to fix the entire dataset. So, I started recording a macro and named it "FIXONE" with a shortcut key of CONTROL+A.

Since this is a specialized problem for this particular workbook, I chose to store the macro in THIS WORKBOOK. I then proceeded to fix one record by cutting the data in A2 and pasting it in B1, then cutting the data in A3 and pasting it in C1. I also deleted the next three rows and made sure I was on the next name so that the macro could be run again. Finally, I stopped recording the macro.

However, when I tried to run the macro, I realized that the macro recorder is very literal and recorded every step I took. This meant that the macro only worked for the first record and was not useful for the rest of the dataset. This was not the impressive solution I was hoping for. But, there is a trick to make the macro recorder work in this situation.

Before recording the macro, we need to click on the "USE RELATIVE REFERENCE" button. This will change the macro recorder to record the relative action instead of the absolute action. Now, when we record the macro, it will go down one cell from where we started, cut, go up one cell and over one cell, and paste. It will then go down two cells, cut, go up and over two cells, and paste. Finally, it will delete the next three rows and select that cell. This macro can now be run repeatedly by pressing CONTROL+A and it will work perfectly for all the records.

I believe that this button should be turned on by default and the tooltip should say "press this button to make your macros work 99% of the time instead of 1% of the time". But, for now, we have to manually turn on the "USE RELATIVE REFERENCE" button. In the next episode, we will explore a few other tweaks that can be made while recording macros to make them work more often than not. Thanks for watching this netcast from MrExcel. Don't forget to like, comment, and subscribe for more Excel tips and tricks. See you next time!

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/

While yesterday's macro showed a simple formatting macro, it had a limitation that you can not move to a new cell. That is a fairly severe limitation. In Episode 810, we take a look at why moving the cell pointer causes a macro to fail and the simple setting to allow the macros to work.

Table of Contents:
(00:00) Introduction to the macro recorder
(00:11) Example of the macro recorder in action
(00:21) The common pattern of macro recording
(00:35) Fixing a data formatting issue with a macro
(01:00) Recording a macro to fix one record
(01:36) The problem with the recorded macro
(02:10) The trick to making the macro recorder work
(02:29) Using relative reference for more accurate macros
(03:19) The effectiveness of using relative reference
(03:30) Upcoming topics for macro recording
(03:50) 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:
Deleting rows with macro recorder
Excel mail merge with macro recorder
Fixing data format using macro recorder
How to use macro recorder in Excel
Improving macro recording efficiency
Making macros work more often
Recording a macro to fix data in Excel
Shortcut key for macro recorder
Storing macro in workbook
Tips for recording effective macros
Using relative reference in macro recorder
YouTube video on macro recorder

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

More from this channel