How to Save Macro in Excel (with Easy Steps)

Step 1 – Activate Record Macro Option

• Record a macro. To do that, go to the Developer tab and click on Record Macro.

A dialogue box will open.

Step 2 – Input Macro Name, Short Key, and Store Location

• In the Record Macro Dialogue Box, there are 3 fields you need to fill.
  • In the Macro name box, enter a suitable name for the macro (The name must not have any space).
  • In the Shortcut key box, enter a shortcut key that you will use for running the macro.
  • In the Store macro box, there are three options.

1. 1. 1. 1. Choose Personal Macro Workbook if you want to run the Macro for every workbook.
         2. Choose New Workbook if you need to run the macro in a new workbook.
         3. Choose This Workbook if you want to run the macro in this workbook only.

Step 3 – Perform a Series of Actions to Record Macro

• For illustration purposes, we are showing an Example Macro. In this macro, we will perform 3 tasks.
  1. Change the background color of any selected cell to green.
  2. Change the font size to 14 and
  3. Make the font bold.
• Select a cell, go to the Developer Tab and click on Record Macro.

In the Dialogue Box, we entered the following.

• Go to the Home tab and perform the 3 tasks mentioned above.

Step 4 – Stop Recording of Macro

• Go to the Developer tab and click on Stop Recording.

• Our macro has been recorded.
• Select another cell and run the macro by clicking ctrl+f.

• We get the result.

Step 5 – Save Excel File in XLSM Format

To run the macro again after closing the file, we need to save the file as an xlsm file.

• Go to the File Tab.

• You should see a new window as shown below.

• Go to Save As. In the File type drop-down menu. Select Excel Macro-Enabled Workbook(*.xlsm). Click Save.
• If you close and reopen the workbook, you can still run the macro by clicking ctrl+f.

Things to Remember

• If you don’t have the Developer Tab enabled, you have to enable it first. Check this article for enabling Developer Tab.
• You must perform the tasks in sequence while recording Macro. Otherwise, the result might be different.
• If your excel file is xlsx type, you don’t have to save this as an xlsm file to enable the Macro as macro is by default enabled in the xlsx file.