Search Contextures Sites

Format Entire Row Based on One Cell

You can apply conditional formatting that checks the value in one cell, and applies formatting to other cells, based on that value. For example, if the values in column B are over a set value, make the row blue.

Format entire row with Conditional Formatting

Video: Format Row with Conditional Formatting

Format the Cells

Download the Sample File

More Excel Conditional Formatting Tutorials

For Excel 2003 instructions, please see:Format Entire Row Based on One Cell - Excel 2003

Format entire row with Conditional Formatting

You can apply conditional formatting that checks the value in one cell, and applies formatting to other cells, based on that value. For example, if the values in column B are over a set value, make the row blue.

conditional formatting on entire row

In this example, we'll colour cells blue, if the number of units, in column B, is greater than 75. At a glance, we'll be able to see which dates have a high number of units.

Video: Format Row with Conditional Formatting

You can watch the steps in this video, and the written instructions are below the video.

Format the Cells

  1. Select the cells that you want to format (A2:D4 in this example)
  2. On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
  3. In the New Formatting Rule window, click Use a Formula to Determine Which Cells to Format
  4. In the rule box, enter a formula that refers to the active cell in the selection. In this example, we selected A2:D4, and cell A2 is the active cell. So, in the formula, we'll refer to cell B2, because it's in the active row.

        =$B2>75

We use an absolute reference to column B ($B), to ensure that the conditional formatting in all columns refers to the value in column B.

If we used a relative reference (B), the formula will be adjusted in each column, and won't work properly. Each cell would refer to the cell to its right, instead of refering to the cell in column B.

  1. Click the Format button.
  2. In the Format Cells dialog box, select the formatting options that you want. In this example a medium blue fill colour was selected. You could also select a Font format, and a Border format.

    conditional formatting rule

  3. Click OK to close the Format Cells dialog box.

    conditional formatting on entire row

Download the Sample File

Download a zipped sample file for this Excel tutorial

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Search

Search Contextures Sites

 

 

Excel Conditional Formatting Tutorials

Excel Conditional Formatting -- Introduction

Excel Conditional Formatting -- Multiple Cells

Excel Conditional Formatting -- Examples

Excel Conditional Formatting -- Documentation

Excel Conditional Formatting -- Data Bars  

 

 

More Tutorials

Conditional Formatting Intro

Conditional Formatting Examples

Conditional Formatting Documentation

Conditional Formatting Data Bars  

 

Contextures
Excel news
by email

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

 

 

 

__

 

Privacy Policy

 

Contextures Inc., Copyright 2015
All rights reserved.

 

Last updated: March 17, 2015 12:15 AM