Color drop down in Excel cell (with no text)? e.g. bgcolor = Red-Green-Amber-unknown

Question

I have an Excel sheet that I'm using to keep track of the status of certain things.

I want to have a column which consists of cells containing a repeated drop down that allows you to select (as background)

red amber green unknown

I don't want any text in this cell, I just want a coloured block.

Is this possible?

I've tried playing around with data-validation-list (based on range containing all of said colours but to no avail)

Answer 1

In Excel 2003 it is not possible to select a color with an autofilter. However in Excel 2007 this is possible.

Answer 2

Strictly speaking, no, as Rogier has said.
But I can suggest an almost work-around, whether or not this is suitable for your suitation I don't know.

---

First have each cell contain a single letter (or short name, etc) representing one of the colours you need (if the cell contains some other value already, see the section at the end after you've read this one).

You can do this manually, or you could enforce it using data validation settings.

To enforce, first select the relevant column (but not the header), then on the menus visit Data -> Validation. Set the type to list and enter (for example) Red,Amber,Green as the source. Keep Ignore blank ticked so "unknown" is represented by an empty cell.

Then, using Conditional Formatting have one entry for each colour (so condition one is Cell Value is Equals to ="Red", for example). Set the format of each so that the background colour and the colour of the text are both the colour you need. The text is still there, you just can't see it (well ... except in the formula bar).

Conditional formatting in Excel 2003 limits you to only three options, but since you only need to consider three colours this is an option. Just leave "Unknown" cells empty so they default to plain white.

---

If the reason you don't want text is because something else is in the cell already, you could setup the data validation column in a different column. The condtional formatting would then need to be Formula is =IF(D1="Red",1,0) - manually enter this in just the top cell and then fill down the formatting for the whole column.

If necessary, you might want to then set column D to have white text and/or ensure it is outside the printable area - depending on your requirements.