Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or
0
0
0
s2smodern

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)


Click Kutools Plus>> Super Filter. See screenshots:

shot super filter 01 -2

Filer data based on cell value with one criterion

Supposing you have 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 screenshot.

1. Clickshot-button1 button to select the range that you want to filter, and click shot superfilter 2to fixed the selection.
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 shot-button2button to add new condition group.

3. Click Filter button to filter the data, and you will get the results as follows:


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.) Click shot-button1 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.
-1

3. 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.


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 shot-button1 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.

3. 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.


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, see screenshot:

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, see screenshot:

3. Then click Filter button, and only the uppercase text strings have been filtered out.


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 shot superfilter 15> 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:

-1
shot-super-filter12

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.


Notes:

1. You can save your created scenario and manage them.

: Click on this button, you can save your current filter settings.

: With this button, you can save your filter settings as a scenario. And in the popped out dialog, enter the name of your scenario. When you want to use these settings next time, you can go to Manage filter settings scenarios dialog to open it.

shot-super-filter13

: Click on this button, a Manage filter settings scenarios dialog will display, you can manage (rename, delete, open) the scenarios that you are saved. See screenshot:

shot-super-filter14

2. You can add filter group by clicking shot-button2or shot superfilter 14 button, all of the conditions and condition groups can be closed or disabled by clicking Close button or unchecking the checkboxes.

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


Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2019, 2016, 2013, 2010, 2007 and Office 365.
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016, 2019 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
People in conversation:
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    inetguru · 1 years ago
    I appreciated the capabilities of Superfilter, a very cool tool!

    By my sensations it lacks
    the following:

    1) The ability to copy the current filter settings from the Excel table (in one group with the AND condition).

    2) Selection options from the data available after filtering in the selected column (as implemented in Excel).
    Those, I need the ability to see the data that I can filter, and not search for them in the data table separately, and then configure the Superfilter.

    3) Ability to pass as a filter criterion a named range or list of values ​​from the selected area in the table with:
    a) the setting of relations between them before import (as in the advanced Excel filter)
    b) condition like "in range/list", "not in range/list".
    4) Possibilities to drag a group or a separate line into another group (drag'n'drop).

    5) Ability to filter by the color of the text (including such conditions as "contains text with color", "does not contain text with color") or background. Suggest a choice from the list of colors in the selected column.

    6) The ability to filter text with the terms "does not end with", "does not start with".

    7) Additional conditions for the relations of the elements in the group: XOR, NOT.

    8) Ability to filter by type of error in the cell, like # N / A, #REF !, #NULL !, # DIV / 0 !, #VALUE !, #NAME ?, ##### ERROR (cell formating).

    Good luck to you and keep up the good development!
  • To post as a guest, your comment is unpublished.
    malli · 4 years ago
    Hi,
    I Am working with kutool 7.8.0 in this superfilter saved Scenario is not opening and its Showing a Message " Before Opening a Filter setting scenario, you must open a workbook first".Can u give me the Solution for this...?


    [img]C:\Users\mallikharjunp\Desktop\ku_Problem.jpg[/img]
    • To post as a guest, your comment is unpublished.
      admin_jay · 4 years ago
      [quote name="malli"]Hi,
      I Am working with kutool 7.8.0 in this superfilter saved Scenario is not opening and its Showing a Message " Before Opening a Filter setting scenario, you must open a workbook first".Can u give me the Solution for this...?[/quote]
      Please open a workbook in your Excel. :-) The scenario cannot work without a workbook.
  • To post as a guest, your comment is unpublished.
    admin_jay · 4 years ago
    [quote name="Siva"]Hi,
    I Am working with Super Filter in this Version.In every new Filter setting Scenario, After some time the saved Scenario is not opening and its Showing a Message " [b]Before Opening a Filter setting scenario, you must open a workbook first[/b]". after this again i am creating new scenario and working.Can u give me the Solution for this...?[/quote]
    Hello, please make sure you have installed the latest version. If the problem persists, please contact me at jaychivo#extendoffice.com with detailed information of the issue. Please replace # with @.
  • To post as a guest, your comment is unpublished.
    Siva · 4 years ago
    Hi,
    I Am working with Super Filter in this Version.In every new Filter setting Scenario, After some time the saved Scenario is not opening and its Showing a Message " [b]Before Opening a Filter setting scenario, you must open a workbook first[/b]". after this again i am creating new scenario and working.Can u give me the Solution for this...?
  • To post as a guest, your comment is unpublished.
    Naested · 4 years ago
    Is there another way to edit or pre-populate the criteria for the super filter?

    Is there a limit to the number of criteria you can add for a single scenario?
    • To post as a guest, your comment is unpublished.
      admin_jay · 4 years ago
      [quote name="Naested"]Is there another way to edit or pre-populate the criteria for the super filter?

      Is there a limit to the number of criteria you can add for a single scenario?[/quote]
      There is no other way to edit the criteria.

      The number of criteria for a single scenario is no limitation.