How to Write a Simple Macro in Microsoft Excel

Enable Macros

How to Write a Simple Macro in Microsoft Excel Picture 1

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.

How to Write a Simple Macro in Microsoft Excel Picture 2

Click the File tab .

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

How to Write a Simple Macro in Microsoft Excel Picture 3

Click Options .

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

How to Write a Simple Macro in Microsoft Excel Picture 4

Go to the Customize Ribbon option.

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

How to Write a Simple Macro in Microsoft Excel Picture 5

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.

How to Write a Simple Macro in Microsoft Excel Picture 6

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

Macro Recording

How to Write a Simple Macro in Microsoft Excel Picture 7

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.

How to Write a Simple Macro in Microsoft Excel Picture 8

Open the Developer tab.

How to Write a Simple Macro in Microsoft Excel Picture 9

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

How to Write a Simple Macro in Microsoft Excel Picture 10

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.

How to Write a Simple Macro in Microsoft Excel Picture 11

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

How to Write a Simple Macro in Microsoft Excel Picture 12

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.

How to Write a Simple Macro in Microsoft Excel Picture 13

Click the Store macro in menu .

How to Write a Simple Macro in Microsoft Excel Picture 14

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

How to Write a Simple Macro in Microsoft Excel Picture 15

Click OK . Your macro will begin to be recorded.

How to Write a Simple Macro in Microsoft Excel Picture 16

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.

How to Write a Simple Macro in Microsoft Excel Picture 17

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

How to Write a Simple Macro in Microsoft Excel Picture 18

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

How to Write a Simple Macro in Microsoft Excel Picture 19

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

How to Write a Simple Macro in Microsoft Excel Picture 20

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.

How to Write a Simple Macro in Microsoft Excel Picture 21

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

How to Write a Simple Macro in Microsoft Excel Picture 22

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

How to Write a Simple Macro in Microsoft Excel Picture 23

Click the macro that you want to run.

How to Write a Simple Macro in Microsoft Excel Picture 24

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

How to Write a Simple Macro in Microsoft Excel Picture 25

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