Record a Macro using the Macro Recorder

Excel 2007
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.    Choose Tools -> Macro -> Record New Macro... to display the Record Macro dialog box:

Record Macro in Excel 2003

   4.    The Macro Recorder enters a default name (such as Macro1) in the Macro Name box.

  • 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 2003
  • 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 2003

   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 Stop Recording toolbar (or, if you've lost track of it, choose Tools -> Macro -> Stop Recording).

Stop Recording in Excel 2003

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more