Microsoft excel provide filter function for you to find out required rows by customized condition in a very long excel worksheet. The filter feature include basic filter which provide simple condition filtering and advanced filter which provide complex condition filtering. This article will show you both 2 filter feature with example.
1. Excel Basic Filter Example.
There is an example excel file which contains so many rows and each row contains a lot of columns, i want to focus on 2 columns, one is Country ( column B ) the other is Units Sold ( column E ). You can downlod the example file by click this link.
1.1 Filter Text Column (Country).
The Country ( column B ) column is a text column, it contains some country text value like Canada, France, Germany etc. I want to filter out the rows which Country column value is Canada or Germany. So i click Data —> Filter menu item in ribbon. Then you can find there add a dropdown arrow at each column right side of the first row.
When you click the Country column dropdown arrow to expand it, you can find some menu item such as Sort A to Z, Sort Z to A etc. There is also a checkbox list at the bottom of the dropdown list, it summarize all the country values that exist in the Country column such as Canada, France, Germany etc.
If you click Sort A to Z or Sort Z to A list item, it will group the excel rows by the Country column value. If you only want to display the rows which Country column value is Canada or Germany, you can just check the checkbox before Canada and Germany checkbox and uncheck all other checkboxs. Then you will get all the excel rows which Country column value is Canada or Germany.
If you think the filter condition is not enough for your scenario, you can click the Text Filters list item, and then add more complext text filter condition through the popup menu list such as Begins With, Ends With, Contains etc.
When you click one filter condition item for example Contains, it will popup a Custom AutoFilter dialog, you can input your filter condition in it like below.
1.2 Filter Number Column ( Units Sold ).
Now we will add a simple filter to the Units Sold ( column E ) column. Because this column contains number value, so there are some difference between it and the Country column which is a text column.
Click the dropdown arrow at right side of Units Sold column, you will see below list items. You can see list item Sort Smallest to Largest, Sort Largest to Smallest etc.
When you click the Number Filters list item, it will popup number value filter condition menu list. Click one item such as Greater Than, and in the popup Custom AutoFilter dialog input 3000 after is greater than condition input box like below picture.
When you click OK button in above dialog, you can get all the excel rows which Country column value is Canada or Germany and Units Sold column value is greater than 3000.
2. Excel Advanced Filter Example.
In section 1, we had filtered out excel rows which Country column value is Canada or Germany and Units Sold column value is greater than 3000. But how to filter out excel rows which match follow condition.
- Country column value is Canada and Units Sold column value is greater than 3000.
- Country column value is Germany and Units Sold column value is less than 500.
To filter out excel rows which match above condition, we can use excel Data —> Advanced filter feature follow below steps.
- Select the first excel worksheet row.
- Click Home —> Insert —> Insert Sheet Rows to insert 5 rows at the worksheet top area.
- Copy the original header row ( now the 6 row ) to worksheet first row.
- Add one filter condition in row 2, the condition is Country column value is Canada, and Units Sold column value is >3000.
- Add another filter condition in row 3, the condition is Country column value is Germany, and Units Sold column value is <500.
- Below is the picture after you implement above actions. I have create another worksheet Sheet2 in the example excel file for this example.
- After create advanced filter condition, we should click Data —> Sort & Filter —> Advanced menu icon to execute it. Then it will popup below dialog.
- Click the up arrow button at the end of List range input text box, and input the filter query executed list range Sheet2!$A$5:$P$705 ( sheet_name!start_cell:end_cell ).
- Click the up arrow button at the end of Criteria range input text box, and input the filter condition list range Sheet2!$A$1:$P$3 ( sheet_name!start_cell:end_cell ).
- Click OK button, now you can see the filter out excel rows listed in the excel worksheet.
- To clear the filter condition you should click Data —> Sort & Filter —> Clear item, then all the worksheet rows will be listed out.