MS Excel: Conditional Formatting

Published on
3,700 Points
2 Endorsements
Last Modified:
Thomas Zucker-Scharff
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 paranthises.



Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Learn the basics of Skype For Business in office 365

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month