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 🙂