Macros Made Easy: How to Excel at Microsoft Excel

The What

So, what exactly is a macro? A macro is a series of computer steps that are recorded and repeated as part of a shortcut to automatically run those commands with a single mouse click or keystroke.

The Why

When would a macro be beneficial? Excel macros are extremely useful when you have common tasks that you perform frequently. For instance, they would allow you to format data the same way every time you receive it.

The How

Fortunately, it's easy to create a macro. To better understand this concept, picture a simple "how-to" video. If, every day, you had to teach a different audience how to change a light bulb, it would make sense to record an instructional video instead of doing the same thing every time. You would take the camcorder out and press "Record," demonstrate all the steps of replacing a light bulb, and then press "Stop." Now, instead of having to physically walk through each step with each audience, you could simply press one button and show them the video. That's how macros work in a computer program.

The Example

Utilizing Excel 2007, let's assume that I want to deliver a weekly report that shows accounts with past-due balances. Also, I want these balances to be displayed in red with a red border around the cell.

1. In your accounts worksheet, click on one of the cells that will be formatted in this manner.

2. Go to View --> Macros --> Record Macros.

3. Give the macro a name that will make sense if you need to choose it from a list of macros in the future. In this instance, we'll call it "Past_Due_Accounts". (The name field is picky - it must start with a letter and may include letters, numbers and underscores, but it can't contain spaces.)

4. I want this macro to run when I press the "Ctrl" and "P" keys, so I will enter "P" in the "Ctrl +" box. Excel automatically changes this shortcut to be "Ctrl + Shift + P" because "Ctrl + P" is already a keyboard shortcut. For now, leave the default to store the macro in This Workbook, and enter a short description such as, "Macro to show past-due accounts. Created on 03/10/2009."

5. Click the "OK" button.

6. Looking in the lower left-hand corner of the worksheet, notice how the macro is now in "Ready" mode and a square appears to be clicked when it's finished recording.

7. Choose the "Home" tab and right-click on one of the cells to which you will apply this formatting. Click "Format Cells" and then the "Font" tab, and select the color red. Click "Border Tab." Choose red for "Color" and click the "Outline" button. Then click "OK."

8. Click the square at the bottom left-hand corner of your screen to stop recording the macro. Alternatively, you can also go to View --> Macros --> Stop Recording. You have now created a macro that can be run by simply holding down the Ctrl, Shift and P keys. Click on any cell that you want to appear in red font with a red outline, and then press Ctrl + Shift + P. You can also apply this macro to multiple fields by holding down your Ctrl key and clicking all the cells on which you will run it. Release the Ctrl key with those cells selected, click Ctrl + Shift + P, and your macro will run on those fields.

To edit a macro: Go to View --> Macros --> View Macros. Select the macro you want to edit, and then click on "Edit".

Now that you understand the concept and function of macro-building, below are the same instructions applied to Microsoft Excel 97-2003:

1. In the top menu, place your mouse over Tools --> Macro --> and click "Record New Macro."

2. The same "Record Macro" dialogue box will pop-up; enter a macro name as described above and click "OK."

3. Perform the steps of each process you want to build into the macro. To stop recording, either go back to Tools --> Macro and click "Stop Recording" or simply click the square in the pop-up box that appears when you began recording your macro.

Be creative and use macros to perform conditional formatting, formula applications, table functions, and more. If it makes your life easier, then use it!