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.
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
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:
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.