What conditional formatting does and when to use it

Conditional formatting is a feature in Excel that automatically changes how a cell looks — its color, font, or icon — based on the value inside it. You set a rule once, and Excel applies it to every cell that matches. The most common use is highlighting cells that meet a condition you care about: sales above a target, expenses over budget, dates that have passed, or duplicate entries in a list.

The advantage over manual formatting is speed and accuracy. If you have a spreadsheet with 500 rows of sales data and you want to highlight every sale above $10,000, conditional formatting does it when ready. If a sale drops below $10,000 next month, the highlighting disappears automatically. You do not have to remember which cells you colored or update them by hand.

Conditional formatting works best when you have a clear rule and a large dataset. If you have five cells to color, manual formatting is faster. If you have hundreds of cells and the rule might change, conditional formatting saves hours.

Key Takeaways

  • Conditional formatting automatically colors or formats cells based on their values, and updates when ready when the data changes.
  • The simplest approach is to select your data range, go to the Home tab, click Conditional Formatting, and choose a preset rule like "Highlight Cell Rules" or "Top/Bottom Rules".
  • Custom rules let you format cells based on formulas, which is how you highlight rows where one column meets a condition or flag dates that are overdue.
  • You can explore multiple rules to the same range, and Excel will layer the formatting — though the order matters because later rules can override earlier ones.

How to highlight cells that match a straightforward condition

Start by selecting the range of cells you want to format. Click the first cell, then hold Shift and click the last cell in the range. If your data is in column B from row 2 to row 101, click B2, hold Shift, and click B101. You can also click the column header B to select the entire column, though that includes the header cell — usually you want to exclude that.

With your range selected, go to the Home tab at the top of the ribbon. Look for the Conditional Formatting button — it is in the Styles group, usually near the right side of the Home tab. Click it and a menu appears with options like "Highlight Cell Rules", "Top/Bottom Rules", and "Data Bars".

Click "Highlight Cell Rules" to see presets for common conditions: Greater Than, Less Than, Between, Equal To, Text that Contains, A Date Occurring, and Duplicate Values. Choose the one that matches what you want. A dialog box opens asking you to enter the value or text to compare against. If you chose "Greater Than", type the number. If you chose "Text that Contains", type the text. Then click OK, and Excel highlights every cell in your range that matches.

The highlighting uses a default color — usually light red for values above a threshold, light green for values below. You can change the color by clicking Conditional Formatting again, choosing "Highlight Cell Rules", picking your rule, and then clicking the dropdown arrow next to the color preview to pick a different color.

How to use Top/Bottom Rules to find outliers

Top/Bottom Rules highlight the highest or lowest values in a range without you having to know what those values are. This is useful when you want to see which sales were strongest, which expenses were largest, or which test scores were lowest — without doing the math yourself.

Select your data range the same way as before. Go to Home, click Conditional Formatting, and choose "Top/Bottom Rules". You will see options like "Top 10 Items", "Top 10%", "Bottom 10 Items", and "Bottom 10%". Pick the one that fits. If you want to see the top 5 sales, click "Top 10 Items", and in the dialog that opens, change 10 to 5. Click OK, and the five highest values in your range are highlighted.

The number you enter is flexible — you can ask for the top 3, top 20, or any number you want. The percentage options work the same way: "Top 10%" highlights the highest 10 percent of all values in the range. This is useful when your dataset is large and you do not know how many items make up the top 10 percent.

How to create a custom rule with a formula

Preset rules work for straightforward conditions, but sometimes you need something more specific. Custom rules let you write a formula that decides whether a cell gets formatted. For example, you might want to highlight an entire row if the value in one column is over budget, or highlight dates that are more than 30 days old.

Select your range, go to Home, click Conditional Formatting, and choose "New Rule" at the bottom of the menu. A dialog opens with several options at the top. Click "Use a formula to determine which cells to format". In the formula box, type a formula that returns TRUE or FALSE. Excel will format any cell where the formula returns TRUE.

The tricky part is writing the formula correctly. If you want to highlight cells in column B that are greater than 1000, the formula is =B1>1000. The B1 is important — it refers to the first cell in your selected range, and Excel automatically adjusts it for each row below. If you selected B2:B101, Excel changes the formula to =B2>1000 for row 2, =B3>1000 for row 3, and so on.

