Learning Outcomes

  • Use macros to complete actions in Microsoft Excel

Macros are programs used to automate frequently used processes or tasks in Excel. A macro records operations and re-uses the sequence of mouse actions or keystrokes of anything you can do in Excel with keystrokes or a mouse. Some of those actions include; cleaning up data, formatting cells, creating tables or organizing data in an Excel spreadsheet. If you have done repetitive tasks with a spreadsheet before, you may already have discovered macros. If you have not discovered them, learn this advanced Excel tool to speed up your efficiency and save time.

First, watch the following video which gives an overall example of how to program a macro in order to change the formatting on a sales report. After the video, we’ll walk through a macro step by step and highlight a few of the commonly used actions to be program into a macro.

Practice Question

Macro Recording Locations

A macro has three main ways to be recorded. Two are from the ribbon area and one is not. Experiment and find your favorite way to record it.

How to record a macro:

  1. From the View tab, click the Macros button, and then the Record Macro button.
    Excel menu bar displaying the View tab, Macros button with dropdown menu including View Macros, Record Macros, Use Relative References
  2. The video demonstrates how to make the developer tab appear in the menu. Here are the steps to follow:
    • Go to File, Options, Customize Ribbon, then check the Developer check box and click OK.
      Screenshot of Excel Options window highlighting actions to make Developer tab visible.
    • Going back to the main menu, the Developer tab is now visible. Click on it, and go to the Code group, and select the Record Macro button.
      Excel menu bar displaying the Developer tab, Macros in Code area on ribbon.
  3. The last main recording area is for the Macros button is at bottom left of spreadsheet window. Click on the button and a Macro dialog box opens to be filled out to begin recording.
    Excel screenshot of open worksheet filled with data highlighting the small record macro button on bottom lower left of window.

Practice Question

Macros are recorded in a program called Visual Basic. To edit a macro, the developer tab needed to access the Visual Basic program. We will not be diving into Visual Basic this time as an entire course all on its own. For now we will stay with the macro recording basics.