Filter Data in Ms Excel With a Macro Button
Filter Data in MS Excel With a Macro Button
Introduction to Assigning a Filter to a Macro Button
Creating a macro button to automate Excel tasks can save you a lot of trouble. Keep in mind that it doesn’t take an intermediate Microsoft Excel user to learn this task. Creating a macro button is easy after a little practice. Just about any type of process in Excel can be recorded by you and saved to a button. The button can then be utilized to cut task times down to seconds. Once upon a time, I found my boss asking for updates from the same Excel spreadsheet at least 4 times daily. I soon figured out how to automate the sorting and filtering of columns to create my report by assigning these tasks to a button. Let's get started to see how this works.
Save the Workbook With the Right File Extension
Before creating macros in Excel, you should first save the workbook that you are using as a macro-enabled .xlsm workbook so that you can test the document while you work. For example, if I record a macro and assign that macro to a button, I will be able to test that button if the document is saved as an .xlsm spreadsheet. Additionally, while creating macros you should close all other Excel documents that you are not working on.
File Extension Change
Add the Developer Tab
Before continuing with this tutorial, you must have the developer tab enabled. This tab will allow you to add form control buttons to your spreadsheet later in this tutorial. If you don't already have the tab enabled find out how to enable it here.
Developer Tab Location
Start With the Sample Data
Let's consider the sample data below. If you would like to follow along, please recreate the table below or download the sample data here. For simplicity, the data set was reduced to seven records.
Add Sort and Filtering to the Table
The only thing that this table of data needs before proceeding is sorting and filtering capabilities. Sorting and filtering can be added to the table with a few steps. First, select all of the column headers in the table and click on home tab. Next, go to the editing section to the right of the screen and click on sort & filter. A menu will drop down and you will need to select the filter option. This will enable sorting and filtering for each column of the table.
Adding Sort & Filter Capabilities
Stage Area for Macro Button
Now an area needs to be created at the top of the spreadsheet for the button that will soon be added to the workbook. I will choose line 1 and increase the height of this line to 50 to add some space. Now I can insert my form control button in this new space and not have to worry about my work in the spreadsheet affecting the button.
Making Space for the Macro Button
Inserting the Button
Inserting a form control button requires a small number of steps. First, the developer tab needs to be selected. Now click on the insert button. This button will display several options, but I want to choose the form control button option which you can see circled in the illustration below. Now a square area can be traced with the cursor. Trace a square where ever you want the button to appear.
Selecting the Form Control Button
After tracing the button, a window will appear. Create a macro name and press the record button. Another window will appear with some options. Ignore these options and click Ok. At this point you must be careful because any click made within the Excel program will be recorded into a macro program that you are recording. Basically, as you work in Excel with this record feature running you are writing lines of code for the macro to read later when the button is pressed. If a mistake is made you can simply press the back button or restart the whole process.
Start Recording the Macro
Create the Sort
The data needs to be sorted so that the highest cost appears first. To sort by cost, the cost header needs to be clicked and sort from Largest to Smallest needs to be selected. This task is shown in the illustration below.
Sorting By High Cost
Creating the Filter
Next, all records that contain a paid date will be filtered out. This can be accomplished by clicking on the date paid header dropdown and deselecting the select all check box and then selecting the blanks check box. This task is illustrated below.
Selecting the Filter
Stop Recording the Macro
To stop recording the macro the small black square at the bottom left-hand corner of the screen needs to be clicked. After the stop button is clicked it will turn into a record button. The stop button is shown in the illustration below.
Create a Button to Clear the Filter
One thing you may want to do after you filter the data is revert the table back to the unfiltered state. Instead of taking the steps to clear the filter each time you need to, you can create another button to complete this task. To create this button the same steps are taken as were stated earlier and change after you start recording. When the macro is recording go to the home tab and click on the sort & filter button in the editing section. Select the clear filter button. This button can be seen in the illustration below. Now that the filter has been cleared stop running the macro by clicking on the square stop button in the bottom left-hand corner of the screen.
Clear Filter Button
Label the Buttons
Now you can label the buttons to display the type of function that they provide when they are clicked. To edit the buttons simply right click on them and select the exit edit text option. Your cursor will move to the front of the button text to be edited. The text style can also be changed by right clicking on the button and choosing format control.
Edit Button Names
© 2018 James Smith