Excel macros are a convenient way to save you time and energy as a great alternative to formulas. Instead of entering repetitive tasks, like calculations, you can use macros to continually do the work for you. Unlike simple formulas that just give you back a value, macros can alter cell formatting, sort rows/columns, calculate data, and they can even use functions as a way to figure out the best answer. No computer programming degree required. All you need to do is record your keystrokes and read the helpful tips below to quickly master macro recording.
How to Record a Macro
- Start by opening the spreadsheet that you want to use a macro for.
- Click the “Tools” option in the Home Ribbon and click the dropdown button under Macros, then select Record New Macro.
- You can name your macro in the window that pops up for convenience.
- (optional) You can also assign a shortcut key. Note that this action will automatically replace the functionality the key may have had before.
- Always list a brief description in the main box of the template so you don’t forget what it does.
- if you want to use the macro often you should enter it into your Personal Macro workbook.
- Click the “OK” button.
- Follow these steps when creating your own macro.
- Click Macros in the ribbon and choose “Stop Recording” when you’re finished.
- Voila! Your macro is complete.
Important Notes about Running Macros
- Planning ahead and entering notes about your macro will reduce the amount of errors while recording.
- Macros have strict naming conventions so be careful. Just don’t use spaces in your title and you should be fine. You can however use underscores and hyphens with your words.
- Remember the longer it takes to record your macro the longer it will run.
Running a Macro
- Open the sheet you want to use your macro with
- Again, click the “Tools” option in the Home Ribbon. Then trace to Macro, and then Macros.
- You will see the macro you created in your list, simply click on it.
- You can also just use the keyboard shortcut you created for it as well.
Message when Exiting Excel
A message will appear when exiting Excel asking if you want to keep the changes you made to the macro for your personal macro workbook.
If you plan on using the macro again just select “Yes”.
Macro Security Levels
If you receive a disabled macro message after trying to run one of your macros, this means you need to change your macro security level. Go to Tools/Macro/Security and simply change the level to medium to give the option to enable or disable your macros in the future.
Download: Macro Quick Start Guide