Using Excel’s conditional formatting: changing colors and fonts to highlight data

I wanted to show one other thing helpful thing that Excel can do when you need to highlight data called conditional formatting. (Note: this tutorial is image heavy!)

Conditional formatting basically allows you to use colors, font styles, and borders to highlight cells that meet certain conditions. Unlike IF and other Excel functions, conditional formatting is found as a pull-down menu item in Excel rather than a function that you can type into a cell.

With the same salesman example I’ve been using in the previous tutorials on using the IF function, let’s look at how this would work. First, here’s the raw data, unformatted:

Before we go further, if you’d like to work through the examples yourself, here’s the raw data. First, open up a blank excel worksheet. Next, highlight the table below. Copy it, and then go back to your excel worksheet. Go to cell A1 (or another empty cell, if you want to put the data elsewhere), and then select “Edit” from the menu bar. Select “Paste Special” and then “Text” from the popup box. Click “OK”. The data should appear in your Excel worksheet just as it does above.

Ok, now back to the tutorial.

Suppose you wanted to highlight all the sales that were above the minimum amount of $100K green, and any that were below the minimum amount red. How would you do that?

Step 1: Select the range of cells you want the formatting applied to with your mouse. Just click on cell B6 and drag your mouse to B15.

Step 2: Under the “Format” menu item, choose “Conditional Formatting”.

Step 3: A pop-up menu will appear like this (click to enlarge).

Step 4: Fill in your menu with the conditions. In this case, we want two conditions. Under “Condition 1”, and “Cell Value Is”, we choose “greater than” from the drop down menu (click to enlarge).

Then we enter B2 into the blank by either typing it in or clicking on the box next to that menu item and selecting B2 using the mouse (click to enlarge).

Step 5: Click on the “Format” menu item and yet another pop-up menu appears called “Format Cells”.

Click on the “Patterns” tab and choose the bright green color (click to enlarge).

Then hit “OK”, and your first conditional formatting should be complete, like so (click to enlarge):

At this point, you might be done, if you have only one condition. If so, you’d hit “OK” again and watch as your cells are automatically formatted. However, we said we also wanted to highlight any sales that were below $100K, so we repeat the process above one more time. You can add up to 3 conditions to check for in a conditional format.

Step 6: Click on the “Add>>” button, and another set of conditions to fill out, called “Condition 2” appears (click to enlarge).

This time, we choose “less than” and cell B2 again as the criteria (click to enlarge).

Step 7: Click on the “Format” button, the “Patterns” tab, and choose red as your color (click to enlarge).

Then hit “OK”, and your second conditional format should be added like so (click to enlarge):

Step 8: Finally, hit OK again, and watch your cells automatically change their background color based on the data that’s in them.

Notice that cell B7 remains white, because the number is equal to $100K and doesn’t fit either of our conditions. If we wanted to, we could change one of the criteria in our conditional formats to include the “equal to” case.

The thing about conditional formatting is that once it’s applied, it stays with the cells until you delete the condition. So, for example, if you changed cell B8 to “1”, the cell would automatically change colors to red because 1 is less than 100K:

(The cell next to it, under “Bonus” also changed, but that’s because it contains a conditional IF equation from the previous tutorial.)

To remove conditional formatting, you have go through a few steps. First, select the cells for which you want to remove the formatting. Then, go back under the “Format” and then “Conditional Formatting” menu items and click on “Delete”. At this point, another pop-up menu appears, asking which condition(s) you want to remove. To remove all formatting in this case, you’d check the boxes by condition 1 and condition 2 (click to enlarge):

Then we click “OK” on the popup, “OK” again on the conditional formatting popup menu, and voilà, all turns back to normal:

One of the most useful ways I’ve seen conditional formatting used is in corporate finance, when Excel worksheets are sent around to be filled in by various people. You can set up sheets so that cells automatically change colors when certain criteria are met.

For example, suppose you’re trying to track a product’s cost. As long as a product’s cost is at or below target, the cell stays green. If the cost is over the target 5% or less, you want to highlight this yellow. If the cost is greater than 5% above target, you want to call this out using red.

Here’s a simple excel spreadsheet that demonstrates conditional formatting using that scenario as an example. In this case, someone who isn’t comfortable with using Excel can enter in the product’s current cost and see how far off target it is automatically. Feel free to download the spreadsheet, give it a try and see for yourself!

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss

***************************************************