Quickly filter data based on cell value with multiple criteria in Excel

With the Filter function in Excel, it is easy for you to filter data by one criterion. And when you want to filter date based on two or more multiple criteria, you can use the Advanced Filter, but this method is not easy to use. With Kutools for Excel’s Super Filter utility, you can filter data based on cell value with multiple criteria by a few clicks.

Filer data based on cell value with one criterion

Filter data based on cell value with multiple criteria

Filter data based on year / month / day / week / quarter

Filter data based on text length / number of characters

Filter cell text with case sensitive (only uppercase / lowercase or contains uppercase / lowercase)

Filter cell values with all errors or one specific error

Save filter criteria as scenario for using next time

Click Kutools Plus >> Super Filter. See screenshots:

Filer data based on cell value with one criterion

Supposing you have a worksheet as shown in the following screenshot, you can filter data with Super Filter utility as follows:

In this demo, we will filer data based on Product = KTE.

1. Apply the utility (Click Kutools Plus > Super Filter).

2. Apply the settings in the Super Filter as shown in the screenshots.

	1. Check to fix the selection, then click button to select the range that you want to filter.
	2. In the general Relationship drop down list to choose OR or AND relationship as you need.
	3. Choose the group relationship by clicking Relationship in Group drop down list.
	4. Click the horizontal line beside the relationship AND, and it will appear some condition boxes, click the condition boxes and select the criterion one after one as you need.
	5. Then enter the condition that you want to use, in this case, KTE. And click the Ok button to add the condition

3. Then click Filter button. You will see the filter results in the worksheet as follows:

Filter data based on cell value with multiple criteria

If you want to filter data based on the following criteria:

• (1.) Product = KTE and Country = US
• (2.) Product = KTO and NO. ≥ 10

And between these two criteria the relationship is or.

You can quickly filter the data that you want with the following steps:

1. Apply this utility by clicking Kutools Plus > Super Filter.

2. Apply the settings in the Super Filter dialog as shown in the screenshot:

• (1.) The relationship between the two criteria is or, so you need to choose OR from the general Relationship drop down list.
• (2.) Create the criteria as you need, and you can click Add Filter or Adding button to add new condition group.
• (3) Then click Filter to filter the data

  Demo

Filter data based on year / month / day / week / quarter

In Excel, if you want to filter the rows based on year, month, day, week or quarter of the date, the Super Filter also can help you to deal with this task. In this example, I want to filter the data in quarter 3, you can do as follows:

1. Apply this utility by clicking Kutools Plus > Super Filter.

2. Then in the appeared Super Filter dialog, set the following options:

• (1.) Check to fix the selection, then click button to select the selection that you want to filter the specific rows.
• (2.) In the general Relationship drop down list to choose OR or AND relationship as you need.
• (3.) Choose the group relationship by clicking Relationship in Group drop down list.
• (4.) Click the horizontal line beside the relationship AND, and it will show some condition boxes, click the condition boxes and specify the criterion one after one as you need.
• After finishing the settings, click Filter button, and the rows which date in quarter 3 have been filtered out.

Note: You can filter the data by month, year, day, week as well as you like.

  Demo

Filter data based on text length / number of characters

If you want to filter rows by one of the columns text length, for example, to filter all the rows with text lengths greater than 15 characters. What should you do? The Super Filter utility can solve this task quickly and easily.

1. Apply this utility by clicking Kutools Plus > Super Filter.

2. In the Super Filter dialog, set the following options:

• (1.) Click button to select the data range that you want to filter the specific rows.
• (2.) In the general Relationship drop down list to choose OR or AND relationship as you need.
• (3.) Choose the group relationship by clicking Relationship in Group drop down list.
• (4.) Click the horizontal line beside the relationship AND, and it will show some condition boxes, click the condition boxes and specify the criterion one after one as you need.
• Then click Filter button, the rows which text length is greater than 15 characters have been filtered out.

Note: You can filter the rows which text length is a specific number of characters or less than a number of characters as you want.

  Demo

Filter cell text with case sensitive (only uppercase / lowercase or contains uppercase / lowercase)

To filter the rows which text string is only uppercase or lowercase, you can apply the Super Filter to finish it conveniently. Please do as follows:

1. Select the data range that you want to filter, and click Kutools Plus > Super Filter to go to the Super Filter dialog, in the dialog box, click the horizontal line beside the relationship AND, then choose the column name that you want to filter by from the first drop down, and from the second drop down list, select Text format.

2. Then choose the criteria from the third drop down, at last specify one choice you need and click the OK button, you can filter only uppercase / lowercase text, or the text containing uppercase / lowercase, then click Filter. see screenshot:

  Demo

Filter cell values with all errors or one specific error

 Select the data that you want to filter, and click Kutools Plus > Super Filter to go to the Super Filter dialog, in the dialog box, click the horizontal line beside the relationship AND, then choose the column name that you want to filter by from the first drop down, and from the second drop down list, select Error. Then choose the criteria from the third drop down, at last specify one choice you need and click the Ok button, you can filter all errors or one specific error such as #N/A,#VALUE!, then click Filter. see screenshot:

  Demo

Tip:

You may get puzzled when you apply this utility first time, so we have created a built-in sample for you to reference and use.

1. After activating the Super Filter utility, click Create new filter settings scenario button  > Sample scenario to open the sample workbook and default criteria.

2. And the criteria has been set up, you can imitate them when you apply this utility, click Filter button to filter the data, and you will get the following result:

3. All Super Filter features are collected in the drop-down menu as below screenshot shown. You can use them from the drop-down menu as you need.

Save filter criteria as scenario for using next time

With Super Filter utility, you can save the current filter criteria as a scenario which can be reused next time.

: Click this button to create a new filter settings scenario. If there has been existed filter settings which are not saved, a dialog pops out for remind you.

: Click this button to save your current filter settings, if the current filter settings never have been saved before, a dialog pops out for you to name this new filter scenario and specify the applying scale.

: Click this button to save current filter settings to a new filter scenario.

: Click this button to close current filter scenario.

Click this button to display the Open saved filter settings scenario dialog, then select a scenario in the right pane of the dialog to open it.

: Click on this button, a Manage filter settings scenarios dialog will display, you can manage (add, rename, delete, open, import or export) the scenarios folders in left pane, also in right pane you can manage  (add, rename, delete, open, move, import or export) the scenarios of each scenario folder. See screenshot:

Notes:

1. You can add filter group by clicking Adding button or  button, all of the conditions and condition groups can be closed or disabled by clicking Close button or unchecking the checkboxes.

2. Click the Settings button, you can set the displaying way of the filter criteria as you need, checking Enabel automatic folding for criteria checkbox, then choose one folding type from the drop-down list.

and you also can choose the scenario saving type as you need in the When closing the workbook, a scenario that not saved section.

3. Click the Apply to button to specify which scale the current filter settings apply to.

4. Click Clear button to cancel the filter and recover the data.

5. If you think the Super Filter feature is useful, you can click  button to share it to your friends.