Create a Macro Button in MS Excel 2016 to Filter Data
Joshua is a graduate student at the USF. He has interests in business technology, analytics, finance, and lean six sigma.
Custom Macro Button
Having access to a custom macro button can help you save time with a repetitive task. This can be extremely valuable if you can skip steps by using the button to complete a task.
Created by Joshua Crowder
The purpose of this tutorial is to show a medium skilled MS Excel 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. Keep in mind that you are not restricted. There are possibly hundreds of types of tasks that you can automate to help maximize efficiency in your document.
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.
Adding the Developer Tab
Practice Workbook
Use the link above to open the Excel document that was made for this article. You can use it to complete the steps as you read.
Created by Joshua Crowder
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.
Note: You can place your button anywhere on the document that works for you.
Created by Joshua Crowder
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.
When you click on the form control button the button will not automatically appear. You must draw a box with your cursor immediately after you select the control button.
Created by Joshua Crowder
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.
Be aware that after inserting the button here the button will not appear on its own. You must draw a square or rectangle with the cursor where you want the button to appear.
Created by Joshua Crowder
Macro Recording
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.
When recording is finished you can click on this stop icon to save the recorded macro.
Created by Joshua Crowder
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.
Created by Joshua Crowder
Next scroll down and select the "(blanks)" option and click on OK.
Notice that I am very specific about how I want to filter my data. You can filter your data how ever you want. You can also use multiple filters and even add a sort.
Created by Joshua Crowder
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.
Created by Joshua Crowder
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 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.
To save your document as a macro enabled document you must use the .xlsm file extension.
Created by Joshua Crowder
To learn more about using Excel macros for business applications I recommend the following book. I have been using the Excel Bible for years to improve my understanding of all aspects of this Microsoft product.
The Excel 2019 Bible
References
Crowder, J. (2020, January 1). How to Add the Developer Tab in Excel. Retrieved January 1, 2020, from https://youtu.be/nskuG6pK5ig.
Related Articles
How to Create a Macro Button to Save & Close an Excel Workbook
Create a Macro Button That Will Clear All of Your Work Within an Excel Worksheet
How to Add the Developer Tab in MS Excel 2016
How to Create a Button in Microsoft Excel That Opens a Calculator
This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.
© 2018 Joshua Crowder
