Wednesday, October 25, 2017

microsoft excel - Color Cell Based On Text Value



An Excel column contains a text value representing the category of that row.



Is there a way to format all cells having a distinct value a unique color without manually creating a conditional format for each value?



Example: if I had the categories bedroom, bedroom, bathroom, kitchen, living room, I would want all cells containing bedroom to be a particular color, bathroom a different color, etc.


Answer





  1. Copy the column you want to format to an empty worksheet.

  2. Select the column, and then choose "Remove Duplicates" from the "Data Tools" panel on the "Data" tab of the ribbon.

  3. To the right of your unique list of values or strings, make a unique list of numbers. For instance, if you have 6 categories to color, the second column could just be 1-6. This is your lookup table.

  4. In a new column, use VLOOKUP to map the text string to the new color.

  5. Apply conditional formatting based on the new numeric column.


No comments:

Post a Comment

hard drive - Leaving bad sectors in unformatted partition?

Laptop was acting really weird, and copy and seek times were really slow, so I decided to scan the hard drive surface. I have a couple hundr...