8.11: Recording a Macro
Learning Objectives
- Use a macro to add a filter to column headers
- Use a macro to remove duplicates
- Use a macro to select blank rows
In business, you may have spreadsheets filled with data from different departments, divisions or even other companies requiring updating, reformatting or cleaning up on a regular basis. There are a few macros examples that help make this repetitive work easier.
These next spreadsheet actions are created from the View > Macros > Record macro path.
Adding Filters to Column Headers
Each quarter you receive a spreadsheet filled with information. To make it easier to sort data you’d like to add filters to each new spreadsheet. Here are the steps to follow to make a macro for this:
- Open worksheet > View > Macros > Record Macro
-
In
Record Macro
dialogue box;
- Type in macro name with no spaces or by using _ to connect words (e.g. Filter_Macro).
- Create a shortcut key (optional). If you do choose one, make sure it isn’t Ctrl + C, Ctrl + V, or other already existing shortcut keys.
- Decide where to store the macro recording.
- Add a description (optional) to help identify the functions done by the macro and click OK . The macro is now recording.
- Highlight the entire row of column headers > Data tab > Filter button.
- Go back to View > Macros > Stop Recording . This macro is now available to use on the next set of data needing filters added.
- Run macro: To run the macro on the new set of data open the data, go to View > Macros > View Macros . A dialog box will open then select the macro for adding filters and click Run .
Watch this short video to see these tasks done in sequence and used on a fresh data set.
Remove Duplicates
Spreadsheet data is often filled with duplicate information and needs to be sorted through in order to be useful. For this example, a single list of the salespeople is needed from the large data set. To start recording the macro, follow the order in the example for recording above, then follow these steps:
- Highlight the column containing the Salesperson names. Right click > copy OR use Ctrl + C
- Open a new tab > select a cell > right click and paste OR Ctrl + V
- Click Data tab > Remove Duplicates button
- Go back to View > Macros > Stop Recording . This macro is now available to use on the next set of data needing duplicates reduced.
- Run macro: To run the macro on the new set of data open the data then go to View > Macros > View Macros . A dialog box will open, select the macro for removing duplicates and click Run .
Watch the short video to see these tasks done sequence and used on a fresh data set.
Selecting Blank Rows
Many times, a worksheet or data set is filled with blank rows that can stop a successful analysis of the data or cause errors in formulas. In order to clean up a data set with a macro, follow these steps.
Like with the other functions, open the spreadsheet and set up a macro for recording. An alternative way to quickly record is to select the record button at the bottom left corner of the Excel window to start.
- Create a new column > label it Empty (or another name). Make sure it has a filter added in the header.
- In the first cell, type in the function that counts how many values are in the list of arguments, that function is = COUNTA(B4:G4) your table range and press the Enter key.
- With the H4 cell highlighted, pull the corner down the entire column covering all the rows.
- Click filter arrow in “ Empty ” header and deselect “ All ” the select “ 0 ” and press OK .
- Select all the rows that are “ 0 ” > right click delete
- Click filter arrow in “ Empty ” header, then select Clear filter from “Empty” and data rows appear with no empty rows.
- Go back to View, Macros and Stop Recording . Alternatively, click the little square in the bottom left corner to stop the recording. This macro is now available to use on the next set of data needing duplicates reduced.
- Run macro: To run the macro on the new set of data open the data, go to View > Macros > View Macros . A dialog box will open, select the macro for selecting blank rows and click Run .
Watch the short video to see these tasks done in sequence and used on a fresh data set.
Practice Questions
Note
Unlike other actions, running a macro cannot be reversed with the Ctrl + Z (Cmnd+Z) short-cut or the Undo arrow so make sure you have a copy of the original data worksheet before running a macro for the first time.
Macros take a little practice to get used to, but once you use them more frequently, you will see the variety of things that a macro can accomplish. Remember, a macro allows you to record operations and re-use the sequence of mouse actions or keystrokes of anything you can do in Excel with keystrokes or a mouse. Start using them and see how far you can go.
Contributors and Attributions
- Recording a Macro. Authored by : Sherri Pendleton. Provided by : Lumen Learning. License : CC BY: Attribution