• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Factor 10 Conditional Formatting Painting by Numbers

You are here: Home / Excel / Excel Factor 10 Conditional Formatting Painting by Numbers
Excel Factor 10 Conditional Formatting Painting by Numbers
August 15, 2012 by Mynda Treacy
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:

Cell Fill with Conditonal Formatting

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

  1. Select the cells you want the Conditional Formatting to be applied to.
  2.  

  3. On the home tab of the ribbon select Conditional Formatting > Highlight Cells Rules > Text that Contains…
  4.  
    Cell Fill with Conditonal Formatting

  5. Set up your first rule. We’ll do 1 = blue first.
  6.  
    Cell Fill with Conditonal Formatting

    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.

  7. Rinse & repeat for other number/colour combinations.

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:
  •  
    Cell Fill with Conditonal Formatting

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 10 Conditional Formatting Painting by Numbers

More Conditional Formatting Posts

conditional formatting tables and matrices in power bi

Conditional Formatting in Power BI Tables and Matrices

How to apply conditional formatting to tables and matrices in Power BI. Use color, icons, data bars and URL's.
project management dashboard

Excel Project Management Dashboard

Excel project management dashboard video tutorial covering various techniques including conditional formatting, PivotTables, Slicers, charts and more.
Highlight Selected Cell In Excel and Preserve Cell Format

Highlight Selected Cells in Excel and Preserve Cell Formatting

Use shapes to highlight the selected, active cells in Excel & preserve cell formatting. So you won't lose any conditional formatting, borders, colors etc.
Conditional Formatting PivotTables

Conditional Formatting PivotTables

Conditional Formatting PivotTables values areas will automatically expand/contract as you add new data or make changes to the filters, rows or columns.
excel conditional formatting gantt charts

Excel Conditional Formatting Gantt Chart

You can build a Conditional Formatting Excel Gantt Chart easily with just a few WORKDAY.INTL formulas and relative references.
Excel Conditional Formatting with Formulas

Excel Conditional Formatting with Formulas

Excel conditional formatting with formulas can be super confusing when you don't follow these three simple rules that ensure they work everytime.
Excel Factor 8 Highlight Cells Containing Formulas

Excel Factor 8 Highlight Cells Containing Formulas

Automatically highlight cells containing formulas in Excel to reduce the chance of them being deleted or over-written
How to Use Excel Conditional Formatting

How to Use Excel Conditional Formatting

Excel Conditional Formatting examples and workbook.

More Excel_Factor Posts

Excel Factor Voting Roundup

Excel Factor Voting Roundup

Excel Factor 21 Hyperlink Triptych

Excel Factor 21 Hyperlink Triptych

Tips and tricks with hyperlinks to make them dynamically update as the selection in the sheet changes. Sample workbook available.
Excel Factor 20 Custom Number Format Disguise

Excel Factor 20 Custom Number Format Disguise

Excel Factor 19 Dynamic Dependent Data Validation

Excel Factor 19 Dynamic Dependent Data Validation

Excel Factor 18 Dynamic Hyperlinks and the Magic Hash

Excel Factor 18 Dynamic Hyperlinks and the Magic Hash

The Excel HYPERLINK function creates links to places inside or outside of your Excel file. I'll show you a shortcut for creating them that few people know.
Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 16 Dynamic Lookup

Excel Factor 16 Dynamic Lookup

Excel Factor 15 The Lazy Lookup

Excel Factor 15 The Lazy Lookup

Excel Factor 14 Interleave Data from Two Columns into One

Excel Factor 14 Interleave Data from Two Columns into One

Excel Factor 13 Handy Tips & Tricks

Excel Factor 13 Handy Tips & Tricks

More Excel Posts

excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.

10 Common Excel Mistakes to Avoid

10 common Excel mistakes to avoid, including merge cells, external links, formatting entire rows/columns and more.
new Excel features

Cool New Features in Excel for Microsoft 365

Cool New Features in Excel for Microsoft 365 including the navigation pane, smooth scroling, unhide multiple sheets and more.
dynamic dependent data validation

Dynamic Dependent Data Validation

Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.
QAT

Excel Quick Access Toolbar

The Excel Quick Access Toolbar is not only a handy for your mouse, but it also enables some super easy keyboard shortcuts.

Share and Collaborate in Excel

Share and Collaborate in Excel just like Google Sheets! Show changes, custom views, threaded comments with @ mentions and more.
Workbook Protection

Excel Workbook Protection

Excel Workbook protection can prevent your users from breaking your reports while still allowing interaction with Slicers and refreshing.




Category: ExcelTag: conditional formatting, Excel_Factor
Previous Post:Excel Factor 9 Count Unique Items in a ListExcel Factor 9 Count Unique Items in a List
Next Post:Excel Factor 11 Forecasting TricksExcel Factor 11 Forecasting Tricks

Reader Interactions

Comments

  1. ron

    June 3, 2013 at 2:55 am

    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?

    Reply
    • Mynda Treacy

      June 3, 2013 at 7:33 am

      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:

      =IF(task is on target, "ü", "û")

      I can then also apply the conditional formatting to colour the cells red/green etc.

      I hope that helps.

      Kind regards,

      Mynda.

      Reply
  2. Mike

    May 1, 2013 at 1:00 am

    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/

    Reply
    • Mynda Treacy

      May 1, 2013 at 10:06 am

      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.

      Reply
  3. Michael

    October 16, 2012 at 9:35 pm

    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!

    Reply
    • Mynda Treacy

      October 16, 2012 at 11:09 pm

      Hi Michael,

      Cheers. Thanks for sharing 🙂

      Kind regards,

      Mynda.

      Reply
  4. Birbal

    September 20, 2012 at 10:04 pm

    Thanx

    Reply
    • Mynda Treacy

      September 21, 2012 at 8:18 am

      You’re welcome, Birbal 🙂

      Reply
  5. kayakbob

    August 23, 2012 at 12:28 am

    I like this usage of Conditional formatting!

    Reply
    • Mynda Treacy

      August 23, 2012 at 8:15 pm

      Cheers, Kayakbob!

      Reply
  6. raju

    August 20, 2012 at 2:06 pm

    great

    Reply
    • Mynda Treacy

      August 20, 2012 at 6:49 pm

      Cheers, Raju.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.