Using macros in Microsoft Excel is the perfect way to automate tasks. We’re going to show you how to record a macro in Excel 2016 so that you can free up your time and stop wasting it on repetitive actions.
Before beginning, you might want to check out our top resources for Excel macros to give you an idea of what you could create. Once you’ve mastered it, you’ll even be able to send emails from Excel or integrate your Excel data into Word.
How to Record a Macro in Excel 2016
These are the steps you need to follow in order to record a macro in Excel 2016. We’ll summarise them below and then go into detail afterwards.
- Enable the Developer tab.
- On the Developer tab, click Record Macro.
- Input a Macro name.
- Assign a Shortcut key.
- Select where to Store macro in.
- Input a Description.
- Click OK.
- Perform your macro actions.
- On the Developer tab, click Stop Recording.
Recording a Macro: In Detail
1. Enable the Developer tab
The Developer tab is where you can record your macro. However, it isn’t enabled by default.
To enable it, go to File > Options > Customize Ribbon. In the Customize the Ribbon column with Main Tabs selected on the dropdown, tick Developer, and click OK.
The Developer tab will now appear in the ribbon. It’ll stay here permanently unless you untick it following the instructions above.
2. Click Record Macro
Navigate to the newly enabled Developer tab on the ribbon. In the Code group, click Record Macro. This will open a new window.
Alternatively, you can press Alt + T + M + R.
3. Input a Macro Name
Input a name for the macro within the Macro name field. Make it specific, otherwise, you’ll have trouble quickly identifying what the macro does in the future.
The first character of the macro name must be a letter, but subsequent characters can be letters, numbers or underscores. You can’t use spaces and avoid giving the macro the same name as a cell reference.
4. Assign a Shortcut Key
Assigning a shortcut key will let you run the macro in Excel at any time by pressing that combination. Click within the Shortcut key box and press the key that you want to use in conjunction with Ctrl.
I recommend that you hold Shift while selecting your key combination to make it part of the shortcut. Your macro shortcut will override the default Excel shortcut, if one already exists. For example, if you choose Ctrl + A then it’ll override the ability to select everything. Instead, use Ctrl + Shift + A since that isn’t an existing shortcut.
5. Select Where to Store Macro In
Use the Store macro in dropdown to choose where you want to store the macro.
The options available are:
- Personal Macro Workbook: This will make the macro available whenever you use Excel. It’ll store the macro in a hidden macro workbook called Personal.xlsb.
- New Workbook: This will make the macro available for any workbooks you create during the existing Excel session.
- This Workbook: This will make the macro available only in the workbook you have open.
6. Input a Description
The final step on this window is to input a Description into the box. Use this to describe in detail what the macro does.
This field is optional, but it’s advisable to be as comprehensive as possible so that you and others can see what the macro does in the future.
7. Click OK
Perhaps the easiest step of all! Click OK when you’re happy with what you’ve submitted and to begin recording the macro.
8. Perform Your Macro Actions
The macro is now recording, so perform your steps. This can include typing, clicking cells, applying formatting, or importing data externally from places like Microsoft Access.
While recording you can use the Use Relative References toggle, found on the Developer tab. If enabled, macros are recorded with actions relative to the initial cell. For example, if you click from cell A1 to A3 while recording, running the macro from cell J6 would move the cursor to J8. If disabled, the cursor would move from J6 to J8.
It’s best to plan out in advance what your actions are going to be so that you don’t make a mistake. If you slip up, stop the recording and start again. Alternatively, you could try to fix it by editing the Visual Basic Application (VBA) code that the macro is stored in, but that’s only for advanced users.
9. Click Stop Recording
Once you’ve finished your macro steps, on the Developer tab, in the Code group, click Stop Recording.
Alternatively, you can press Alt + T + M + R.
Use Your Macros
Once you’ve created your macros, you can access them from the Developer tab. Click Macros to see them listed. You can also press Alt + F8 to open this window. Here you can select your macros and perform various options, like Run, Edit or Delete.
Macros aren’t the only way to boost your Excel productivity. Other useful tips include creating dropdown lists for Excel cells, using IF statements for dynamic Excel data, and using Excel’s Goal Seek feature for more advanced data analysis.