Learning Outcomes
- 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.
Practice Questions
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.
Practice Questions
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.
