applied tips : march 2008

microsoft excel : macros made easy

Create macros to perform common tasks

If you find yourself repeating the same steps over and over in Excel, a macro may be just what the doctor ordered.

Macros let you store a series of commands or functions that you can then summon by the touch of a key or the click of a menu, saving you the hassle of repeating a bunch of manual steps.

Macros can be especially useful for grouping together several formatting commands. For example, if you always make the headings on your worksheets 12 pt. Garamond font, italic, and red, you can record a macro that rolls all those formatting attributes into one command. Then, the next time you’ve got an unformatted header, you can just select the cells you want to format and run the macro.

Working with macros involves two main parts: recording the macro and running the macro. First let’s look at how to record, or create, a macro.

Recording a macro

To create a macro, you need to perform a series of actions while Excel records your every move. To start recording, open a worksheet and select Tools > Macro > Record New Macro from the menu. The Record Macro dialog box appears.

First you need to give the macro a name to identify it. The first character of the name must be a letter, and no spaces are allowed in the name—although underscores are. Also, don’t give a macro a name that is also a cell reference.

Next, enter a keystroke shortcut for the macro. A shortcut key will allow you to quickly run your macro once you’ve created it. All shortcuts include <Ctrl> + <a letter>. If you use a capital letter, you’ll also need to press <Shift> to run the shortcut.

You can’t use numbers or symbols as shortcuts, and if you create a shortcut that’s the same as a default Excel shortcut, your macro shortcut will override it while the workbook that contains the macro is open. For example, if you press <Ctrl> + <S>—which is normally the shortcut for the Save command—your macro will run instead of the Save command.

Now you need to decide where you want to store the macro. Click the “Store macro in” list arrow and select from This Workbook, New Workbook, or Personal Macro Workbook. To make the macro available whenever you use Excel, select Personal Macro Workbook. Most often, you’ll probably choose This Workbook.

Next you can edit the description of the macro in the Description text box. By default, the description will read: Macro recorded <Date> by <Username>. Now click OK to start recording. The Record Macro dialog box disappears, and now, everything you do will be recorded as part of the macro.

Perform the actions you want to include in the macro, such as formatting or other commands, and once you’re done, select Tools > Macro > Stop Recording. Or, you can click the Stop Recording button on the Macro toolbar instead.

Running a macro

Now when you return to the workbook containing the macro, and you want to run the macro, just press the shortcut keys to run it. An alternate way to run a macro is to select Tools > Macro > Macros from the menu, select the macro you want to run from the list, and click the Run button.

You can also manage your macros by selecting Tools > Macro > Macros from the menu and selecting the macro in the list. You can click the Edit button to edit the macro in Microsoft Visual Basic mode, the Delete button to delete the macro, or the Options button to change the shortcut key.

schedule a class on excel

Request an on-site Excel class from Applied Office. Sessions are just $125/hr and your employees will be shown how to create spreadsheets, charts, and use formulas that will save them a lot of time.  Learn more here

quick reference card

Get the Quick Reference Card on Microsoft Excel! Download it for free and print it on your own printer. You might even want to laminate it.