How to Create a Macro In Excel

Automate your recurring tasks with simple macros

What to Know

  • Open the Developer tab and choose Record Macro. Add a name and shortcut for macro. In the drop-down, choose This Workbook > OK.
  • Once created, perform formatting commands for the new macro, then choose Stop Recording > File > Save As. Save as an .xlsm file.
  • The Developer tab isn't visible by default. To enable, open Options (PC) or Preferences (Mac). Open Ribbon settings, select Developer.

Microsoft Excel's spreadsheet configuration, formatting abilities, and formula functions allow you to perform repetitive tasks. You can streamline those tasks further by using macros. Learn how to do this with Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel for Microsoft 365 for Mac, Excel 2019 for Mac, and Excel 2016 for Mac.

How to Display the Developer Tab in Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, and Excel 2010

Before you add macros in Excel, display the Developer tab on the ribbon. By default, the Developer tab is not visible.

  1. Go to the File tab and then select Options.

    Excel with Options highlighted
  2. In the Excel Options dialog box, select Customize Ribbon

    Customize Ribbon tab in Excel Options
  3. In the Customize the Ribbon list, go to the Main Tabs section and select the Developer check box.

    Developer option in Excel
  4. Select OK to add the Developer tab to the ribbon.

How to Create a Macro in Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, and Excel 2010

When you're ready to create a macro, start Excel and open a worksheet.

Macros cannot be created or run in Excel Online. However, Excel Online opens workbooks containing macros. You can make changes to worksheets and save workbooks in Excel Online without affecting macros.

  1. Go to the Developer tab.

  2. In the Code group, select Record Macro.

    Record Macro button in Excel
  3. In the Macro Name text box, enter a descriptive name for the macro.

    Macro name field in Excel
  4. Enter a shortcut key for the macro.

    Shortcut key in Record Macro dialog
  5. Select the Store Macro In drop-down arrow and choose This Workbook.

    Store macro in This Workbook field in Excel
  6. Select OK.

  7. Perform the formatting and commands you want to include in the macro. 

  8. Select Stop Recording when you're finished. 

    Stop Recording button in Excel
  9. Go to the File tab and then select Save As or press F12.

    Save As menu item in Excel
  10. In the Save As dialog box, enter a file name for the workbook.

    Naming the file in Excel
  11. Select the Save as Type drop-down arrow, choose Excel Macro-Enabled Workbook and then select Save.

    Excel Macro-Enabled Workbook option

How to Display the Developer Tab in Excel for Microsoft 365 for Mac, Excel 2019 for Mac, and Excel 2016 for Mac

Before you add macros in Excel for Microsoft 365 for Mac or in Excel 2019 or 2016 on a Mac, display the Developer tab on the ribbon. By default, the Developer tab is not visible.

  1. Go to Excel and choose Preferences.

    Path to Excel Preferences on Mac
  2. Select Ribbon & Toolbar.

    Excel Ribbon & Toolbar Preferences icon
  3. In the Customize the Ribbon section, go to the Main Tabs list and select the Developer check box.

    Developer check box in Excel
  4. Select Save.

How to Create a Macro in Excel for Microsoft 365 for Mac, Excel 2019 for Mac, and Excel 2016 for Mac

When you're ready to create a macro, start Excel and open a worksheet.

  1. Go to the Developer tab.

  2. In the Code group, select Record Macro.

    Record Macro buton in Excel for Mac
  3. In the Macro Name text box, enter a name for the macro.

  4. In the Shortcut key text box, type the lowercase or uppercase letter you want to use.

  5. Select the Store macro in drop-down arrow and choose This Workbook.

    This Workbook drop-down menu in Mac Excel
  6. Select OK.

  7. Perform the formatting and commands you want to include in the macro. 

  8. When you're finished, go to the Developer tab and select Stop Recording

  9. Go to the File tab and select Save As or, press Shift+Command+S.

    Save As menu item in Excel for Mac
  10. In the Save As dialog box, enter a file name for the workbook.

  11. Select the File Format drop-down arrow, choose Excel Macro-Enabled Workbook (.xlsm). Then select Save.

    Excel Macro-Enabled Workbook (.xlsm) dropdown menu in Excel for Mac

How to Run a Macro

When you want to run a macro you created in Excel, either use the shortcut you assigned to the macro or run the macro from the Developer tab.

To run a macro using a combination shortcut key, open the worksheet containing the macro. Enter or select the data on which you want to apply the formatting or commands included in the macro. Then, press the key combination assigned to the macro.

To run a macro from the Developer Tab, open the worksheet containing the macro, then follow these steps:

  1. In Excel, enter any data on which you want to apply the formatting or commands you included in the macro. 

  2. Go to the Developer tab.

    Developer tab in Excel
  3. In the Code group, select Macros.

    Macros button in Excel Developer tab
  4. In the Macro dialog box, choose the name assigned to the macro, then select Run.

    Run button in Excel Macro dialog

How to Change the Macro Shortcut Key

To add or change a combination shortcut key for a macro:

  1. Go to the Developer tab.

  2. In the Code group, select Macros.

  3. In the Macros dialog box, select the name of the macro for which you want to assign or change the combination shortcut key.

  4. Select Options.

  5. In the Macro Options dialog box, go to the Shortcut key text box, type the lowercase or uppercase letter to use for the combination shortcut, and then select OK.

Was this page helpful?