Excel Macro Tutorial

Image of a person working on a complex Excel spreadsheet

Image Source/Getty Images 

This tutorial covers using the macro recorder to create a simple macro in Excel. The macro recorder works by recording all keystrokes and clicks of the mouse. The macro created in this tutorial applies a number of formatting options to a worksheet title.

In Excel, all macro-related commands are located on the Developer tab of the ribbon. Often, this tab needs to be added to the ribbon in order to access the macro commands. The topics covered by this tutorial include:

  • Adding the Developer Tab
  • Starting the Excel macro recorder
  • Excel macro recorder options
  • Recording the macro
  • Running the macro
  • Macro errors / Editing the macro

Note This information in this article applies to Excel versions 2019, 2016, 2013, 2010, and 2007.

01
of 06

Add the Developer Tab

Add the Developer Tab in Excel

Adding the Developer Tab in Excel 2019, 2016, 2013 or 2010

  1. Select File.
  2. Select Options to open the Excel Options dialog box.
  3. Select Customize Ribbon in the left-hand window to view the available options in the right-hand window of the dialog box.
  4. Under the Main Tabs section of the options, select Developer.
  5. Select OK.
  6. The Developer tab is added to the ribbon.

Adding the Developer Tab in Excel 2007

  1. In Excel 2007, select the Office button to open the drop-down menu.
  2. Select Excel Options located at the bottom of the menu to open the Excel Options dialog box.
  3. Select Popular at the top of the left hand window of the open dialog box.
  4. Select Show Developer Tab in the Ribbon in the right hand window of the open dialog box.
  5. Select OK.
  6. The Developer tab is visible in the ribbon.
02
of 06

Add a Worksheet Title

Opening the Excel Macro Recorder Dialog Box

Before you start recording a macro, you'll need to add the worksheet title that will be formatted.

Since the title of each worksheet is usually unique to that worksheet, you don't want to include the title in the macro. Therefore you'll add it to the worksheet before starting the macro recorder.

  1. Select cell A1 in the worksheet.
  2. Type the title: Cookie Shop Expenses for June 2008.
  3. Press the Enter key on the keyboard.

Excel Macro Recorder

The easiest way to create a macro in Excel is to use the macro recorder. To do so:

  1. Select Developers.
  2. Select Record Macro to open the Record Macro dialog box.
03
of 06

Understand the Macro Recorder Options

The Macro Recorder Options

There are four options to complete in this dialog box.

  1. Macro name: Give your macro a descriptive name. The name must begin with a letter and spaces are not allowed. Only letters, numbers, and the underscore character are permitted.
  2. Shortcut key (optional): Fill in a letter, number, or other characters in the available space. This will allow you to run the macro by holding down the CTRL key and pressing the chosen letter on the keyboard.
  3. Store macro in (options):
    1. This workbook: The macro is available only in this file.
    2. New workbook: This option opens a new Excel file. The macro is available only in this new file.
    3. Personal macro workbook: This option creates a hidden file Personal.xls which stores your macros and makes them available to you in all Excel files.
  4. Description (optional): Enter a description of the macro.

Set the options in the Record Macro dialog box to match those in the image above. Before you move on, be aware of the following:

  • Selecting OK in the Record Macro dialog box starts recording the macro you have just identified.
  • As previously mentioned, the macro recorder works by recording all keystrokes and clicks of the mouse.
  • Creating the format_titles macro involves clicking on a number of format options on the home tab of the ribbon with the mouse while the macro recorder is running.
04
of 06

Record the Macro Steps

Recording the Macro Steps

Once you have set up the Record Macro options as shown in the previous step, you are ready to proceed.

  1. Select OK in the Record Macro dialog box to start the macro recorder.
  2. Select Home.
  3. Highlight cells A1 to F1 in the worksheet.
  4. Select Merge and Center to center the title between cells A1 and F1.
  5. Select Fill Color (it looks like a paint can) to open the fill color drop-down list.​
  6. Choose Blue, Accent 1 from the list to turn the background color of the selected cells to blue.
  7. Select Font Color (it is a large letter "A") to open the font color drop-down list.
  8. Choose White from the list to turn the text in the selected cells to white.
  9. Select Font Size (above the paint can icon) to open the font size drop-down list.
  10. Choose 16 from the list to change the size the text in the selected cells to 16 points.
  11. Select Developer.
  12. Select Stop Recording to stop the macro recording.

At this point, the worksheet title resembles the title in the image above.

05
of 06

Run the Macro

Running the Macro

Now you can run the macro you just recorded.

  1. Select the Sheet2 tab at the bottom of the spreadsheet.
  2. Select cell A1 in the worksheet.
  3. Type the title: Cookie Shop Expenses for July 2008.
  4. Press the Enter key on the keyboard.
  5. Select Developer.
  6. Select Macros to bring up the View Macro dialog box.
  7. Select the format_titles macro in the Macro name window.
  8. Select Run.

The steps of the macro will run automatically and apply the same formatting steps applied to the title on sheet 1.

At this point, the title on worksheet 2 resembles the title on worksheet 1.

06
of 06

Troubleshoot Macro Errors and Edit a Macro

The VBA Editor Window in Excel

If your macro did not perform as expected, the easiest and best option is to follow the steps of the tutorial again and re-record the macro.

An Excel macro is written in the Visual Basic for Applications (VBA) programming language. Selecting either the Edit or Step Into buttons in the Macro dialog box starts the VBA editor (see the image above).

Using the VBA editor and covering the VBA programming language is beyond the scope of this tutorial.