How to Record an Excel Macro (Part 2)

Do you want a practical way to make repetitive tasks in Excel easier and faster?

Say you receive a spreadsheet every week from a reseller that markets your products. You perform the same changes weekly to adjust the format of the sheet to your company’s standards. You also run a series of custom calculations to help you evaluate the reseller’s performance. These calculations involve adding a formula to the same columns and cells every week. The answer: record a macro! (Click here for Part 1)

Macros automate repetitive tasks to speed up and streamline work in Excel. They are collections of commands that perform pre-determined actions in your worksheet. Macros can either be recorded (Excel tracks and saves your actions) or programmed (written in VB—Visual Basic—code in a special editor.)

You can record a series of actions that sets up your spreadsheet to look a certain way or organizes your data in a particular fashion. Macros can also be used to run a series of calculations on a collection of data. If you always prepare a report by multiplying the items in a certain column by the corresponding items in the next column, you can set up a macro that performs that action each time you need it done with one keystroke.

Record a Macro

This macro takes raw sales data from a report and converts it into user-friendly information. The report used in this example has three columns.

• Column A contains the number of the store that made each sale.
• Column B contains the date each sale was made, but the date is expressed in the native Microsoft Excel numeric format, meaning that it is just a series of seemingly random numbers.
• Column C contains the amount of each sale, but the prices are formatted as numbers rather than currency.

The goal in this example is to create a macro that converts the Transaction Date data to a standard date format and the Amount to dollars.

1. Make sure the Developer tab is enabled. The tab is enabled if it is visible on the ribbon.
2. Click the Record Macro button [A] in the Code group on the Developer tab. 
3. In the Record Macro dialog box:

• Provide a name for your macro in the Macro name: field. Create a descriptive name rather than something like “macro1.”
• Identify a shortcut key (it must be a letter) for your macro in the Shortcut key: textbox. This is the key you press on your keyboard to run your macro after recording it.
• Choose a location where you want your macro to be stored from the Store macro in: dropdown [B].
• Provide a clear description of what your macro does in the Description: field so you (and others) can remember its purpose.

4. Click the OK button [C]. If there are no problems with your macro information, recording begins immediately. If there are problems (such as if the shortcut key you selected was already in use), correct the problems and then press the OK button again. 
5. Perform the actions you want to record in your macro. The actions recorded for the sample macro are listed here:

• Select Column B, then use the tools in the Format Cells dialog box (accessed from the Number group on the Home tab) to format the data as MM/DD/YYYY.
• Select Column C, then use the tools in the Format Cells dialog box to format the data as currency ($0.00).

6. Click the Stop Recording button [D] in the Code group on the Developer tab. 
7. To run your macro, click the Macros button [E] in the Code group on the Developer tab to launch the Macro dialog box [F]. Select the macro you want to use, then click the Run button [G].  

Now, you can record the actions, save it as a macro, and then make those changes with a single click going forward. All that’s left is brainstorming a few things to do with all of that saved time!