[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


MS Excel: Conditional Formatting

Published on
3,887 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

PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Join & Write a Comment

A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…
See the Basics of Office 365's Note Taking app, OneNote

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month