Excel – Conditional Formatting Example

I have been asked by a former student for some help with Excel’s Conditional Formatting. My student needed help setting up a column of data with a cell fill color, red or green, based on wether the previous column was lower or higher.  Here is the Excel – Conditional Formatting Example with the data cleaned for privacy.

Non Formatted Excel Data

Here is a pic of the data before formatting was applied. The student wanted Col C to be formatted with a fill color based on the comparison to Col B.

Excel Conditional Formatting

We begin by selecting Column C (the column we need to apply the formatting to) and on the Home Menu Ribbon in Excel click the Conditional Formatting Icon and select New Rule. The following New Formatting Rule window will appear.

new formatting rule

Select the option shown (Use a formula to determine which cells to format).  Set the text box to the formuala “=$C1>$B1”.  This will compare the two cells in the same row.  Click the Format button to set your desired format. In this case the fill color of the cell is set to a green color. Then click OK.

With Column C still selected click on the Home Menu Ribbon then the Conditional Formatting Icon and select New Rule again to create the rule for when the condition is false.

Excel Conditional Formatting

Set the options as shown above (notice the formual is now set for “less than”). Choose a differnt format for this rule as desired. Then click the OK button.

Result

As you can see below the formatting rules apply the fill colors according to your rules. Also note that as we only used “<” and “>” if the number is the same as the previous column it is not formatted.

Conditional Formatting Applied

The two rules we just created above can be viewed or edited by first selecting Col C again then clicking on the Home Menu Ribbon then the Conditional Formatting Icon and select Manage Rules.

Excel Manage Rules

You may add\edit\delete more rules as necessary.

Summary

Using Conditional formatting is very powerful in Excel. It can great help ease analyzing data by makeing changes more visual. Hope this helps someone else. Plese leave questions or comments in the comment sections.

For more Excel Tips and Tricks