Use conditional formatting to color all the cells in a row, based on the value in one cell in that row. For later versions of Excel, please see: Format Entire Row Based on One Cell
With Excel's conditional formatting, you can check the value in one cell, and format other cells, based on that value. For example, you could colour the entire row in a table, if the values in column B are over a set value.
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.
You can watch the steps in this video, and the written instructions are below the video.
In this example, there is an absolute reference to column B ($B), to ensure that the conditional formatting in all columns refers to the value in column B.
If a relative reference (B) is used, the formula will be adjusted in each column, and won't work properly. Each cell will refer to the cell to its right, instead of refering to the cell in column B.
Download a zipped sample file for this Excel tutorial
For regular Excel news, tips, videos, and special offers, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.
Last updated: December 5, 2016 10:22 AM