Filter – the little funnel shaped icon in Excel, may be one of the most frequently used functions in Excel. Tell Excel one or several conditions and let Excel return those rows that meet your criteria. It works perfectly well in one single column.
However, sometimes you may want to filter one column according to the corresponding value in another column – in such a case, Filter may not be the most convenient way.
Here is an example. One patron recently came to my Office Hours inquiring about filtering in her Excel worksheet. She has two columns here, like this:
She would like to get all values in column B if the number in B is larger than the corresponding number in A.
While Filter might seem like the best option for this problem, it cannot really help you to compare the value in the same row but in different columns.
Thus, here I introduced her an useful function – IF function.
The syntax is like this:
IF(logical_test, [value_if_true], [value_if_false])
According to the definition from the Microsoft website, the IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE.
So, now let’s try this out.
In cell C1, we type:
For this function, I basically tell Excel that if the value in cell A1 is smaller than the value in cell B1, then it will say “YES”, otherwise, it will say “NO”.
Then I can use AutoFill to fill the rest of the rows – thus you can get a column with “YES’s” and “NO’s.”
Now you can easily use Filter to filter out those rows with “YES’s.” Done!
If you are not familiar with AutoFill and how to input a function, you can refer to this handout here!
As a second way, you can also use Conditional Formatting to solve this problem.
Under the [HOME] tab, you can find the Conditional Formatting button in the [Styles] group.
- Choose the range B1:B7 first, then click on Conditional Formatting
- Choose [Highlight Cells Rules] – [Greater Than]
- In the dialog box, put “=A1” in the empty box, then click OK.
- Now you will find that all the rows that meet our criteria will be highlighted with the selected style.
Conditional Formatting can use colors and styles to help you “filter” out the records you need. I found this visualization method really easy and helpful!
As always, if you have questions about this or other aspects of Excel, please stop by the Weigle Information Commons during my Excel office hours, and I will help to troubleshoot your problem!