Nowadays, Excel files are loaded with numerous records. In such cases, it isn’t easy to find any information quickly. Microsoft Excel Filter formula will help us hide unwanted records and lets us view only necessary records on the worksheet.
Filter Records Through the GUI
Microsoft Excel Filter Formula
Before using Excel’s Filter, make sure that your data range has headers. The Filter will be applied to each column, and the header row will be used to identify the column name. Excel will not throw an error if the header row is missing. It will still continue to apply filter drop downs on the first row in your data range.
Follow the steps mentioned below to apply the Filter to records on an Excel worksheet:
Step 1: On the Excel ribbon, select the tab “Data.” Under the group, “Sort & Filter,” click on “Filter.” Now each column in the header row will have a drop-down arrow. Please note, before clicking the option “Filter,”; make sure that there are no blank rows in between records. If there is a blank row, the Filter will be applied only to records that are above the blank row, and the rows below the blank row will be skipped from filtering.
Step 2: If you want to include blank records, you must manually select the whole data range and then apply the Filter. If you do so, Excel will treat blank as a value, and in the filter drop-down, you could see blank as a filter criterion.
Step 3: Excel automatically recognizes the data type of each column in your data range. Based on the data type, the filter type varies on each column. On a column containing text, you would see Text filters, whereas you would see Number filters on a column with numbers. Similarly, on a column with dates, you would see Date filters. Microsoft Excel filter formula for Date allows you to filter your data using 35 different options.
Advanced Filter
Step 1: Before using Excel’s Advanced Filter, you might have to set up your data range. Make sure that your data has a column header and each header has a unique name. If names in your header are duplicated, Excel’s advanced Filter will throw an error. You should also ensure that there are no blank records in between your data. If there are blank records, delete them manually or send them to the last row by sorting records.
Step 2: Next step is using Advanced Microsoft Excel Filter formula to set up the range as a Criteria. Though this is optional, this is what differentiates Excel’s advanced Filter from the conventional Filter. You can set a range on any other worksheet as Criteria to your advanced filter setup. However, it would be very easy if the Criteria range is just above the data set. If your first record in your data set starts at row 1, then insert new rows at the top and push your data set below.
Copy Column headers from your dataset and paste them as Column headers to your filter criteria range. Always ensure that Column headers of your data set and Column headers of the Criteria range are the same. A mismatch in the column header will hide all records in your data set.
Step 3: We are now ready to apply the “Advanced” Filter. After selecting any cell in your dataset, select the tab “Data on your Excel ribbon.” From the group, “Sort & Filter,” click “Advanced.” This will bring up a user form. In the user form under Action, the option “Filter the list, in-place” will apply the Filter on the active sheet. You can then copy filtered records to a different sheet using the option “Copy to another location.”
The three text boxes on this form will allow you to define the range of your data set, the range of the filter criteria, and the new destination to which your records should be copied after the Filter is applied. Option “Unique records only” will help you to remove duplicates from filtered records. After choosing your options, press “OK” on the form, and your data will be filtered.
Advanced Filter is not automatic.
The advanced Filter will not recognize changes that you make to your data set or to the criteria change. After making changes, you must again click on the option “Advanced” from Excel. This time, when the form opens, all your previous values will reappear. You must press “OK” to reapply the Filter.
Reapply advanced Filter using VBA
Using VBA, we can automatically refresh the filter criteria.
Step 1: Within your Excel workbook, activate the worksheet with an advanced filter applied to your data set.
Step 2: Right-click on the worksheet tab, and from the list of the visible context menu, click “View Code.” This will open the Visual-basic editor.
Step 3: Paste this code into the VBA editor.
Do not miss updating placeholders with actual values. Now for any change that you make to your data set or to your criteria range, the advanced Filter will be reapplied.
Create Conditions in Advanced Filter
If you list multiple criteria in the same row, you create an ‘AND’ condition. The data set is filtered using two conditions.
Condition 1: The region should have the text “Asia.” Any character and any number of characters can be present before and after the text “Asia.”
Condition2: The population should be greater than 500,000 Records that pass both. These conditions will be visible on the sheet and records that do not pass both these conditions. If you list multiple criteria in a different row, you create an “OR” condition. Records with the text “Asia” in the column “Region” or records with a population greater than 500,000 passes the filter criteria and will be visible on the sheet.
Filter Using VBA
You can use a macro to filter large data sets. Here are some macros that you can paste into a new module and invoke to filter records. Replace placeholders with appropriate values.
This script applies text filter to field1 in the data set. Records that begin with the criteria value pass the filter criteria.
This script applies text filter to field1 in the data set. Records that contain the criteria value pass the filter criteria.
If you are using VBA to filter records in a worksheet, it is essential to identify if the sheet already has a filter applied. Copy this script into your workbook and run the script. This will identify if the active sheet has a filter on it.
This script will remove filters applied on a sheet and will display all records on it.
