Create a Macro Button in MS Excel 2016 to Filter Data
The purpose of this article is to show a medium skilled MS Excel 2016 user how to add a button to an Excel spreadsheet with a macro attached to it. Recording a macro that is assigned to a button is an easy way to repeat a repetitive task. Filtering records in a data set is the task that will be eased.
Add Developer Tab If Necessary
First, make sure that you add the developer tab if you don’t already have it on the main tabs section at the top of your Excel screen. If you need to add the developer tab, please click here to find out how before continuing. The screenshot below shows the default location for the developer tab. Download this .xls file from Google Docs after opening it in another tab. This will allow you to follow along with the instructions below.
Make Room for the Button
Insert a blank line into line 1 of the document above your headers. If you would like for your button to appear larger, adjust the row height to a larger size. Also, make sure that your headers are set to filter data.
Insert a Button
Now click on the developer tab, then click on the insert button. Under form controls, click on the form control button in the top left-hand corner. The button has an arrow pointing to in the screen shot below.
Record a Macro
Now take the cursor and drag a box where you want your button to appear. Afterward, the “Assign Macro” window will appear. Type UNPAID in the area reserved form "Macro name:". After you have assigned a name with no spaces, click on the record button in the Assign Macro window. If a "Record Macro" window appears, just click the OK button.
You know the macro is recording because you will see a square shape in the bottom left-hand corner of your screen to the right of the word ready. Now the macro is recording every step that is taken with your cursor in the Excel program.
Program the Button to Filter the Date Paid Column
Now we can simulate what we want our button to do. Since all unpaid invoices don’t have a paid date, I will have my macro only display “DATE PAID” dates with a blank cell. Now click on the sort/filter button on the "DATE PAID" header and deselect the “Select All” option. The screen shot below shows the option after it has been deselected.
Next scroll down and select the "(blanks)" option and click on OK.
Stop Recording and Adjust the Button
Now click on that square in the bottom left-hand corner that we reviewed earlier. This will save the steps that you just created to the button. Rename the button by right clicking on it. Chose "Edit Text" from the drop down menu to enable you to back space the default name. Rename the button UNPAID INVOICES and click in another cell. You can also resize the button by selecting it and dragging the borders to the size of your liking.
When you click on the button you will notice that the only records that display do not have an invoice date. If you would like to easily revert back with another button, follow the same instructions to create a button but give the macro a different name like revert. When recording the macro deselect the "(blanks)" selection and click on the “Select All” option when filtering the "DATE PAID" column header. Name the button REVERT and click on the square button in the bottom left-hand corner of your Excel screen. You are almost done.
Save your File as an Excel Macro-Enabled Workbook
Now you must save the file as a macro enabled excel file. If you don't do this your button will not work. Also, whenever you open this file you must enable the macro after Excel starts for this file.
If you are using MS Excel 2016 at the beginners level or medium level, I recommend the textbook that I have provided below. I was able to navigate through the book from start to finish with no problems. After reading the benchmark series, you should have a good working knowledge of MS Excel 2016.
© 2018 Joshua Crowder