Excel provides a wealth of filters that are readily available under the current filter option, however sometimes in our need to cut and modify data we require a more precise method of filtering through the rows.
If in the below screenshot I wanted to grab all the accounts that equal “546” and display only their Sales data, I could place a filter on the AccountID. However I am still left scrolling through a list of values to find “546” which is not ideal if the list has thousands of unique values.
To avoid this we can create a new column which displays “t” if the value in column A is equal to “546” and “f” if it doesn’t.
The basic conditional format is =IF(Condition, [value if true], [value if false])
This formula simply states, if cell A2 is equal to “546” display “t” otherwise display “f“.
The condition must equate to a true or false value, if your condition returns a string of characters or anything other than a true/false value then your formula will return an error and an #NA will be displayed in the cell.
Double click on the right-hand corner of the first cell to populate the column with the formula.
Take off the Filter on the first row (if it is currently on) and reapply it (under the Data tab) so that there is a drop down box on the new column. You can now filter the values using the equality formula set against the rows.
Select “t“ using the newly created drop down box and your correctly filtered data will remain.
By default your values will be of type general, however if you have pasted your data from an unknown source you may have different data types in your rows and columns. When checking for equality between your criteria and the column data the data types should be the same. Otherwise your conditional may not evaluate to true.
You can change the data type of your cells by highlighting them, right-clicking and selecting “Format Cells”, if both your columns are set to the same type here, then your formula should work.
The formula can be easily modified to filter using more complicated requests.
If you wanted accounts which begin with 546, the formula would look like this:
If you were after only customers that contained the word “Anonymous” the formula could be modified to look like this:
The criteria Search will return either an index indicating where the word was found or a value that cannot be displayed. The isnumber() criteria transforms this into a true or false statement that can be evaluated. So if search finds the word “Anonymous” in cell B2 it will return an index, that is a number resulting in a “t” being displayed.
The more text modification formulas you know the more powerful the filtering can become.