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.
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).
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.
Copying the formula down, we get the following 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.
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.
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.
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.
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.
Filtered results will appear.
To re-run your filter, say with different criteria, you will need to repeat steps, 1 to 3.