To follow using our example, download MacrosForBeginners.xlsx
Once you get comfortable navigating Excel, it won’t be long before you start to hear about this wondrous feature called a “Macro” that can leap tall projects with a single keystroke and make boring tasks go by faster than a speeding bullet. While Macros aren’t really the superman of Excel features, they can make you look like a Super Hero. Here’s a brief tutorial to get you started.
What is a macro?
Think of a Macro as a program or script that performs several tasks in sequence. You can “write” this program by simply recording the steps for each task as you are doing them. When you play back the recording, the program runs, and the steps are repeated.
The programming language that executes macro programs is called Visual Basic for Applications or VBA. When you use the macro record feature, Excel is helping your write a VBA program. Advanced VBA programmers can write very complex macros that perform tasks beyond what a user can simply record by hand.
When should I use macros?
A repetitive task that involves several steps is the perfect candidate for a macro. Do you have to re-format a report every week from a vendor into your department’s style and formats? Are you updating an old document to include new data line by line? Do you type the same loooooong company name over and over?
Ask yourself: Have I performed this same set of steps three times, and do I anticipate I will do it at least five more times? If the answer is yes to both, then it is probably worth your time to record a macro.
How do I record a Macro?
- Click on the Developer tab and look for the Record Macro button in the Code group.
If the Developer tab is NOT visible, follow these steps:
- Click on the File tab.
- Click Options.
- Click Customize Ribbon.
- Put a checkmark by Developer in the Main Tabs pane.
- Click OK and continue with the steps to record a macro.
- Fill out the Record Macro dialog box:
- Macro name must begin with a letter and can’t contain spaces, symbols or punctuation.
- Choose a shortcut key if you wish to activate the macro with a keyboard shortcut. Warning! If you choose a key that is already used by Excel, the macro will over-rule it. Don’t choose Ctrl+c, for example, or you will not be able to copy using the shortcut.
- Choose This Workbook to save the macro in the same workbook you are editing.
- Write a description so you will remember what the macro does.
- Click OK.
- Perform the steps that you wish to record.
- When you are finished, click the Stop Recording button on the Developer tab, or the task bar.
How do I play my macro?
- Click the Developer tab.
- Click the Macros button.
- Select the macro you want in the dialog box.
- Click Run.
Or, if you saved a shortcut key, type the shortcut!
How do I save my macro?
Because macros are programs running within your Excel document, you will need to save the workbook as a Macro Enabled file type. This tells Excel to load the programs when it opens the workbook again. To save a macro enabled Excel file:
- Click the File tab.
- Click Save As.
- Browse to the folder where you wish the workbook to be saved.
- Choose Excel Macro-Enabled Workbook in the Save as type: dropdown menu.
- Type a name for the file and click Save.
That’s it! With a little thought, you are sure to find many time-wasting repetitive tasks that macros can rescue you from on a daily basis. To see our example macro in action, download MacrosForBeginners.xlsx and follow the steps above to run FormatEmployeeSheet.