Conditional Formatting

Your current progress:
0%

Hey - work along with me.  Download the sample file and click the worksheet Conditional.

Conditional Formatting your worksheet is a super way to improve the look of your worksheets.  Its makes them more interesting to your audience and helps to show information in a unique way.  This session shows 4 examples of applying Conditional Formatting to your worksheets.  The examples shown in this session are on the Conditional sheet in the sample file.  Here are the topics:

  1. Example showing Conditional Formatting Highlighting
  2. Example showing Conditional Formatting Icons
  3. Example showing Conditional Formatting Data Bars
  4. Example showing Formula-Driven Conditional Formatting
  5. Remove Conditional Formats from parts of worksheet
  6. Example showing how to use Formulas to set Conditional Formatting

Here is the worksheet we are using:

Our first example is great for changing colors of cells if they meet our condition/criteria.  We want to look at the numbers in column B of the worksheet - and if a number in that column is greater than 3,000 we want the cell to be color-coded so that it stands out in the worksheet.  Here are the steps:

  1. select the cells in column B that we want to format
  2. on Home tab click Conditional Formatting button
  3. in drop down list point at the top choice Highlight Cells Rules and then on the side menu click Greater Than
  4. in the Greater than window enter the value that you want to set the condition for - in our example 3000
  5. click the down arrow next to the color options and select a choice in the drop down list
  6. click OK

This next example places color markers ICONS in cells if the condition/criteria.  This is great when you want to test for several conditions.  We want to look at the numbers in column D the variance column of the worksheet - and if a number in that column is:

  • greater than or equal >= 50 we want to put a green icon in the cell
  • if the number in the same column is greater than or equal >= 10 but less than 50 we want to place a yellow icon in the cell
  • and if the number in the same column is less than < 10 we want to place a red icon in the cell

Here are the steps:

  1. select the cells in column D that we want to format
  2. on Home tab click Conditional Formatting button
  3. in drop down list point at the top choice Icon and then on the side menu click 3 Traffic Lights Rimmed
  4. Excel automatically applies the colors using its own built-in rules (any cell with a number in the top third percent gets a green icon; in the second third percent gets a yellow icon and in the lower third percent gets a red icon)

   5. we want to change the rule to make it into a number rule - >= 50; between 10 and 49; and less 10
   6. we need to edit the rule and to do that mke sure the cells are still selected - click Conditional Formatting button again and select at the bottom of list Manage Rules
   7. In the Manage Rules window click the Edit Rule tab
   8. In the lower part of the window change the settings so that it looks like the sample below
   9. click OK

This example is a really quick way to compare large numbers to small numbers.  Excel looks at the selected cells in comparison to each other and the cells with larger values have more cell-highlighting compared to cells with smaller values have less cell-highlighting

Here are the steps:

  1. select the cells in column C that we want to format
  2. on Home tab click Conditional Formatting button
  3. in drop down list point at Data Bars and on the side menu click a color

Here are the steps for removing conditional formats from parts of your worksheet.  We are remove the formats from the cells in column B.

  1. select the cells that you want to remove conditional formatting - in our example B5 to B15
  2. click Home tab and then click Conditional Formatting
  3. on the drop down list point at Clear Rules - and on side menu click Clear Rules from Selected Cells

The examples shown so far apply conditional formatting to cells that we select.  You can also set condtions based on the contents of other cells in the worksheet by creating formulas within Conditional Formatting.  The worksheet we are using for this example is PassFail from our sample workbook.  A snapshot of the sheet is shown below.  It shows a list of students and their grade for a course.  If their grade is greater than or equal to 60 (>=60) we want to format the student's name which is column A.

Here are the steps:

  1. select cells A3 to A13 - these are the cells we want to format if the mark is >=60
  2. on Home tab click Conditional Formatting and then select New Rule
  3. in the New Rule window select Based on a Formula
  4. in the formula box type the formula =B3>=60
  5. in the bottom right corner of the window click the Format button
  6. in the Format Cells window change the font color
  7. click OK to close the Font window - click OK again to close the rule window