Extracting data from table by cell or text color

So, here's the thing. Color is not data. The use of coloring in cells to provide contextual information is a dangerous and oblique way to present meaning.

Conditional Formatting takes you further into the rabbit hole, but in this instance at least Excel tried to get you to think logically about what the colors mean. You must fully design your TRUE/FALSE test to get the color to appear.

It is at this point I always warn people that if you are starting to think of your CF colors as points of data in need of further extrapolation, it is now that you should start pulling that hidden logic (hidden in CF rules) and putting them into clearly defined additional COLUMNS so these tests are self-evident, no need for color explanation, the new columns will have a clearly defined column title.

And columns are FREE! So there is no need to hide logic away behind colors, then try to extract info from the colors.

Just don't do it. Turn the data into data and all the simple reporting power of Excel stays right at your fingertips with no heroics required.



A simple example, if green means <100, and yellow means >100<500 and red means >500, a simple formula in a column can provide that same meaning:

=IF(A2<100, "Good", IF(A2<500, "Warning", "Danger"))

You can filter tables on cell color and font color.
Then you can copy the visible rows.