Skip to content
Home » Perform a Case Sensitive Filter in Excel

Perform a Case Sensitive Filter in Excel

    Excel Filters Are Not Case Sensitive

    You may have discovered for yourself that Excel’s filters are not case sensitive. Take the following example where our database contains product codes, some of which contain lowercase letters and others uppercase. Download the example file here.

    Database with filter
    WE want to be able to filter for Product YU-PUT4264 but not YU-put4264.  In the filter’s drop down list (shown below) we can only see the lower case version of the product code but when we select it and run the filter the results show both versions (upper and lower case).

    not case sensitive 1not case sensitive

    Running a Case Sensitive Filter Using Normal Filters

    To solve this problem we can add an additional column to our database that contains a formula that runs a test on case.  To run a test on case we can use the EXACT function which allows us to compare two text values.  EXACT is case sensitive and will return TRUE only if the two values are identical including case.

    The first cell of the new column contains the criteria for our filter.  In the next cell down we have entered the following formula =EXACT(C$1,A2).  The EXACT function will compare the values in C1 and A2, return TRUE if there is a match and FALSE if there isn’t.  Notice we have fixed the cell reference C$1 so that as we copy the formula down our new column the cell reference won’t change.

    EXACT Function

    Copying the formula down, we get the following results.

    True and False Results

    Next step is to filter on our new column for all the TRUE results.  You may need to reapply your filters so that the filter button appears on your new column.

    Filter for TRUE results

    We now get the case sensitive filtered results we were after.  To change the criteria for our filter requires us only to enter a new product code at the top of our new column and then to re-run the filter.

    TRUE Results

    Running a Case Sensitive Filter Using Advanced Filter

    An alternative to the method above is to use an advanced filter.  The advantage with this second method is that it does not require an additional column in your database.

    See below to see how you could set up the criteria for your advanced filter.  The criteria needs to be set up outside your database (normally above the data). Cell A2 contains the criteria and cell B2 contains the EXACT function formula (notice again the fixed reference on A$2).  There must be a cell above the EXACT formula that is either blank or containing a value that is not equal to any of the column headings in your database.

    Advanced filter criteria

    The formula in B2 will return a result for the first record in your database either TRUE or FALSE but when we run the advanced filter it will repeat that test for each row in the database and only return records where the result is TRUE.  Let’s try it.

    To run the advanced filter, follow these steps.

    1) Click in any single cell in your database

    2) Click on the Data tab on your Ribbon and then click the Advanced button in the Sort & Filter group. The Advanced Filter dialog box will open.

    Advanced filter button

    3) The List Range is your database – this should automatically be selected, if not select it including column headings. The Criteria Range needs to include the cell with your EXACT formula in it and the cell above it. If you want to filter the database in place click on OK now. If you want to copy the results of the filter to a separate location, select the Copy to another location radio button and in the Copy to box select a single cell as the location for your filtered results. Then click OK.

    Advanced filter dialog box

    Filtered results will appear.

    Advanced filter result

    To re-run your filter, say with different criteria, you will need to repeat steps, 1 to 3.

    Notes

    1) When you use a formula as criteria it must return TRUE or FALSE.

    2) The cell above your formula must be blank or contain a label that does not match any column heading labels in your database.

    3) The cell reference in your formula that refers to the first row of data must be a relative cell reference (no dollars). All other references in the formula must be fixed (using dollars).

    4) You can specify additional criteria in an advanced filter by adding columns to your criteria area.  For example, if we wanted to show results for product YU-PUT4264 where the Qty was greater than 5000 we would set out our criteria like this.

    Advanced filter additional criteria

    The criteria label must match the column label in the database.  In this example we would need to specify our criteria range as B1 to C2.