Advanced Filter Excel Template
Quite often Excel is used for working with small databases and spreadsheets with up to several tens (or hundreds) of thousands of rows. Such tools as Pivot Tables, filters and Advanced Filters are used for easy and convenient data analysis.
Advanced Filter allows a data sampling function to be performed from Excel spreadsheets which is similar to SQL-query. Typically, there are two additional rows: the top row that contains a field name (a heading) and the bottom value based on which the data will be filtered. All values can be defined either as a target value or specified in the comparison form: "more" (>100), "less" (<200), or text values, that contain a part of the "*some text*".
For example, we are going to have a look at the spreadsheet with three thousands of rows (sheet "Data") and create the user-friendly form of data filtering (sheet "Filter").
The spreadsheet contains columns with the following data:
Data sampling will be organized according to the following data:
The list of available fields
First of all, we create the lists of permissible values for all fields, except "Product". Data, that contain the following words (*some word*), will be searched based on the "Product" field.
Data for the "Month" field is naturally defined – from 1 to 12. For the rest of fields, we will use the Advanced Filter function for filtering unique values. It can be done by calling out the dialog window in the Excel menu: Data->Sort &; Filter->Advanced:
- Select the "Country" column;
- Switch a radio-button to "Copy to another location";
- The "Criteria range" field should be empty;
- Select a cell, where data will be copied to "Copy to";
- And select "Unique records only".
As a result, we get a list of all values in the field "Country" which we will be sorted alphabetically for more convenience.
Apply the same procedure to the fields "Year" and "Category".
Parameters for data filtering
The picture below shows an example of how cells with headings should be specified for the criteria of data filtering:
We will use the value of ">0" for the criteria "Year" and "Month", if the concrete field value is not specified:
for the criteria "Country" and "Category" use "*":
for the "Product" field, a target value will be set between symbols "*":
Form used for data sampling
Let's create a new "Filter" list with request parameters and prepared data sampling results as it is shown below:
For the first four parameters we will limit the list of entered parameters by means of the "Data Validation" function. Moreover, it will allow you to use a drop-down list for the entered data:
- Define names for the following lists (sheet "Data"):
- Range K5:K7 – define a name: list_year
- Range L5:K16 – define a name: list_mth
- Range M5:K47 – define a name: list_country
- Range N5:K8 – define a name: list_category
- For each cell of request parameters we will define the most relevant list in the "Data Validation" dialog window.
Button for data filtering
Create a button "Filter" and write down a macro command for the data filtering according to the set criteria.
For example, the macro command will look like this:
Delete 9 rows given below:
' clear old data first Dim n As Long n = Cells(Rows.Count, "A").End(xlUp).Row If n > 9 Then Rows("10:" & CStr(n)).Delete Shift:=xlUp End If
Apply the filter with the following criteria:
With Sheets("Data") .Select ' apply filter .Range("A:I").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=.Range("Criteria"), Unique:=False End With
As a result of using the VBA-script, we will get a list of rows in the "Data" field that will fully satisfy our requirements. Now we will need to copy the data to the "Filter" list:
' select filtered rows Dim rngFilter As Range Set rngFilter = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 9) ' copy selection rngFilter.Select Selection.Copy End With ' paste new data Sheets("Filter").Select Sheets("Filter").Range("A10").Select ActiveSheet.Paste Application.CutCopyMode = False
Remove the filter and get the spreadsheet style back for more convenience:
' remove filter Sheets("Data").ShowAllData ' table style Sheets("Data").ListObjects("Table1").TableStyle = "TableStyleMedium2"
Attention! If there are no data given for some of the criteria values, the macro will automatically copy the whole spreadsheet. In order to avoid it, we should check the number of filtered rows. In this case, we will not copy the data if there are no rows available for data sampling.
' count number of filtered rows On Error Resume Next n = 0 n = rngFilter.SpecialCells(xlCellTypeVisible).Rows.Count On Error GoTo 0 If n = 0 Then Sheets("Filter").Select ' skip copying GoTo skip_copying End If
The full VBA script can be download and examined at the Advanced Filter Excel Template (220 Kb)