You can automate any repetitive tasks in Excel using macros. Here’s how to record one.
Every button you press, every formula you create—it can all be automated in Excel if you know how to record macros. An Excel macro repeats every step you take, making it easy to automate your most repetitive actions.
If you’ve ever recorded macros in Word, then you’ll be familiar with the process. You could write your own macros using VBA (Visual Basic for Applications), but it’s much easier to record them visually using the Macro Recorder tool. Here’s how.
How to Record an Excel Macro
For most Excel users, the easiest method for recording a macro in Microsoft Excel is to use the Macro Recorder. While the Macro Recorder is recording, you complete an action. The steps you take are saved, converted into actions that Excel can repeat.
To start recording an Excel macro, you’ll need to access the Developer tab in the ribbon bar. This is usually hidden, so you’ll have to customize the ribbon bar to display it first.
To do that, right-click the ribbon bar and press Customize the Ribbon.
In Excel Options, click to enable the Developer checkbox in the Customize Ribbon tab. Press OK to save.
The ribbon bar will now display the Developer tab between the View and Help tabs.
To begin recording a macro, click Developer > Record Macro.
In the Record Macro window, give your macro a memorable name and description. To tie your macro to a keyboard shortcut, type a letter under Shortcut key, although this is optional.
To save your Excel macro with your current workbook, make sure that the Store macro in option is set to This Workbook. If you want to use your macro in all Excel workbooks, select Personal Macro Workbook instead.
Click OK to start recording your macro. Every step you take after this point will be recorded. For instance, if you decide to perform an action like changing the format of a cell, these steps will be recorded.
Once you’ve recorded your steps to the macro, press Stop Recording in the Developer tab.
Click the Macros button in the Developer tab to see a list of your recorded macros.
To use your macro, select it, then click the Run button.
If you added a keyboard shortcut to your macro, you can use that shortcut to run your macro instead.
Editing Excel Macros
It’s entirely possible to edit a recorded macro to add, change, or remove steps entirely. Unfortunately, this will require you to know how to write and edit VBA (Visual Basic for Applications) code to be able to make changes.
Should you wish to make changes to your recorded macro (and you’re happy to write in VBA), click Developer > Macros, select your macro, then press the Edit button.
This will load the Office VBA editor. You can make changes to the VBA code directly, pressing the Run button to test your changes to make sure they work.
Close the Office VBA editor once you’ve made the changes to your macro, then save your Excel workbook.
If you can’t write macros in VBA, the easiest method is to delete the macro and re-record it. Select your macro and press Delete to remove it, then repeat the steps above to record it with updated steps.
Saving Your Excel Macros
Once you’ve started using macros in Excel, you can record any actions or tasks and repeat them whenever you need to.
When you go to save your Excel workbook, remember to save it in the XLSM Macro-Enabled Workbook file format. This will save y0ur recorded Excel macros inside the workbook, allowing you to use them elsewhere.