Create a Macro Button in MS Excel 2016 to Filter Data

Updated on March 1, 2018
Joshua Crowder profile image

Joshua has work experience in manufacturing, distribution, and aerospace. He received his BBA in accounting from Kent State University.

Having a custom button close can help you save time with a repetitive task.  This can be extremely valuable if you can save 2 or steps.
Having a custom button close can help you save time with a repetitive task. This can be extremely valuable if you can save 2 or steps.

Introduction

The purpose of this tutorial 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. 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.

Practice Document

Use the link above to open up the excel document that was made for this article.  You can use it to complete the steps as you read.
Use the link above to open up the excel document that was made for this article. You can use it to complete the steps as you read.

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.
Note : You can place your button anywhere on the document that works for you.

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

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.

When recording is finished you can click on this stop icon to save the recorded macro.
When recording is finished you can click on this stop icon to save the recorded macro.

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.

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

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.

To save your document as a macro enabled document you must use the .xlsm file extension.
To save your document as a macro enabled document you must use the .xlsm file extension.

Questions & Answers

    © 2018 Joshua Crowder

    Comments

      0 of 8192 characters used
      Post Comment

      No comments yet.

      working