This Excel Factor entry was sent in by Rob Anderson of Oxfordshire, UK.
Project planning and other tracking worksheets can be enhanced with Conditional Formatting.
Rob uses a clever trick to quickly colour code cells using conditional formatting like columns F:M below:
Remember painting by numbers? It’s a bit like that. Where 1 = blue, 2 = green, 3 = red and so on.
Set up Painting by Numbers with Conditional Formatting
- Select the cells you want the Conditional Formatting to be applied to.
- On the home tab of the ribbon select Conditional Formatting > Highlight Cells Rules > Text that Contains…
- Set up your first rule. We’ll do 1 = blue first.
- Rinse & repeat for other number/colour combinations.
Here’s the trick, make sure you set the colour of the fill and the font to the same shade of blue. That way when you enter a 1 in the cell you won’t see the actual number as it will be the same colour as the fill.
Painting by Numbers Variations
- Instead of typing a number in the cell, you can use other characters like */=+ etc, or combinations of numbers and symbols. Simply set up your Conditional Formatting accordingly.
- Use formulas. E.g. If 2 = green then the formula =1+1 will format a cell green. Obviously this is a silly example, and you could do something much more sophisticated with and IF statement, or other formula that reads the results of a cell and returns the relevant value.
- Use a combination of cell fill and symbols to indicate different statuses in the one cell:
Thanks for sharing this tip, Rob.
Vote for Rob
If you’d like to vote for Rob's tip (in X-factor voting style) use the buttons below to Like this on Facebook, Tweet about it on Twitter, +1 it on Google, Share it on LinkedIn, or leave a comment….or all of the above 🙂
Excel Factor 9 Count Unique Items in a List
ron
I don’t follow the last point:
Use a combination of cell fill and symbols to indicate different statuses in the one cell:
I understand the colors, but how are you getting the symbols in the cells. Are they being inserted by the conditional formatting or they already there?
Mynda Treacy
Hi Ron,
The symbols are created using the wingdings 3 font. You set the font for the cells to Wingdings 3 and then use the characters that represents a triangle, check mark or cross in Wingings in your formulas.
For example the û represents the check mark and ü represents the tick in Wingdings 3. I could therefore write a formula like this:
I can then also apply the conditional formatting to colour the cells red/green etc.
I hope that helps.
Kind regards,
Mynda.
Mike
Great article and excellent use of conditional formatting. I’ve put together a complete project planning template using excel and this same form of conditional formatting – freely available for download from my web site at http://www.mlynn.org/2012/09/excel-project-planning-spreadsheet-updated-version-3/
Mynda Treacy
Hi Mike,
Thanks for sharing your template. Although I see you have a few comments on your post that you haven’t answered.
Kind regards,
Mynda.
Michael
Nice article! Here’s a similar article which expands on the use of Excel for project management and includes a downloadable template that you can customize and use to help manage your projects
http://www.mlynn.org/2012/09/excel-project-planning-spreadsheet-updated-version-3/
Enjoy!
Mynda Treacy
Hi Michael,
Cheers. Thanks for sharing 🙂
Kind regards,
Mynda.
Birbal
Thanx
Mynda Treacy
You’re welcome, Birbal 🙂
kayakbob
I like this usage of Conditional formatting!
Mynda Treacy
Cheers, Kayakbob!
raju
great
Mynda Treacy
Cheers, Raju.