How to Highlight Cells with Conditional Formatting in Google Sheets

You want every amount over 1,000 to stand out, or every row marked Overdue to turn red, without coloring cells by hand. Conditional formatting applies the color from a rule, so it updates as the values change.

Select the range to format

Drag across the cells the rule should cover, for example B2:B100 for a column of amounts.

Open Format, Conditional formatting

Go to Format, Conditional formatting. A panel titled Conditional format rules opens on the right. The Apply to range box shows the cells you selected and can be edited.

Pick a condition under Format rules

Open the drop-down under Format cells if and choose a condition. Common ones are Text contains, Text is exactly, Greater than, Less than, Is between, and Date is before. A value box appears under it. For amounts over 1,000, choose Greater than and type 1000.

Choose a color and click Done

Under Formatting style, pick a fill color from the paint bucket, or change the text color or set bold. Click Done. Every cell in B2:B100 above 1,000 is now colored, and a cell that later drops below 1,000 loses the color on its own.

Use a custom formula for anything the list does not cover

Choose Custom formula is at the bottom of the condition list and type a formula that returns TRUE or FALSE. Write it for the top-left cell of the range and Sheets adjusts it for every other cell:

=B2>1000

To color a whole row based on one column, apply the rule to A2:D100 and use =$C2="Overdue". The $ locks the column to C while the row number changes, so each row is tested against its own status cell.

Switch to Color scale for a gradient

At the top of the panel, click the Color scale tab instead of Single color. Pick a minimum and maximum color, and each cell is shaded in proportion to its value, low amounts pale and high amounts dark.

To edit, reorder, or delete rules. Opening Format, Conditional formatting again lists every rule on the sheet. Click a rule to edit it, drag it to change its position, or click the trash icon to remove it. Rules apply in the order listed, and when two rules set the same property on one cell, the rule higher in the list wins.