How to Use Conditional Formatting in Excel

A column of totals needs the numbers over 500 in red, and coloring them by hand lasts until the next data refresh. Conditional formatting applies the color as a rule attached to the range, so it re-evaluates every time a value changes.

Apply a built-in rule

Select the range first, then go to Home, Conditional Formatting, Highlight Cells Rules and pick Greater Than, Text that Contains, or Duplicate Values. Type the value in the box, choose a format from the dropdown, and click OK. Top/Bottom Rules covers the same ground for rankings, such as the top 10 items or everything below average.

Add data bars or a color scale

For comparison at a glance, choose Home, Conditional Formatting, Data Bars to draw an in-cell bar scaled to the largest value in the range, or Color Scales to shade low to high across a gradient. Neither asks for a threshold, which makes them the fastest way to see shape in a column of numbers.

Write a formula rule to shade a whole row

Select the full block of data, columns included, starting at A2. Choose Conditional Formatting, New Rule, Use a formula to determine which cells to format, and enter a formula written for the top-left cell of the selection:

=$D2="Overdue"

• $D: the column being tested, locked so every cell in the row checks column D.
• 2: the first data row, left unlocked so the rule moves down with each row.

Click Format, pick a Fill color, and click OK. Every row whose column D reads Overdue is shaded.

Manage or delete rules

Open Conditional Formatting, Manage Rules, and set the dropdown to This Worksheet to see every rule, not just the ones on the current selection. Edit a rule, change its Applies to range, reorder it, or select it and click Delete Rule. Clear Rules on the same menu wipes the selection or the whole sheet.

The dollar sign is what makes row shading work. Without it, =D2="Overdue" tests a different column in every cell, so only scattered cells turn color.

Keep the Applies to range tight. Rules pointed at entire columns such as $A:$Z recalculate over a million rows and make large files slow to scroll and save.

Sources. Use conditional formatting to highlight information in Excel, Use data bars, color scales, and icon sets to highlight data.