ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Filter Data in Ms Excel With a Macro Button

Updated on January 9, 2019
THEAMAZINGJAMES profile image

James likes to learn about technology and share what he learns through his articles.

Filter Data in MS Excel With a Macro Button

Repetitive spreadsheet tasks can be lame and downright tedious. More people need get on the macro programming train to make life a little easier while at the workplace.
Repetitive spreadsheet tasks can be lame and downright tedious. More people need get on the macro programming train to make life a little easier while at the workplace.

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

The .xlsm file extension creates a macro-enabled workbook for use in Microsoft Excel. This .xlsm workbook has the same spreadsheets functions with ability to save a host of embedded macros programmed in Visual Basic code.
The .xlsm file extension creates a macro-enabled workbook for use in Microsoft Excel. This .xlsm workbook has the same spreadsheets functions with ability to save a host of embedded macros programmed in Visual Basic code.

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

By default, the developer tab is not displayed in Microsoft Office programs. Adding the developer tab to the ribbon can allow users to run macros, create macros, use ActiveX controls, create applications and much more.
By default, the developer tab is not displayed in Microsoft Office programs. Adding the developer tab to the ribbon can allow users to run macros, create macros, use ActiveX controls, create applications and much more.

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.


Sample Data

The sample data above can be sorted and filtered automatically after a macro records the sorting and filtering of the data and is assigned to a button.
The sample data above can be sorted and filtered automatically after a macro records the sorting and filtering of the data and is assigned to a button.

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

All columns in the table need to have the sort and filter capability added to them so that the records sort and filter properly as a whole.
All columns in the table need to have the sort and filter capability added to them so that the records sort and filter properly as a whole.

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 a form control button requires a small number of steps. Since the spreadsheet that I am using here will be ongoing, I want this button to appear in a fixed and convenient location at the top of the spreadsheet.
Inserting a form control button requires a small number of steps. Since the spreadsheet that I am using here will be ongoing, I want this button to appear in a fixed and convenient location at the top of the spreadsheet.

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

A Form control button can run a macro that creates an action when a user clicks on the button. This button is known as a push button that can be set up to automate printing, filter data, and calculate numbers.
A Form control button can run a macro that creates an action when a user clicks on the button. This button is known as a push button that can be set up to automate printing, filter data, and calculate numbers.

Record

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

To get the sort and filter dropdown to display, the small arrow in the header needs to be clicked. This will display the column sort and filter options.
To get the sort and filter dropdown to display, the small arrow in the header needs to be clicked. This will display the column sort and filter options.

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

By selecting blanks, you are indicating that the only records that will appear In the table have no value inputted in the date paid column.
By selecting blanks, you are indicating that the only records that will appear In the table have no value inputted in the date paid column.

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.

Results

These results show the data after the filter has been applied. Every time this macro button is pressed the same filter and sorting will occur.
These results show the data after the filter has been applied. Every time this macro button is pressed the same filter and sorting will occur.

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

Adding a macro button that reverses the effect of another macro can also save you even more time.
Adding a macro button that reverses the effect of another macro can also save you even more time.

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

Comments

    0 of 8192 characters used
    Post Comment
    • THEAMAZINGJAMES profile imageAUTHOR

      James Smith 

      18 months ago from United States

      Greetings.

    • profile image

      Savuthy 

      18 months ago

      Hello

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://maven.io/company/pages/privacy

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)
    ClickscoThis is a data management platform studying reader behavior (Privacy Policy)