How to Record a Macro in Excel: A Beginner's Guide to Automate Excel with the Macro Recorder
Last updated on April 13, 2023 By Victor Chan
Do you want to automate repetitive tasks in Excel? Do you want to save time and improve your productivity? If so, you might want to learn how to record and run macros in Excel.
A macro is a set of instructions that tells Excel what to do. You can use a macro to perform complex calculations, format data, create charts, and more. Macros are written in a programming language called Visual Basic for Applications (VBA).
But don't worry, you don't need to be a programmer to create macros in Excel. You can use a feature called the Macro Recorder, which allows you to record your actions as VBA code. The Macro Recorder is like a video camera that captures everything you do in Excel and converts it into VBA code that you can run later.
In this article, I will show you how to record macros in Excel and run the recorded macros. You will learn how to:
- Enable the Developer tab in Excel
- Record a simple macro using the Macro Recorder
- Run a macro from the Macro dialog box or a keyboard shortcut
- Edit a macro using the Visual Basic Editor
- Save a macro-enabled workbook
- Use absolute references or relative references
Let's get started!
Enable the Developer Tab in Excel
Before you can record a macro in Excel, you need to enable the Developer tab on the ribbon. The Developer tab contains tools for working with macros, VBA, and add-ins.
To enable the Developer tab, follow these steps:
- 1. Click the File tab and select Options.
- 2. In the Excel Options dialog box, click Customize Ribbon on the left side.
- 3. In the Customize the Ribbon list on the right side, check the box next to Developer and click OK.
You should now see the Developer tab on the ribbon.
Record a Macro in Excel Using the Macro Recorder
Now that you have enabled the Developer tab, you can record a simple macro using the Macro Recorder. For this example, I will record a macro that formats a range of cells as a table with alternating row colors.
To record a macro using the Macro Recorder, follow these steps:
- 1. Select the range of cells that you want to format as a table.
- 2. Click the Developer tab and click Record Macro in the Code group.
- 3. In the Record Macro dialog box, enter a name for your Macro. The name should start with a letter and not contain any spaces or special characters. For this example, I will name my macro “InsertIcon” because I will use it to insert an icon on the worksheet.
- 4. Optionally, you can assign a keyboard shortcut to your Macro by entering a letter in the Shortcut key box. The shortcut key will be combined with Ctrl (or Ctrl+Shift if you enter an uppercase letter). For this example, I will assign Ctrl+Shift+I as my shortcut key.
- 5. Optionally, you can enter a description for your Macro in the Description box. This can help you remember what your Macro does later.
- 6. In the Store macro in the drop-down list, select where you want to save your Macro. You can choose one of these options:
- This Workbook: This will save your Macro in the current workbook only. You can use your Macro in this workbook or any workbook that references it.
- New Workbook: This will create a new workbook and save your Macro there. You can use your Macro in this new workbook or any workbook that references it.
- Personal Macro Workbook: This will save your Macro in a hidden workbook called PERSONAL.XLSB that opens every time you start Excel. You can use your Macro in any workbook on your computer.
- 7. For this example, I will choose This Workbook.
- 8. Click OK to start recording your Macro.
Perform the actions that you want your Macro to do. In this case, I will do these steps:
- 1. Click the Insert tab and click Icons in the Illustrations group.
- 2. Choose your favorite icon from the gallery.
- 3. Click on insert. This will insert the chosen icon in the active cell.
When you are done with your actions, click Stop Recording in the Code group on the Developer tab.
You have successfully recorded a macro in Excel! You can now run your Macro to insert the same icon in the active cell.
Run a Recorded Macro from the Macro Dialog Box or a Keyboard Shortcut
First select a new cell, say D2. To run your Macro, you have two options:
- Use the Macro dialog box
- Use the keyboard shortcut that you assigned
To run your Macro from the Macro dialog box, follow these steps:
- 1. Click the Developer tab and click Macros in the Code group.
- 2. In the Macro dialog box, select the name of your Macro from the list. For this example, I will select FormatTable.
- 3. Click Run (or Double click the Macro name).
Your Macro will run and format the selected range of cells as a table with alternating row colors.
You can also use the shortcut Alt+F8. This opens the Macro Dialog Box where you can see all the macros in the current workbook, and choose to run, edit, or delete them. To close the Macro Dialog Box, press Esc or click Cancel.
To run your Macro from the keyboard shortcut that you assigned, simply press the shortcut key combination.
For this example, I will press Ctrl+Shift+I.
Your Macro will run and format the selected range of cells as a table with alternating row colors.
Edit an Excel Macro Using the Visual Basic Editor
If you want to modify or enhance your Macro, you can edit it using the Visual Basic Editor (VBE). The VBE is an integrated development environment (IDE) that allows you to write, edit, debug, and run VBA code.
To edit your Macro using the VBE, follow these steps:
- 1. Click the Developer tab and click Visual Basic in the Code group.
- 2. In the VBE window, locate your Macro in the Project Explorer pane on the left side. The Project Explorer shows all the workbooks and modules that contain VBA code. A module is a container for VBA code that can hold one or more macros.
- 3. Double-click the module that contains your Macro. For this example, I will double-click Module1 under ThisWorkbook.
- 4. In the Code window on the right side, you will see the VBA code that was generated by the Macro Recorder. You can edit this code as you wish. For example, you can change the table style, add comments, insert variables, use loops or conditions, and more.
When you are done editing your code, click Save on the toolbar or press Ctrl+S to save your changes.
Save a Macro-Enabled Workbook
If you want to save your workbook with macros, you need to save it as a macro-enabled workbook. A macro-enabled workbook has a .xlsm file extension and can run macros when opened.
To save your workbook as a macro-enabled workbook, follow these steps:
- 1. Click the File tab and select Save As.
- 2. In the Save As dialog box, choose a location where you want to save your workbook.
- 3. In the File name box, enter a name for your workbook.
- 4. In the Save as type drop-down list, select Excel Macro-Enabled Workbook (*.xlsm) and click Save.
You have successfully saved your workbook as a macro-enabled workbook. You can now open it and run your macros anytime.
Relative References vs Absolute References when Recording a Macro
Now that you’ve learned how to record and run a macro in Excel to automate simple tasks, I’d like you to know one more thing before creating your own macros: the difference between absolute and relative references.
- Absolute references are fixed cell addresses that do not change when you copy or move them. For example, $A$1 is an absolute reference to cell A1. If you record a macro with absolute references, it will always perform the actions on the same cells you recorded, regardless of where you start the macro. This can be useful if you want to apply the same formatting or calculation to a specific range of cells every time.
- Relative references are flexible cell addresses that change depending on where you copy or move them. For example, A1 is a relative reference to cell A1. If you record a macro with relative references, it will perform the actions on the cells that are offset from the active cell by the same amount as you recorded. This can be useful if you want to repeat the same actions on different parts of the worksheet.
To record a macro with relative references, you need to click on the Use Relative References button in the Developer tab before you start recording. You can also toggle this option on and off during recording if you need to mix absolute and relative references in your macro.
Tips and Tricks for Macro Recording
Here are some tips and warnings when recording a macro:
- Before you record a macro, plan your steps carefully and make sure you have a clear goal and outcome.
- Choose a meaningful name and description for your macro and assign a shortcut key if you want.
- To run a recorded macro, you can either click on the Run button in the Developer tab, or press the shortcut key that you assigned.
- To view or edit the VBA code of your macro, you can click on the Visual Basic button in the Developer tab and open the Module window.
- Be careful when running a macro, because macros cannot be undone. If you make a mistake or want to stop a macro, press Ctrl + Break on your keyboard.
- To delete a macro, click on the Macros button in the Developer tab and select the macro that you want to delete. Then click on the Delete button.
Conclusion
In this article, you learned how to record and run macros in Excel step by step. You learned how to:
- Enable the Developer tab in Excel
- Record a simple macro using the Macro Recorder
- Run a macro from the Macro dialog box or a keyboard shortcut
- Edit a macro using the Visual Basic Editor
- Save a macro-enabled workbook
- How to record macros with Absolute references
- How to record macros with Relative references
Recording macros is a great way to automate repetitive tasks in Excel and save time and effort. However, recording macros is only one way to create macros in Excel. You can also write macros from scratch using VBA code. This gives you more flexibility and control over what your macros can do.
If you want to learn more about VBA from Microsoft's documentation, check out this resource:
I hope you enjoyed this article and learned something new. If you did, please share it with your friends and colleagues who might benefit from it. And if you have any questions or feedback, please leave a comment below. I would love to hear from you.
Thank you for reading, and happy macro recording!
Hey, I'm Victor Chan
Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?
Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.
That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.
Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!
Recommended Posts
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
RECOMMENDED READING
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.