How to Use Conditional Formatting (Highlight Cells Automatically)
- Peter J.
- Jul 3
- 5 min read
Conditional formatting automatically changes the color of a cell based on its value. Select your cells, go to Format > Conditional formatting (Google Sheets) or Home > Conditional Formatting (Excel), set a rule like “greater than 100,” and pick a color. Any cell that meets that condition highlights automatically.
This is one of the most powerful visual tools in any spreadsheet. With conditional formatting, you can instantly spot overdue dates, flag high-value numbers, highlight duplicates, or color an entire row based on a status. No manual coloring required.
How to Add Conditional Formatting in Google Sheets
Step 1: Select the Range
Click and drag to select the cells you want to format. You can select an entire column by clicking the column letter.
Step 2: Open Conditional Formatting
Go to Format > Conditional formatting in the menu bar. A panel appears on the right side of the screen.
Step 3: Set the Condition
Under “Format rules,” click the dropdown that says “Format cells if…” and choose a condition:
Condition | Use it when… |
Greater than | Highlighting sales above a target, temperatures over a threshold |
Less than | Flagging scores below a passing grade |
Text contains | Marking rows that contain a specific word or status |
Date is before/after | Highlighting overdue tasks or upcoming deadlines |
Is empty | Finding cells that haven’t been filled in |
Custom formula is | Advanced: applying a formula-based rule (see below) |
Type the comparison value in the field that appears below the condition.
Step 4: Choose the Formatting Style
Click the fill color swatch under “Formatting style” to choose a background color. You can also change the text color, make it bold, or add underlines.
Step 5: Click Done
Click Done. The formatting applies immediately to any cells that match your rule. If you change a cell’s value later, the formatting updates automatically.
How to Add Conditional Formatting in Excel
Step 1: Select the Range
Click and drag to select the cells.
Step 2: Open Conditional Formatting
Go to the Home tab in the Ribbon. In the Styles group, click Conditional Formatting. A dropdown menu appears.
Step 3: Choose a Rule Type
Excel offers several categories:
Highlight Cells Rules; Greater than, Less than, Between, Equal to, Text that contains, Duplicate values, etc
Top/Bottom Rules; Top 10 items, Top 10%, Above average, etc
Data Bars; Colored bars showing relative values within cells
Color Scales; Gradient color coding from low to high values
Icon Sets; Icons (arrows, traffic lights, stars) based on value ranges
For most uses, start with Highlight Cells Rules.
Step 4: Set the Value and Format
A dialog box appears. Type your threshold value (like 100). In the dropdown, choose a color format (yellow fill, red text, etc.) or choose “Custom Format” for more options.
Step 5: Click OK
Click OK. The formatting applies.
How to Use Color Scales
Color scales apply a gradient; for example, green for high values, red for low, yellow in the middle. This is great for visualizing a column of numbers at a glance.
Google Sheets: In the conditional formatting panel, click the Color scale tab instead of “Single color.” Choose a preset or customize the min, midpoint, and max colors.
Excel: Go to Conditional Formatting > Color Scales and pick a gradient from the gallery.
How to Highlight Duplicates
Finding duplicate values is a common task. Both platforms make it easy.
Google Sheets:
Select the range.
Go to Format > Conditional formatting.
Set the condition to Custom formula is.
Type: =COUNTIF($A$1:$A$100, A1) > 1 (replace $A$1:$A$100 with your actual range).
Choose a highlight color and click Done.
This highlights every cell that appears more than once in the range.
Excel:
Select the range.
Go to Conditional Formatting > Highlight Cells Rules > Duplicate Values.
Choose “Duplicate” in the first dropdown and pick a format.
Click OK.
For a full guide on removing duplicates (not just highlighting them), see our article on how to remove duplicates in Google Sheets and Excel.
How to Highlight an Entire Row Based on One Cell’s Value
This is one of the most impressive uses of conditional formatting; coloring the whole row when a status column says “Done” or when a deadline has passed.
Step 1: Select the Entire Data Range
Select all the rows and columns of your data; for example, A2:E100 (starting from row 2 if row 1 is headers).
Step 2: Add a Custom Formula Rule
In Google Sheets: choose Custom formula is as the condition. In Excel: choose Use a formula to determine which cells to format.
Step 3: Write the Formula
Type a formula that references the column you want to base the rule on. For example, if your status is in column D:
=$D2="Done"Breaking this down:
$D; always look in column D (the $ locks the column).
2; no $ before the row number, so it adjusts for each row.
="Done"; the condition to match.
Step 4: Choose the Color and Apply
Pick your color and click Done (or OK). Every row where column D says “Done” highlights in that color.
💡Tip: This technique works with IF function logic too. You’re basically running a logical test across the whole row.
Managing Multiple Rules
You can have multiple conditional formatting rules on the same range. They’re applied in order; the first matching rule wins, and lower rules may or may not apply depending on settings.
Google Sheets: In the conditional formatting panel, you’ll see all rules listed. Drag them to reorder.
Excel: Go to Conditional Formatting > Manage Rules to see all rules, reorder them, or delete specific ones.
How to Remove Conditional Formatting
Google Sheets:
Select the range.
Go to Format > Conditional formatting.
Click the trash icon next to the rule you want to remove.
Excel:
Select the range.
Go to Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.
Or use Conditional Formatting > Manage Rules to selectively remove specific rules.
Common Mistakes
Selecting the wrong range. If you select too few cells, the rule won’t apply to new rows you add. Consider applying to the whole column to future-proof it.
Getting the dollar signs wrong in custom formulas. In =$D2="Done", the $ before D is critical. Without it, the formula looks in a different column for each cell it evaluates.
Applying too many competing rules. If colors are clashing, check the rule list and delete or reorder. The topmost matching rule takes priority.
Thinking the color affects the cell’s value. It doesn’t. Conditional formatting is purely visual.
Frequently Asked Questions
Can I apply conditional formatting to an entire column?
Yes. Click the column letter to select the whole column, then apply your formatting rule. In Excel, the rule will apply to all 1 million+ rows; so use a specific range if performance matters.
Can I copy conditional formatting from one range to another?
Yes. Select the cell or range with the formatting, copy it (Ctrl + C), then select the destination and use Paste Special to paste formatting only. In Excel: Paste Special > Formats. In Google Sheets: Ctrl + Shift + V > Paste format only.
Can I apply conditional formatting based on another cell’s value?
Yes. Use a custom formula. For example, to highlight cells in column B whenever column A is greater than 50: select column B, use the custom formula =$A1>50. The $A locks the formula to always look at column A.
Does conditional formatting slow down my spreadsheet?
Large numbers of complex rules (especially custom formulas over huge ranges) can slow things down. Keep rules simple and limit the range to only the cells that need formatting.
Wrapping Up
Conditional formatting turns a wall of numbers and text into a visual dashboard. Color-coded data is faster to read, easier to act on, and harder to misinterpret. Start with one simple rule; highlight anything above your target; and you’ll quickly see how useful it is.
Ready to do more?
Add drop-down lists so you can control what values go into the cells you’re formatting.
Learn how to remove duplicates that your conditional formatting just revealed.
Understand IF functions to write smarter custom formula rules.
Comments