Excel 2016 Tip: Conditional formatting based on cell value

Source:www.opentechguides.com | Date Published: 2016-12-09 17:25:05

This article shows you how to format a row or column based on a cell value.

Let's take the example below of a worksheet containing three columns - ID, Name and Marks. You want to format each row based on Marks. If mark is more than 80 then the entire row will change the colour to green, between 50 and 80 will be in yellow and less than 50 in red.

excel 2016 conditional formatting

Conditional formatting a row

Conditional formatting a column

Consider the example below where you have marks scored by pupils in vaarious subject and also their total marks. You want to automatically give a red color to column if the total marks is less than 250. This is how you do it.

  • Select the cells that contain the data you want to format. In this example it is from cell C3 to F7.
  • On Excel menu, click Home then from Styles group select Conditional Formatting → New Rule
  • Select Use a formula to determine which cells to format.
  • Enter the formula below.
    =(C$7 < 250)
  • Click Format then click Font tab and pick red colour and click Ok
  • Click Ok again on the Formatting rule dialog.
  • The procedure for conditionally formatting Rows and Columns are similar except for the formula. In case of column formatting, the dollar sign($) precedes the column reference in the formula (C$7 < 250) and in case of Row formatting, the $ sign is for the row reference ($D3 > 80 ).

    Open Tech Guides | www.opentechguides.com