How to Use a Check Box to “Toggle” Conditional Formatting On or Off in Excel

Sort by Color in Excel

Sort by Color in Excel

In my previous Excel Tutorial – “Conditional Formatting to Dynamically Format Dates” – I demonstrated how to  apply Conditional  Formatting based upon the number of days have passed since last contact with a customer. Now I will extend what you just learned.

Three Techniques in this Video:

  1. How to extend  Conditional Formatting Rules to additional cell ranges.
  2. How to use the “Sort by Colors” feature introduced in Excel 2007.
  3. How to add a Check Box Form Control that will “toggle” the Conditional Formatting On or Off.

Paste Special “Live Preview”

I demonstrate a great new feature introduced in Excel 2010, “Live Preview” for Paste Special. You will use Paste Special – Formats to extend the rules for Conditional Formatting to a new range of Cells

Sort by Color

Beginning with Excel 2007, you can now sort fields based upon the color of a font or a cell background. I show how this works.

The Check Box Form Control

The “key concept” to understand when using Excel’s Form Controls is the “Cell Link.” With a Check Box Control, the value in the Cell Link is TRUE when the box is “checked” while the value is FALSE when it is Unchecked. We can use this information to create a “Conditional Format based upon a Formula.”

Conditional Formatting Rules

When you base Conditional Formatting upon a Formula, the result of that formula must return TRUE in order apply this formatting. Setting the background cell color to “White” effectively “hides” the previous background cell colors.

Shop for The 50 Best Tips for Excel 2007

Shop for The 50 Best Tips for Excel 2007

Resources Offered for Sale

I encourage you to visit my secure online shopping website – – to preview the many training resources that I offer you. Check out my latest Excel Training Videos:

Watch this Video in High Definition

Click on this link to watch this video in High Definition on my YouTube Channel – DannyRocksExcels


Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn


  1. […] Follow this link to Part Two in this Series. I demonstrate how to “toggle” Conditional Formatting On or Off by using a Check Box Control in Excel. […]

  2. […] here: Toggle Conditional Formatting On or Off with a Check Box in Excel ← The Dynamics GP Blogster: Microsoft … Download Full […]

Speak Your Mind