Pin Me

Working with Macros in Excel 2007

written by: •edited by: Tricia Goss•updated: 5/14/2009

In this Excel 2007 guide, we’ll show how to quickly automate tasks by recording a macro. We’ll also discuss how to execute macros after they’ve been created.

  • slide 1 of 1

    Macros in Excel

    While there are a number of features and tools in Excel 2007 that can be used to optimize and speed up workflow, there are still series of tasks and commands that many users will find very repetitive and time consuming. However, it is possible to create a custom macro that will execute such a sequence of commands on your behalf with just one click of a button. In the steps below, we’ll show how easy it is to construct a standard macro in Excel.

    How to Record a Macro

    Step 1: Make sure the Developer tab is shown on the Excel ribbon. If it is not, you can add it by clicking on the Office button and then selecting Excel Options. (Click any image for a larger view.)

    Excel Options 

    In the Excel Options window, select Popular from the list of categories in the left hand panel. Put a check in the box next to Show Developer tab in the Ribbon.

    Show Developer Tab 

    Click the OK button to return to Excel. The Developer tab will now be present on the ribbon.

    Step 2: Open the Developer tab, click the Record Macro button.

    Record Macro Button 

    Note that the main Macros button can also be found on the View tab of the Excel ribbon, but the Developer tab contains more advanced macro tools so it’s worth the extra couple of moments it took to add it to the ribbon.

    Step 3: In the Record Macro window, enter a name and description for the new macro. At this time, you can also assign a shortcut to the macro for later use. From the drop down box next to Store macro in, pick the workbook in which you want to store the macro. If you want the macro to be accessible in any Excel file, choose Personal Macro Workbook. Select This Workbook if the current spreadsheet is the only one in which you plan to use the macro.

    Record Macro Button 

    Click OK to continue.

    Step 4: Perform all of the actions that you want included in the macro. When done, click the Stop Recording button on the Developer tab.

    Stop Recording Macro 

    How to Run a Macro

    After a macro has been created, you can run it in two ways:

    If you assigned a shortcut to the macro, you can type that key sequence to run the macro.

    Alternatively, click on the Macros button on the Developer tab. When the macro dialog box appears on your screen, select the macro you want to execute and click Run.

    Run a Macro 

    Be sure to browse through the other Microsoft Excel user guides available here at Bright Hub. Learn how to publish an Excel workbook as a web page, experiment with new chart and table design tips, find out how to use conditional formatting to make your data analysis easier, and more. Additional tutorials are being added on a regular basis so keep checking back.