How To – Filtering Multiple Column Data & Row Highlighting Based on Single Cell in Excel 2010

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)

image

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)

image

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.

image

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

image

In Conditional Formatting Rules Manager click New Rule

image

Use the option “Use a formula to determine which cells to format” with the formula =OR($B2<1.5,$C2<2,$D2<40)

image

In “New Format Rule” I’ll click Format button. In the Fill tab I will decided a light red colour

image

For the Font colour I chose dark red, then once I’ve done selecting formatting I’ll click OK

image

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

image

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.

image

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))

image

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

image

Now we can choose Sort by Color or Filter by Color

image

OK now I have all the machines that I need to upgrade.

image

 

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.

=VALUE(MID(F2,1,FIND(” “,F2)-1))

image

About chentiangemalc

specializes in end-user computing technologies. disclaimer 1) use at your own risk. test any solution in your environment. if you do not understand the impact/consequences of what you're doing please stop, and ask advice from somebody who does. 2) views are my own at the time of posting and do not necessarily represent my current view or the view of my employer and family members/relatives. 3) over the years Microsoft/Citrix/VMWare have given me a few free shirts, pens, paper notebooks/etc. despite these gifts i will try to remain unbiased.
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s