anything in Excel...

It's Excel-o-lozy...

Auto Filter can't filter more than one color.. Formula to get color of a cell.. under formulaFix by Excelolozy.

www.excelolozy.com - Auto Filter doesn't work to filter more than 1 color..

Suppose filter is to be applied on a column with cells having multiple colors as background say Red, Green and Yellow.

Now under Auto filter, either of these three colors is possible for filtering.
BUT... Two or more colors cant be filtered.

Here is a solution.

A Formula to get color of a cell...

=Color(A1)

it will show color of cell range "A1".

Magic of this formula is that it can show the color of same cell it is applied to. No Circular Reference Error is shown.

in above example, if the formula is entered in Cell "A1", it will still show the color code.

It shows following colors as text :
  • Red
  • Green
  • Blue
  • Yellow
For other colors it will show the code in number.


Now coming back to Auto Filter problem for multi color filter, the Super Trick is :
  • Insert a blank column next to source column where colors are marked
  • Apply this formula to get the color of respective cell
  • Now apply filter on the whole range and Select respective Multiple colors and apply filter.

This formula is available within tool named "formulaFix".

"formulaFix" is a "Formula Fixing Tool" that provides various formulas not existing in Excel by default.

To get "formulaFix" click here.




Anything in Excel, be an Excelolozian...
for best MS Excel Tips visit http://www.Excelolozy.com
gotta query??? drop a mail at mail@excelolozy.com