Create a Macro Button in MS Excel 2016 to Filter Data

Author:

Joshua is a graduate student at the USF. He has interests in business technology, analytics, finance, and lean six sigma.

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.

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.

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.

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 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.

References

Crowder, J. (2020, January 1). How to Add the Developer Tab in Excel. Retrieved January 1, 2020, from https://youtu.be/nskuG6pK5ig.

How to Create a Button in Microsoft Excel That Opens a Calculator
Shows a Microsoft Excel user how to create a button that opens up the Windows pre-installed calculator application from an Excel spreadsheet.
How to Add the Developer Tab in MS Excel 2016
This article shows you how to navigate through the MS Excel options menu to enable visibility of the developer tab within the Excel main menu.
Create a Macro Button That Will Clear All of Your Work Within an Excel Worksheet
Shows a Microsoft Excel user how create a button and record a macro that will clear a workbook when the button is clicked.
How to Create a Macro Button to Save & Close an Excel Workbook
The article describes how to create a macro button on an Excel spreadsheet that will save and close a workbook to avoid having to select both operations.

To learn more about developing in Excel, I recommend taking a look at the book Microsoft Excel 2019 VBA and Macros (Business Skills) by Bill Jelen to enhance your skills.

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.