MS Excel: Conditional Formatting and a COOL twist!

Thomas Zucker-ScharffSenior Data Analyst
Veteran in computer systems, malware removal and ransomware topics.  I have been working in the field since 1985.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.

I am not an expert when it comes to MS Excel.  I have taken a course or two and trolled the internet as most do for answers to certain questions.  I even ventured into the realm of VBA to write a script I spoke of in this article to automate sending of bulk emails through outlook.  But one of the built in features recently piqued my interest - conditional formatting.  

What is Conditional formatting?  It is the ability to format different ranges (rows, columns, cells, etc) of cells depending on certain predefined conditions, hence the name.  This may seem like an extra you don't really need, but if you want to make your spreadsheet more readable to non-technically oriented individuals, this is the tool for you!

Microsoft has already built in some basic rules, and they are very handy.  But while creating a tracking sheet for our annual retreat I decided to make the results a little easier to read.  The result was a great worksheet that is instantly understandable.  It took several steps to make the sheet do what I wanted.

First you will need to select the area you wish the rule to apply to (what rows/columns do you want highlighted?).

Next choose the conditional formatting icon

Step 3 is to choose the conditional formatting submenu you wish to use.  There are many builtin formulas or you can make one yourself (I felt this was better).

In the following steps I create my own rule so that an entire row is changed depending on the value in one field: 

First Select the create rule sub option:

In the resulting dialogue box select the last entry to create a rule similar to mine  

Click the cell icon to choose the cell you wish to base the rule on

Select the cell that the rule will be based on.  I selected the cell containing the value for whether the user confirmed their registration or not.

I cannot emphasize this step enough, delete the second $ in the resulting text.  So if it said =$g$2, delete the second $ so the result is =$G2.  The reason for this is that by deleting that second dollar sign you allow the rule to create a formula that will affect each row differently.

In the same dialogue box, type in the value for which you wish to check.  Again, in my example the dialogue entry is (=$G2="no") without the parentheses.

UPDATE: I have recently added another component to my conditional formatting documents, which even my wife, who normally looks at what I think is cool and says, "That's nice.",  this time she said, "That is cool!"  The cool part is building in the ability to easily turn conditional formatting on and off.  The way I found to do this that was easiest to implement, besides being cool, is as follows:

  • As above create the conditional formatting rules with one exception
    • create a cell with the word "on" (no quotes) in it
    • The conditional formatting formulas should read "AND(<first test condition>,<togglecellname>="ON")" - without the outer quotes. For instance, =AND($A2=1,$AA$1="ON")
  • Create a dropdown list consisting of the choices
    • ON
    • OFF
  • Create a cell according to the dropdown list article here.  This will give you a dropdown list of on or off.
  • When on is selected the conditional formatting is turned on
  • When off is selected the conditional formatting is turned off

You can see how I implemented this in this attached spreadsheet.  Book1.xlsx

If you have any questions Don't hesitate to use the blue "Ask a Question" button at the top of the page, or comment on this article!


If you think this article was helpful, please do click the Thumbs Up icon to the bottom left of this text. It helps me out and lets me know the direction I should take for future articles that I write. 

Thomas Zucker-ScharffSenior Data Analyst
Veteran in computer systems, malware removal and ransomware topics.  I have been working in the field since 1985.

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.