How to Record a Macro in Excel 2016

Joe Keeley 17-01-2018

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 5 Resources for Excel Macros to Automate Your Spreadsheets Searching for Excel macros? Here are five sites that have got what you're looking for. Read More 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 How to Send Emails From an Excel Spreadsheet Using VBA Scripts Our code template will help you set up automated emails from within Excel using Collaboration Data Objects (CDO) and VBA scripts. Read More or integrate your Excel data into Word How to Integrate Excel Data Into a Word Document During your work week, there are probably lots of times that you find yourself copying and pasting information from Excel into Word, or the other way around. This is how people often produce written reports... Read More .

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.

  1. Enable the Developer tab.
  2. On the Developer tab, click Record Macro.
  3. Input a Macro name.
  4. Assign a Shortcut key.
  5. Select where to Store macro in.
  6. Input a Description.
  7. Click OK.
  8. Perform your macro actions.
  9. 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.

how to record a macro in excel 2016


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.

how to record a macro in excel 2016

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.

how to record a macro in excel 2016

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.


how to record a macro in excel 2016

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.

how to record a macro in excel 2016


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.

how to record a macro in excel 2016

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.

how to record a macro in excel 2016

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 4 Mistakes to Avoid When Programming Excel Macros With VBA Simple code is vital for Microsoft Excel power users. But how can you make or safely exit a for loop using VBA in Excel? Read More 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.

how to record a macro in excel 2016

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.

how to record a macro in excel 2016

If you’re looking to get even more from your macros, check out our guides: VBA programming for beginners The Excel VBA Programming Tutorial for Beginners VBA is a Microsoft Office power tool. You can use it to automate tasks with macros, set triggers, and lots more. We'll introduce you to Excel visual basic programming with a simple project. Read More and building a toolbar for your macros How to Build a Custom Excel Toolbar of VBA Macros Ready to supercharge your Microsoft Excel productivity? A custom toolbar can do just that. We'll show you how to put all your Excel macros within reach. Read More . Using VBA for macros in Excel adds even more power!

Macros aren’t the only way to boost your Excel productivity. Other useful tips include creating dropdown lists for Excel cells How to Create a Dropdown List in Microsoft Excel Learn how to create a dropdown list in Microsoft Excel, as well as customize it and add a dependent dropdown list. Read More , using IF statements for dynamic Excel data How to Use IF Statements in Microsoft Excel Whether you're a seasoned expert or a spreadsheet beginner, you'll want to check out this guide to IF statements in Excel. Read More , and using Excel’s Goal Seek feature for more advanced data analysis How to Use Excel's Goal Seek and Solver to Solve for Unknown Variables Excel can solve for unknown variables, either for a single cell with Goal Seeker or a more complicated equation with Solver. We'll show you how it works. Read More .

Related topics: Microsoft Excel, Microsoft Office Tips, Spreadsheet.

Affiliate Disclosure: By buying the products we recommend, you help keep the site alive. Read more.

Whatsapp Pinterest

Leave a Reply

Your email address will not be published. Required fields are marked *