How to Write a Simple Macro in Microsoft Excel

December 2022 · 4 minute read

Picture 1 of How to Write a Simple Macro in Microsoft Excel

Open Excel. The steps are the same for Excel 2010, 2013 and 2016 and differ only slightly for Excel for Mac: this difference is detailed below.

Picture 2 of How to Write a Simple Macro in Microsoft Excel

Click the File tab .

With Excel for Mac, click the "Excel" menu.

Picture 3 of How to Write a Simple Macro in Microsoft Excel

Click Options .

With Excel for Mac, click and the "Preferences" menu.

Picture 4 of How to Write a Simple Macro in Microsoft Excel

Go to the Customize Ribbon option.

With Excel for Mac, click "Ribbon & Toolbar" in the "Authoring" section.

Picture 5 of How to Write a Simple Macro in Microsoft Excel

Select the Developer box in the list on the right.

With Excel for Mac, the "Developer" entry is in the "Tab or Group Title" list.

Picture 6 of How to Write a Simple Macro in Microsoft Excel

Select OK . The Developer tab will appear at the bottom of your tag list.

Macro Recording

Picture 7 of How to Write a Simple Macro in Microsoft Excel

Practice with the sequence of actions in your macro. When recording a macro, your every action or any click will be recorded. Therefore, just one mistake is enough for everything to go in vain. Work a few times with the commands you will record so that when you do, you can execute them decisively and accurately.

Picture 8 of How to Write a Simple Macro in Microsoft Excel

Open the Developer tab.

Picture 9 of How to Write a Simple Macro in Microsoft Excel

Click Record Macro . This option is located in the Code section of the ribbon. You can also use Alt+T+M+R to start a new macro (Windows only).

Picture 10 of How to Write a Simple Macro in Microsoft Excel

Name the macro. You should use a recognizable name, especially if you need to create a lot of macros.

You can also add a description to explain the function of the macro.

Picture 11 of How to Write a Simple Macro in Microsoft Excel

Click the Shortcut key field . You can assign keyboard shortcuts to macros for easier operation. Note that this is not a required step.

Picture 12 of How to Write a Simple Macro in Microsoft Excel

Press ⇧ Shift with the same letter. The Ctrl+⇧ Shift+letter key combination used to start the macro will be initialized.

On a Mac, that would be ⌥ Opt+⌘ Command+letter.

Picture 13 of How to Write a Simple Macro in Microsoft Excel

Click the Store macro in menu .

Picture 14 of How to Write a Simple Macro in Microsoft Excel

Click the location where you want to save the macro. If you're only using the macro for the current sheet, just leave it at "This Workbook". If you want to use macros for any worksheet, select "Personal Macro Workbook".

Picture 15 of How to Write a Simple Macro in Microsoft Excel

Click OK . Your macro will begin to be recorded.

Picture 16 of How to Write a Simple Macro in Microsoft Excel

Manipulate the commands that you want to record. Almost now, all your actions will be recorded and added to the macro. For example, in C7, if you run the formula to calculate the sum of A2 and B2, later, when running the macro, the sum of A2 and B2 will always be calculated and displayed at C7.

Macros can become very complex. You can even use them to open other Office programs. When recording a macro, almost everything you do in Excel is added to it.

Picture 17 of How to Write a Simple Macro in Microsoft Excel

Press Stop Recording when finished. Macro recording will be stopped and your macro will be saved.

Picture 18 of How to Write a Simple Macro in Microsoft Excel

Save the file in the available macro format. To preserve macros, you need to save the workbook in a special Excel format that allows the use of macros:

  • Click the File menu and choose Save.
  • Click the File Type menu below the name field.
  • Click Excel Macro-Enabled Workbook.

    • Using macros

    Picture 19 of How to Write a Simple Macro in Microsoft Excel

    Open your macro-enabled Excel file. If you close the file before running the macro, you will be asked to enable its contents.

    Picture 20 of How to Write a Simple Macro in Microsoft Excel

    Click the Enable Content option . Every time an Excel file that allows the use of macros is opened, this option is located at the top of the worksheet, on the Security Warning bar. Since this is your file, you can rest assured with it. However, be very careful when opening macro-enabled files from any other source.

    Picture 21 of How to Write a Simple Macro in Microsoft Excel

    Click your macro shortcut. When you want to use a macro, you can quickly run it by pressing the shortcut you created for it.

    Picture 22 of How to Write a Simple Macro in Microsoft Excel

    Click the Macros button on the Developer tab. All macros available on your current worksheet will be displayed.

    Picture 23 of How to Write a Simple Macro in Microsoft Excel

    Click the macro that you want to run.

    Picture 24 of How to Write a Simple Macro in Microsoft Excel

    Press the Run button . The macro will run on the current cell or selected cells.

    Picture 25 of How to Write a Simple Macro in Microsoft Excel

    View the code of a macro. If you want to learn more about how macro codes work, you can open the code of any macro you've created and tinker with it:

  • Click the Macros button on the Developer tab.
  • Click the macro you want to view.
  • Click the Edit button.
  • View the macro code in the Visual Basic code editor window.

    • ncG1vNJzZmismaXArq3KnmWcp51ktbDDjK2mZq%2BinsGmecBmqqKloKGybrnAnKmoZZmjeq61wqumrKeWqXqmxMKeow%3D%3D