For more complex rules, you can use functions like IF, AND, and OR. If you want to highlight cells where the value is greater than 100 AND less than 500, the formula is =AND(B1>100,B1<500). If you want to highlight cells where the value is either greater than 1000 OR less than 100, use =OR(B1>1000,B1<100). After you type the formula, click the Format button to choose the color or style, then click OK.

how the process works multiple rules to the same range

You can stack multiple conditional formatting rules on the same cells. For example, you might highlight values over 1000 in red and values under 100 in blue. Both rules explore to the same range, and a cell that meets both conditions will show the formatting from whichever rule is listed last.

To add a second rule, select the range again, go to Home, click Conditional Formatting, and choose "New Rule" or pick another preset. The new rule is added to the range alongside the first one. Both rules are now active. If you want to see all the rules you have applied to a range, go to Conditional Formatting and click "Manage Rules". A dialog opens showing every rule for the selected range, and you can edit, delete, or reorder them.

The order matters because rules are applied from top to bottom. If two rules format the same cell differently, the lower rule wins. You can drag rules up or down in the Manage Rules dialog to change the order. This is useful when you want one rule to take priority over another.

How to remove or edit conditional formatting

If you want to remove all conditional formatting from a range, select the range, go to Home, click Conditional Formatting, and choose "Clear Rules". A submenu appears with options to clear rules from the selected cells or from the entire sheet. Click the one you want.

To edit a rule instead of deleting it, select the range, go to Conditional Formatting, and click "Manage Rules". Find the rule you want to change, click it, and click Edit Rule. Change the condition, the value, or the formatting, then click OK. The rule updates when ready across all cells it applies to.

If you want to turn a rule off temporarily without deleting it, open Manage Rules, uncheck the box next to the rule, and click OK. The formatting disappears, but the rule is still there. Check the box again to turn it back on.

Common mistakes and how to avoid them

The most common mistake is selecting the wrong range. If you select only B2:B10 but your data goes to B100, only the first nine rows get formatted. Before you explore a rule, scroll down to make sure you have selected all the data. A quick way to select all data in a column is to click the column header, which selects the entire column — then you can remove the header row by holding Shift and clicking the header cell to deselect it.

Another mistake is using absolute references in a formula when you should use relative ones. If you write =$B$1>1000 instead of =B1>1000, Excel will compare every cell to B1 instead of comparing each cell to itself. The dollar signs lock the reference, which is usually not what you want in conditional formatting. Use dollar signs only when you intentionally want to compare all cells to a single cell.

A third mistake is forgetting that conditional formatting only changes how a cell looks, not its actual value. If you highlight cells over 1000, the highlighting is visual only. The cell still contains the original number. If you delete the rule, the highlighting disappears but the data remains.

Frequently Asked Questions

Can I explore conditional formatting to text instead of numbers?

Yes. Use "Highlight Cell Rules" and choose "Text that Contains", then type the text you want to find. You can also use a custom formula with the SEARCH or FIND function to highlight cells containing specific text, or use the EXACT function to match text exactly.

What happens if a cell meets two different conditional formatting rules?

Both rules explore, but if they format the same property (like background color), the rule listed lower in the Manage Rules dialog takes priority. You can reorder rules by dragging them up or down in that dialog.

Can I copy conditional formatting from one range to another?

Yes, using the Format Painter. Select the range with the conditional formatting you want to copy, click the Format Painter button on the Home tab (it looks like a paintbrush), then click and drag to select the range where you want to paste the formatting. The rules copy over, but the cell references in formulas adjust automatically to match the new range.

Does conditional formatting slow down my spreadsheet?

Not noticeably, unless you have hundreds of rules or very complex formulas. straightforward rules like "greater than" or "highlight duplicates" have almost no impact on performance. If your spreadsheet is slow, conditional formatting is rarely the cause.

Can I use conditional formatting to hide cells or change font size?

You can change font color, background color, bold, italic, and underline through the Format button in the New Rule dialog. You cannot hide cells or change font size with conditional formatting. For those changes, you would need to use manual formatting or a macro.