Change cell color based on value of cell

One of the most common practices in Excel is to change (manually) the colour of a cell based on what is in the cell. So if you are doing school reports you want the marks less than 50% to be red, higher than 80% to be green and perhaps a variety of colours in between. The manual process is tedious (and prone to error). But Excel can automatically change the cell color based on the value of the cell.

The steps to follow are:

1- Highlight the cells as shown below, i.e.

  • click on Home, then
  • Conditional Formatting, then
  • Highlight Cell Rules and
  • choose Less Than.

Change cell color based on value of cell

2- Type in the % that it must be less than and choose Custom Format as shown below. Note that the numbers you type in must match the numbers you have in the cells, so if they are % you type in % but if they are whole numbers you need to type in whole numbers

Change cell color based on value of cell

3- Go to Fill and Choose the colour you want to use for the  Less Than 35% in this case

Change cell color based on value of cell

4- Then you can say OK until you are back in the spreadsheet.

5- To add the next colour you do the same thing again i.e.

  • make sure all the relevant cells are highlighted,
  • click on Home,
  • Conditional Format,
  • highlight Cell Rules,
  • BUT now choose BETWEEN

Change cell color based on value of cell

6- Specify the numbers you want to be between, and then choose a colour in the same way as before

Change cell color based on value of cell

7- You can add as many as you want by repeating this process.