Writing Macros with Excel 2010

Excel 2010 offers a function enabling the user to record a specific set of actions that get repeated in an identical fashion numerous times. These recorded actions are known as macros, and once recorded, can be applied through a single mouse click or by using an assigned keyboard shortcut.

Consider a real estate website that allows a user to download Excel files containing lists of houses for sale, with a row for each address and a column for various attributes of the property. For this example, we will have separate columns for the street number, street name, zip code, asking price, and number of bedrooms. Now, let's consider the case where we have multiple spreadsheets of this type where we want the entries sorted with all properties in each zip code grouped together and listed by price from highest to lowest. The first time we perform this sort, we will record a macro for the action, assign a shortcut key, save to our personal macros file and apply the macro to the remaining sheets.

Start by opening one of the spreadsheets downloaded from the website. Create a new macro by clicking "View" on the ribbon interface. Click on "Macros" on the far right side of the ribbon, and choose "Record Macro" from the drop down menu. This will open a "Record Macro" dialog box with options to enter the macro name, a shortcut key, options for where to store this macro and a text box for entering a description of the macro.

Name the macro "Property Sort", assign the shortcut key Ctrl + s, and choose to store the macro in the "Personal Macro Workbook." By saving the macro in our personal workbook, we can use it in all remaining sheets without having to move them all to the same workbook.

Clicking on OK in the "Record Macro" dialog box starts the process. Each action performed from this point to the end is recorded and will be executed every time the macro is run. To perform the sort, click on the "Data" tab of the ribbon and choose the "Sort" button. In the sort dialog box that opens, choose the option "My data has headers," and enter the parameters for the desired sort order. First, sort by zip code on values in A to Z order. Click on the "Add Level" button, and sort by price on values from Z to A. Click OK, and the sort will be executed.

With the sort completed, we need to stop the recording. Navigate back to the "View" tab of the ribbon and click the "Macros" button. Click on "Stop Recording" to complete the process. To use the macro, open another of the real estate sheets that needs to be sorted. Once open, key in the shortcut command entered while creating the macro to execute the recorded sort. If you don't remember the shortcut, choosing "View Macros" from the View/Macros menu will list all recorded macros available. Choose the desired macro from the list and click run.

This process of recording a macro can be repeated for any other tasks desired such as formatting the cells for printing, applying borders, or concatenating the street number and street name fields.

View our Excel class schedules

Related Courses