Conditional Formatting in Excel

Conditional Formatting is one of the features that takes Excel from a simple spreadsheet tool to an application builder. Depending on the value of a cell, you can change the formatting of that cell. Formatting in cells can also be conditional based off of the values in another cell or the results of a calculation. This is done dynamically saving the user the trouble of changing the format themselves.

I use conditional formatting in Excel to track things like invoicing. With conditional formatting, I can tell at a glance which clients of mine owe money and which ones are paid up. I also use different coloring for charges and deposits. Less than, greater than, and equals a number In this tutorial, we will set up cells to be highlighted based on whether or not they are less than, greater than, or equal to zero. We'll make cells change colors based off of this condition. Let's get started.

1. Open a new Excel file.

2. Type in some sample numbers in a column. In my sample, I have typed 0, -1, and 1. When we're finished with the tutorial, our sample file will show the conditional formatting in action immediately. In column C, I have typed descriptions on which color the cells should be.

1. Select the cells you want to be affected by the conditional formatting. In this example, I have selected B2, B3, B4, C2, C3, and C4.

1. Go to Format > Conditional Formatting...

The Conditional Formatting box will appear.

1. In this box, change Condition 1 to "Formula Is".

2. Then type "=$B2=0" without the quotation marks. This is saying that if the cell in this row that resides on the B column is equal to zero, then the conditional formatting will be applied.

3. Click Format... to specify the format applied when the condition is true. The format can include font, cell color, borders, and more.

In this example, I set the formatting to color the cell blue.

1. Click Add >> to add another row in the Conditional Formatting box.

2. Repeat steps 5 through 7 twice. Specify different formatting options and use the following formulas: "=$B2<0" and "=$B2>0". When you're done, your Conditional Formatting box should look like the sample below.

1. When you are done, click OK and your Excel file should look like the example below.

If the formatting didn't work, go over the steps to determine what you did wrong. If you get really lost, you can download the sample: Other possibilities The possibilities are almost endless with conditional formatting. Instead of using "Formula Is", you can use "Cell Value Is". From there, you can specify what the cell value should be equal to, should not be equal to, should be between, etc. Sadly, you are limited to only three possible conditions on any given cell. If you are going to be using alpha characters instead of numeric characters, the alpha characters must reside within quotation marks in your formula. For example: =$B2="Scott". There are the basics of conditional formatting in Excel. If you use this tutorial in a project, post your story here. You might spark some ideas in other readers.