How to Write a Simple Macro in Microsoft Excel

Enable Macros

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.

Click the File tab .

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

Click Options .

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

Go to the Customize Ribbon option.

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

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.

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

Macro Recording

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.

Open the Developer tab.

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).

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.

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

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.

Click the Store macro in menu .

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".

Click OK . Your macro will begin to be recorded.

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.

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

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:

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

Using macros

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

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.

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

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

Click the macro that you want to run.

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

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:

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

Isabella Humphrey

Update 24 February 2022