Excel Macro Tutorial

Excel Macro Recorder
© Ted French

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 will apply a number of formatting options to a worksheet title.

In Excel 2007 and 2010, 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
01
of 06

Adding the Developer Tab

Add the Developer Tab in Excel
Click to Enlarge this Image - Add the Developer Tab in Excel. © Ted French
  1. Click on the File tab of the ribbon to open the file menu.
  2. Click on Options in the menu to open the Excel Options dialog box.
  3. Click on the Customize Ribbon option 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, window checks off the Developer option.
  5. Click OK.
  6. The Developer tab should now be visible in the ribbon in Excel 2010.

Adding the Developer Tab in Excel 2007

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

Adding a Worksheet Title / The Excel Macro Recorder

Opening the Excel Macro Recorder Dialog Box
Opening the Excel Macro Recorder Dialog Box. © Ted French

Before we start recording our macro, we need to add the worksheet title we will be formatting.

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

  1. Click on 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. Click on the Developers tab.
  2. Click on Record Macro in the ribbon to open the Record Macro dialog box.
03
of 06

The Macro Recorder Options

The Macro Recorder Options
The Macro Recorder Options. © Ted French

There are 4 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:
    • This workbook
      • The macro is available only in this file.
    • New workbook
      • This option opens a new Excel file. The macro is available only in this new file.
    • 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.

For This Tutorial

  1. Set the options in the Record Macro dialog box to match those in the image above.
  2. Do Not click OK - yet - see below.
    • Clicking the OK button 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.
  3. Go to the next step before starting the macro recorder.
04
of 06

Recording the Macro Steps

Recording the Macro Steps
Recording the Macro Steps. © Ted French
  1. Click the OK button in the Record Macro dialog box to start the macro recorder.
  2. Click on the Home tab of the ribbon.
  3. Highlight cells A1 to F1 in the worksheet.
  4. Click on the Merge and Center icon to center the title between cells A1 and F1.
  5. Click on the Fill Color icon (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. Click on the Font Color icon (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. Click on the Font Size icon (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. Click on the Developer tab of the ribbon.
  12. Click the Stop Recording button on the ribbon to stop the macro recording.
  13. At this point, your worksheet title should resemble the title in the image above.
05
of 06

Running the Macro

Running the Macro
Running the Macro. © Ted French

To run a macro you have recorded:

  1. Click on the Sheet2 tab at the bottom of the spreadsheet.
  2. Click on cell A1 in the worksheet.
  3. Type the title: Cookie Shop Expenses for July 2008.
  4. Press the Enter key on the keyboard.
  5. Click on the Developer tab of the ribbon.
  6. Click the Macros button on the ribbon to bring up the View Macro dialog box.
  7. Click on the format_titles macro in the Macro name window.
  8. Click the Run button.
  9. The steps of the macro should run automatically and apply the same formatting steps applied to the title on sheet 1.
  10. At this point, the title on worksheet 2 should resemble the title on worksheet 1.
06
of 06

Macro Errors / Editing a Macro

The VBA Editor Window in Excel
The VBA Editor Window in Excel. © Ted French

Macro Errors

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.

Editing/Step Into a Macro

An Excel macro is written in the Visual Basic for Applications (VBA) programming language.

Clicking on 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.

Format
mla apa chicago
Your Citation
French, Ted. "Excel Macro Tutorial." ThoughtCo, Jan. 13, 2018, thoughtco.com/excel-macro-tutorial-3123380. French, Ted. (2018, January 13). Excel Macro Tutorial. Retrieved from https://www.thoughtco.com/excel-macro-tutorial-3123380 French, Ted. "Excel Macro Tutorial." ThoughtCo. https://www.thoughtco.com/excel-macro-tutorial-3123380 (accessed January 16, 2018).