Excel > Excel 2007 Advanced > Macros

Record a Macro

A macro is an automated sequence of actions. It improves productivity by reducing a number of actions into one click of a button. This will make common tasks much quicker to perform.

The macro recorder is used to create macros in Excel. This is quick and easy to use, but is only capable of creating simple macros. Anything more powerful will involve writing VBA (Visual Basic for Applications) code. Using the macro recorder and then editing the macro is a great start before moving beyond the limitations of the recorder.

Workbooks that contain macros need to be saved as a Macro Enabled Workbook. This can be done by clicking the Office Button, selecting Save As and then Excel Macro Enabled Workbook.

In this example, we wish to create a macro that formats a cell with a particular date format.

  1. Click the View tab on the Ribbon
  2. Click Macros and then Record Macro from the Macros group
  3. Macros button on the Ribbon

  4. The Record Macro dialogue box appears. Enter a name for the macro
  5. Macro names cannot contain spaces, begin with a letter or contain restricted symbols such as / and £

    If the macro is to be used in this workbook only, select This Workbook from the Store macro in field. If the macro is to be used in any workbook, select Personal macro workbook

    Enter a short description for the macro and click Ok

    Record Macro dialogue box

  6. The macro is now recording each step that you take. It is recommended to have the necessary steps written down so that you may follow them. Unless you understand VBA code, any mistake at this point usually results in having to delete the macro and re-record it.
  7. Let's complete the steps for the FormatDate macro.

    1. Click the Home tab on the Ribbon
    2. Click the Number format drop list and click More Number Formats
    3. Select Date from the list of Categories on the Number tab
    4. Select the 14 March 2001 format and click Ok
    5. Format date macro

  8. Click Stop Recording from the Macros button on the View tab
  9. Stop recording the macro

That's it! A macro has been created. Let's run the macro to ensure it works as planned.

Run a macro

  1. Select the range that you wish to apply the date format to
  2. Click the View tab and then View Macros from the Macros button.
  3. Select the FormatDate macro and click Run
  4. Running the macro

  5. The macro runs and formats the dates as planned.

We will now assign the macro to a button.

Follow us on

Facebook  Twitter  You Tube 

Online Excel VBA course