Conditional formatting

Excel’s conditional formatting features let you control how a cell is formatted – such as changing colors – depending on the data the cell contains. If the cell data meets the conditions you set, then the formatting is applied to the cell.

With conditional formatting, you can select one or more cells, and create rules (conditions) for when and how those cells are formatted. The conditions can be, based on the selected cell’s contents, or based on the contents of another cell.

You can control the following formats:

  • Number format
  • Font, font style, and font colour (but not font size)
  • Fill colour and fill pattern
  • Border colour and border style (but not border thickness)

If the rules (conditions) that you specified are met, then the formatting is applied. For example, you can set conditional formatting so that a cell turns red if its value is low, and turns green if its value is high.

 

Apply Conditional Formatting to a Cell

In this example, you’ll set conditional formats so that a cell:

  • turns green if it contains a value higher than 75 and
  • turns red if it contains a value lower than 50.

Follow these steps to apply conditional formatting to cells:
1. In cell I1, type the high value — 75
2. In cell I2, type the low value — 50

3. Select the cells to be formatted. In this example, cells E2:E7 are selected.
4. On the Ribbon’s Home tab, click Conditional Formatting

5. To format the high values, click Highlight Cell Rules, then click Greater Than…

6. In the Greater Than window, delete the value that appears, and click on cell I1, where the High value is entered.

7. Click the drop down list for formats, and click Custom Format.

8. In the Format Cells window, click the Fill tab, and click on the green fill colour that you want.

9. Click OK to close the Format Cells window, and click OK to close the Greater Than window. The cells with values greater than 75 are now coloured green.

 

Apply 2nd Conditional Format

To colour the low values in red fill, you can apply a second condtional formatting rule to the cells.
1. Select the cells to be formatted. In this example, cells E2:E7 are selected.
2. On the Ribbon’s Home tab, click Conditional Formatting

3. To format the high values, click Highlight Cell Rules, then click Less Than…

4. In the Less Than window, delete the value that appears, and click on cell I2, where the Low value is entered.

5. Click the drop down list for formats, and click Custom Format.

6. In the Format Cells window, click the Fill tab, and click on the green fill colour that you want.

7. Click OK to close the Format Cells window, and click OK to close the Less Than window. The cells with values greater than 75 are now coloured green, and cells less than 50 are red.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

code

clearPost Comment