How to Learn MS Excel Macros

A macro is a set of functions and commands that can be recorded and played back in Microsoft Excel. Macros are convenient for anyone who must perform the same task over and over in their work. If you regularly calculate the average of numbers in Excel and add a dollar sign to the result, you can set up a macro that will automatically add a dollar sign each time you use the average function. Learning to use macros effectively can save you a lot of time and work, and Microsoft offers an easy tutorial on working with macros in Excel.

Difficulty: Moderate

Instructions

Learn How to Record a Macro

1. Access the "Macro" menu from the "Tools" menu and click on "Record New Macro." This will open a dialog box in which you will type a name for the macro. When you click the "OK" button, the macro will automatically begin to record and a small window with a "Stop" button will appear.

2. Perform the set of functions that you would like to have in your Macro. If you would like to average a set of numbers, add a dollar sign and put a black border around the cell, carry out all of those tasks. The functions and commands you perform will record in the order in which you complete them.

3. Press the stop button to cease recording. You now have a macro that can average numbers, add a dollar sign and put a black border around the call with just one click.

Learn How to Run a Macro

4. Get to the "Macro" option through the "Tools" menu. When you click on "Macro," it will bring up a list of all available macros saved in your Excel.

5. Make a button for the macro to add to your toolbar. To create the macro button, go to the "Tools" menu and select "Customize" from the list. Go to the "Commands" tab and click "Macros" under the "Categories" section. Drag the custom button of your choice to the toolbar. Go to "Modify Selection" in the "Customize" window and click on "Assign Macro". Choose the name of your macro from the list of macros that comes up and press the "OK" button. The button will now stay on your toolbar and with one-click will perform all of the recorded functions.

6. Create your own keyboard shortcut. This will allow you to run the macro by pressing a combination of keys on the keyboard. To make a shortcut, click on "Macro" under the "Tools" menu. Choose your macro from the list and go to "Options". A window will come up with a place for a "Shortcut key". Pick a key to use as the shortcut for your macro, keeping in mind that some keys are already shortcut keys in Excel, such as Ctrl+P for paste and Ctrl+X for cut. After you have entered your key, click "OK." Your macro will now run automatically when you press the Ctrl key and the key that you assigned for your macro.

References

• About Macros
• How to Work with Excel Macros