Use Excel Macros to Speed Up Work
Updated: July 21, 2003
Curriculum Areas: Language arts, math, science, economics
Grade Level: Middle school and up
Application: Microsoft® Excel version 2002
Tip: Create a Microsoft Excel macro to automate repetitive tasks.
Description:
When you find yourself performing a task or series of tasks repeatedly in a spreadsheet, it's time to create a macro. A macro is a series of commands and functions that are stored in module based on Microsoft Visual Basic® that is attached to a workbook (file). You can run the macro whenever you need to perform the task(s). You will see that a macro will perform tasks very quickly.
In Excel, you can record a macro, which means that Excel records each step you take—that is, each command you choose—as you perform the task. You then run the macro to repeat the commands. Keep in mind that if you make a mistake when you record the macro, the corrections you make are also recorded. After a macro is recorded, you can change and update it by using the Visual Basic Editor.
Think about the tasks that you need to automate when you work with certain types of spreadsheets. For example, you could create a macro that quickly hides a group of monthly columns so that only the summary data shows. You could create another macro to show all of the details again. Another example would be a macro to format cells so that long headings wrap around the cells.
How To:
1. | Start Microsoft Excel version 2002, and either open a file in which you need to create a new macro or start from a new spreadsheet. |
2. | Think through the steps that you want to record in a new macro. |
3. | When you are ready to create a macro, select Macro from the Tools menu, and then select Record New Macro. |
4. | Type a name for the new macro. Notice that the first character of the macro name must be a letter and spaces are not allowed. Other characters can be letters, numbers, or underscore characters (which are good to use to represent spaces). |
5. | Type either a lowercase or capital letter as a way to execute the macro using the keyboard. This will let you run the macro just by typing CTRL+ the letter that you chose. |
6. | Select where you want the macro to be recorded, such as in the current workbook. However, if you want the macro to be available to all Excel workbooks, select Personal Macro Workbook. |
7. | If you want to, type a description of the macro in the Description box, and then click OK. |
8. | Start performing the commands and actions that you want the macro to record. |
9. | When you are finished, click the Stop Recording button (the blue square) on the Stop Recording toolbar. |
10. | Whenever you need to perform the macro in the spreadsheet, either press the keyboard shortcut you defined, or select Macro from the Tools menu, and point to Macros. Click the macro that you want to run, and then click Run. |
11. | To see what the macro commands look like in the Visual Basic Editor, select Macro from the Tools menu, and point to Macros. Click the name of the macro that you want to see, and then click Edit. Close the Visual Basic Editor when you're finished exploring. |
