Record and use Excel macros

Applies to
Microsoft Excel 2000

Do you have common tasks that you perform over and over in Microsoft Excel 2000? For instance, do you often apply the same combination of formats, or do you receive data every week or month that you organize and analyze the same way every time? You can use a macro to combine all of the steps in a task into a single command.

A macro records your mouse clicks and keystrokes while you work and lets you play them back later. You can use a macro to record the sequence of commands you use to perform a certain task. When you run the macro, it plays those exact commands back in the same order, causing Excel to behave just as if you had entered the commands yourself.

Macros are easy to create: you tell Excel to start recording, perform actions as you normally do, and then tell Excel when you're done. Behind the scenes, Excel uses a programming language called Visual Basic® for Applications (VBA) to record your instructions. You don't have to know anything about programming or VBA to create and use macros that will save you time and make your work easier.

This article shows you how to create and use a simple macro that formats a cell, then change this macro to make it do even more. It also explains your options for storing macros so that you can use them from any workbook.

Applying formats is a fairly simple macro example, but you can record macros to do more complex tasks like retrieving and filtering external data, creating and customizing charts, and more.

Simplify frequent chores: record a simple macro

Let's say you work in a group that uses Excel to track accounts receivable. Every week you and your co-workers each submit a report in which your manager expects to see overdue amounts formatted so that they're easy to see: the numbers are bold and red and the cells have red borders. Here's how to record a macro to apply these formats:

  1. In the workbook where you track your accounts receivable, click one of the cells you're going to format.
  2. Point to Macro on the Tools menu, and then click Record New Macro.
  3. In the Record Macro dialog box, type a name for the macro in the Macro name box. Macro names must start with a letter and can include letters, numbers, and underscore characters, but can't include spaces. You don't need to change the other boxes:

Record Macro dialog box

When you click OK, the Stop Recording toolbar appears, and you're ready to record. Until you stop the recording, every Excel command and keystroke will be recorded in the macro, in the order in which they are entered.

  1. Now format the cell the way your boss wants it flagged: click Cells on the Format menu, click the Font tab, click Bold under Font style, click Red for Color, click the Border tab, click Red for Color, click the border thickness you want, click Outline, and then click OK.
  2. To finish recording the macro, click the Stop Recording button:

Stop Recording toolbar and button

Now you have a macro that can perform in a single operation all 12 mouse clicks that it took to format the cell.

Use the macro you created

The next time you need to flag a cell, you can run the macro. If you're going to use the macro frequently, you can create a toolbar button for it, or assign a keystroke for it, or both.

Run your macro using the Tools menu

  1. Click the cell you want to format.
  2. On the Tools menu, point to Macro, and then click Macro.
  3. Click the name of your macro, and then click Run.

Create a toolbar button that runs your macro

  1. On the Tools menu, click Customize, and then click the Commands tab.
  2. Under Categories, click Macros.
  3. Drag the custom button to the toolbar where you want it.
  4. On the Customize dialog box, click Modify Selection, and then click Assign Macro.
  5. In the Assign Macro dialog box, click the name of your macro, and then click OK.
  6. To change the appearance of the button, click Modify Selection again, point to Change Button Image, and click one of the available images; or click Edit Button Image and use the Button Editor to create your own image.
  7. Click Close.

Assign a keystroke to run your macro

  1. On the Tools menu, point to Macro, and then click Macro.
  2. Click the name of your macro, and then click Options.
  3. In the Shortcut key box, type the key to use along with CTRL to run your macro.

 Note    Avoid using a keystroke that's already used for other Excel operations, such as CTRL+C for copy.