Record a Macro using the Macro Recorder

The easiest way to create a macro in Excel is to use Office's built-in Macro Recorder tool.

To record a macro, follow these steps:

    1.    Decide what the macro will do. If necessary, write down the main points so you don't forget them. Planning the macro's sequence of actions will help you avoid making mistakes that you'll then have to edit out of the macro for it to work properly.

    2.    Launch or activate Excel and set it up for the actions you're about to perform. For example, if you're recording a macro that will format a particular type of workbook, open a workbook of that type that you can experiment on without damaging or destroying any valuable data.

    3.    On the View tab, in the Macros group, click the Macros button and then choose Record Macro...:

Macro in Excel 2007

    4.    In the Record Macro dialog box, enter a name (such as Macro1) in the Macro Name box:

Record Macro in Excel 2007
  • Macro names must start with a letter, after which they can be your choice of mix of letters, numbers, and underscores. They can't contain spaces, symbols, or punctuation marks.
  • The maximum length for a macro name is 80 characters.
  • Shorter names tend to be more practical, because you can see them in full in the Macro dialog box.

    5.    Enter a description of the macro's contents and purpose in the Description box. Either replace the Macro Recorder's default description or add to it. This description helps you (or others) identify the macro when the name isn't sufficiently descriptive.

    6.    Choose where to store the macro. It's important to store your macros in a suitable location; otherwise, you won't be able to use them when you need them. Your choices are as follows:

Store Macro in Excel 2007
  • Personal Macro Workbook This is Excel's central repository for macros you create. Macros in the Personal Macro Workbook are available whenever Excel is running. The Personal Macro Workbook is the \Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLS file. Excel automatically creates this file when you first choose to store a macro in the Personal Macro Workbook.
  • This workbook Stores the macro in the active workbook. Macros stored in a workbook are available only when that workbook is open. This option is good for macros that apply only to a particular workbook.
  • New workbook Creates a new workbook and stores the macro in it. The macro is available only when that workbook is open. This option is primarily useful for recording a quick macro that you want to use to manipulate a workbook but which you don't want to store in that workbook or in the Personal Macro Workbook. By closing the new workbook without saving changes to it, you can dispose of the new macro easily after it has outlived its usefulness.

    7.    Optionally, specify a way of running the macro. Excel lets you assign the macro to a CTRL key shortcut or a CTRL+SHIFT key shortcut. Click to place the focus in the Shortcut Key text box and press the key for the letter you want to assign. To create a CTRL+SHIFT key shortcut, press SHIFT and the letter.

Shortcut keys in Excel 2007

    8.    Click the OK button to close the Record Macro dialog box. Excel displays the Stop Recording toolbar, and starts the Macro Recorder.

    9.    Take the actions that you want the macro to record:

  • You can use either the keyboard or the mouse to choose menu commands.
  • For selecting objects, you can use the mouse only for maneuvers that unambiguously identify the object. In Excel, this means most objects, because cells and ranges have fixed addresses. (By contrast, in Word, you can't use the mouse to select a word when recording a macro, because the word could appear almost anywhere in the Word document.)
  • To switch between using relative references (the default) and absolute references, click the Relative Reference button on the Stop Recording toolbar.

    10.    Click the Stop Recording button on the bottom of Excel window (or on the View tab, in the Macros group, click the Macros button and choose Stop Recording).

Stop Recording in Excel 2007