Let’s say you have some data, and you want to filter out any items that don’t meet a minimum spec. But that minimum spec could be in any of multiple columns. For example let’s say we have a list of computer hardware specs. We want to filter ANY computer that meets any of the following specs:
- CPU < 1.5 GHz
- RAM < 2 GB
- DISK < 40 GB
The problem with using a typical Excel filter, if I filter to show only CPU < 1.5 GHz, I could potentially miss out some machine that didn’t meet the RAM or DISK requirement.
(Note: The functionality demonstrated here is available in earlier versions of Excel, but steps may slightly or drastically differ)
Method #1 – Use A Formula Column
In column E I create an OR formula to meet my requirement. In this case the formula as =OR(B2<1.5,C2<2,D2<40)
I then simply drag the bottom right corner of the black square surrounding my formula down to row 11. Because we have not made an explicit cell reference i.e. $B$2, we can just drag this formula and the rows will automatically update.
We can now apply a filter to the Needs Upgrade column as required.
Method #2 – Conditional Formatting Rule
In this case I want to highlight every item that doesn’t meet the requirement with red background, and everyone that does in green. Using the built in rules in may not seem to work straight away to highlight an entire row. In this case the key thing is we must ensure to reference each cell in this way ‘$B2’
Please ensure you have only a single cell selected, and this cell is the top-left cell of your data, otherwise your formula will get messed up. In this case I have A2 selected. (i.e. PC-01)
In the Home Tab we will select Conditional Formatting –> Manage Rules
In Conditional Formatting Rules Manager click New Rule
Use the option “Use a formula to determine which cells to format” with the formula =OR($B2<1.5,$C2<2,$D2<40)
In “New Format Rule” I’ll click Format button. In the Fill tab I will decided a light red colour
For the Font colour I chose dark red, then once I’ve done selecting formatting I’ll click OK
My last step is to select the area this rule will apply to. You can either type this manually into the “Applies To” or click the icon to the right of Applies To to allow selecting the area you’ll apply your rule. When you’re done click OK
OK so now I have anything that doesn’t meet any ONE of my requirement in RED, with the whole row being highlighted in red.
To add my “good” filter I repeat the process for the bad filter, except this time I surround the formula with a NOT to give the opposite result. The formula used here is =NOT(OR($B2<1.5,$C2<2,$D2<40))
Now if we only want to show the good or bad items we can do a colour filter on a single column. The filter is enabled in Data tab and clicking Filter
Now we can choose Sort by Color or Filter by Color
OK now I have all the machines that I need to upgrade.
Finally what if my values are TEXT and NOT numbers?
For example what if CPU speed was a text field like 1 GHz, in this case the < and > comparisons won’t work as expected. So you can use formula to remove the text. This formula below can be used whenever the number you want has no space before it, and a space after it, followed by the text.