Using Conditional Formatting in Excel

Owned by Tiffany Linhardt (Unlicensed)
Last updated: Dec 02, 2014

Creating Rules

  • Select the data you want to apply the formatting to
  • Click on Conditional Formatting on the Home Tab
  • Click on "New Rule" (see Fig 1)
  • A new window will appear

Fig 1

Specific Formatting

  • To create formatting based on specific rules you will want to choose the "Format only cells that contain" option.
  • Create your rule by choosing the appropriate options (see Fig 2)
  • You will most likely want to keep the first drop down field as "Cell Value"
  • Then choose the appropriate option in the next drop down field
  • Enter in the necessary data in the next field(s)
  • Click the "Format" button
    • This button allows you to choose how you want the cell to look if it meets the conditions you specified above
    • Switch to the fill tab if you want the entire cell to be a specific color
    • You can change font color, size, etc as well (if desired)
  • Repeat these steps as necessary for additional formatting

Fig 2

Example

You have some data in Row 1. In this data you would like any cells that have a value that is between 30 and 50 to be colored green. You also want any cells that have the number 20 in them to be colored yellow. You would do the following:

  1. Select all the data in Row 1
  2. Click on the Conditional Formatting button on the Home tab
  3. Click on New Rule
  4. Click on "Format only cells that contain"
  5. Leave the first two drop down fields as they are
  6. Type in 30 in the first box next to the drop down fields
  7. Type in 50 in the box next to the previous box
  8. Click on the "Format" button
  9. Click on the "Fill" tab
  10. Click on a green color
  11. Click "Ok"
  12. Click "Ok"
  13. Repeat steps 1 – 4
  14. Change the 2nd drop down menu to say "Equal to"
  15. Type 20 in the next box
  16. Repeat steps 8 & 9
  17. Click on a yellow color
  18. Repeat steps 11 & 12