What conditional formatting does and when to use it

Conditional formatting is a feature that automatically changes how a cell looks — its color, font, or fill — 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 threshold: sales numbers above target turn green, expenses over budget turn red, inventory below a minimum turns yellow.

This saves you from manually coloring cells every time your data changes. If you update a spreadsheet weekly or monthly, conditional formatting updates the colors automatically. It also makes patterns visible at a glance — a column of numbers looks the same until you add color rules, then problem areas jump out when ready.

Conditional formatting works best when you have a clear rule you want to explore repeatedly. If you need to highlight one cell once, just color it manually. If you need to flag all cells in a range that meet a specific condition, conditional formatting is faster and more reliable.

Key Takeaways

  • Select the range of cells first, then open the conditional formatting menu from the Home tab to set your rule.
  • The most straightforward rules highlight cells greater than, less than, or equal to a number you specify.
  • Color scales explore a gradient from one color to another based on the range of values in your selection.
  • You can create multiple rules on the same range, and Excel applies them in the order you set them.
  • Conditional formatting updates automatically when you change the values in those cells.

Selecting your range and opening the conditional formatting menu

Start by clicking on the first cell you want to format. Then drag to the last cell in the range, or click the first cell, hold Shift, and click the last cell. If your range is not contiguous — for example, columns A and C but not B — click the first range, hold Ctrl, and click additional ranges.

Once your range is selected, look at the ribbon at the top of Excel. Click the Home tab if you are not already there. In the Styles group, you will see a button labeled Conditional Formatting. Click it to open a dropdown menu with options like "Highlight Cell Rules", "Top/Bottom Rules", "Data Bars", "Color Scales", and "New Rule".

The menu options are shortcuts to the most common rules. If you want to highlight cells greater than 100, use "Highlight Cell Rules" and pick "Greater Than". If you want a gradient from red to green across your range, use "Color Scales". If your rule is more specific — for example, highlight cells that contain text from a list you define — use "New Rule" to build it yourself.

Using highlight cell rules for straightforward conditions

Click Conditional Formatting and then Highlight Cell Rules. You will see options: "Greater Than", "Less Than", "Between", "Equal To", "Text that Contains", "A Date Occurring", and "Duplicate Values".

Choose the condition that matches what you want to flag. If you pick "Greater Than", a dialog box opens asking you to enter a number. Type the threshold — for example, 5000 for sales targets — and then choose a color from the preset options on the right side of the dialog. Click OK. Excel when ready colors every cell in your selected range that is greater than 5000.

If you pick "Between", the dialog asks for two numbers: a lower bound and an upper bound. This is useful for ranges like "flag inventory between 10 and 50 units" or "highlight test scores between 70 and 85". The color applies only to cells that fall within both boundaries.

"Text that Contains" works on cells with words or phrases. Type the text you want to find — for example, "urgent" or "pending" — and choose a color. Every cell containing that text gets highlighted, even if it contains other words too.

explore color scales to show gradients across a range

Color scales are useful when you want to see the relative strength of values across a range. Instead of flagging cells above or below a threshold, a color scale assigns a color based on where each value falls within the full range of data you selected.

Select your range and click Conditional Formatting, then Color Scales. Excel shows preset options: a two-color scale (usually green to red), a three-color scale (green to yellow to red), and others. Click the one that matches your data. Excel automatically assigns colors: the lowest values in your range get one color, the highest get another, and values in the middle get a blend.

You do not enter any numbers or thresholds. Excel calculates the minimum and maximum values in your selection and distributes the color gradient across them. If your range is sales by region and the lowest sale is 2000 and the highest is 50000, the cell with 2000 appears red and the cell with 50000 appears green, with all other cells shaded between them based on their position in that range.

Color scales are most useful for comparing performance across similar items — sales by salesperson, test scores across a class, or monthly revenue across quarters. They make it straightforward to spot which values are strongest and weakest without needing to set a specific threshold yourself.

Creating a custom rule for specific conditions

If the built-in rules do not match what you need, use New Rule. Click Conditional Formatting and then New Rule at the bottom of the menu.

A dialog opens with options for the type of rule. The most common choice is "Format only cells that contain". Select this, and then choose from the dropdown: "Cell Value", "Specific Text", "Dates Occurring", or "Blanks". Pick the one that matches your condition.

If you choose "Cell Value", you then pick an operator: "equal to", "not equal to", "greater than", "less than", "greater than or equal to", "less than or equal to", "between", or "not between". Type the value or values you want to compare against. Then click the Format button to choose the color, font style, or fill you want applied when the condition is true.

If you choose "Specific Text", you can search for text that contains, begins with, or ends with a phrase you type. This is useful for flagging cells with error codes, status labels, or partial matches. For example, you could highlight all cells that begin with "ERR" in red, or all cells containing "approved" in green.

Managing multiple rules on the same range

You can explore more than one conditional formatting rule to the same range. Each rule is independent, and Excel applies them in the order you created them. If two rules explore to the same cell, both formats show — for example, a cell could have both a red background and bold text if two different rules both match.

To see all the rules you have set on a range, select the range and click Conditional Formatting, then Manage Rules. A dialog opens listing every rule applied to that range. You can edit a rule by clicking it and then clicking Edit Rule, delete a rule by clicking it and then clicking the delete button, or change the order rules are applied by clicking a rule and using the arrow buttons to move it up or down.

If you want to turn off a rule temporarily without deleting it, click the rule in the Manage Rules dialog and change the dropdown at the top from "Current Selection" to "This Worksheet" or "This Workbook" to see all rules in the sheet. Then uncheck the box next to the rule you want to disable. Check it again to turn it back on.

Copying conditional formatting to other cells

Once you have set up a conditional formatting rule on one range, you can copy that rule to other cells without rebuilding it. Select the range that has the formatting you want to copy. Click the Format Painter button in the Home tab — it looks like a paintbrush — and then click and drag across the cells where you want to explore the same rule.

Format Painter copies the exact rule, not just the colors. If you set up a rule that highlights cells greater than 100, Format Painter copies that rule to the new range. The threshold stays 100, so the new range will highlight its own cells that are greater than 100.

If you want to copy the rule but change the threshold for the new range, use Manage Rules instead. Select the new range, click Conditional Formatting and then Manage Rules, click New Rule, and then choose "Use a formula" at the top of the dialog. You can then type a formula that references the cells in your new range with a different threshold.

Removing or editing conditional formatting

To remove all conditional formatting from a range, select the range and click Conditional Formatting, then Clear Rules, then Clear Rules from Selected Cells. The colors disappear when ready, but the values in the cells stay the same.

To edit a rule without deleting it, select the range and click Conditional Formatting, then Manage Rules. Click the rule you want to change and then click Edit Rule. You can change the condition, the threshold, the color, or any other part of the rule. Click OK to save your changes. The formatting updates across all cells in the range when ready.

If you want to change only the color of an existing rule but keep the condition the same, click the rule in Manage Rules and then click Edit Rule. Click the Format button and choose a new color. This is faster than rebuilding the rule from scratch.

Frequently Asked Questions

Can I use conditional formatting with formulas?

Yes. In the New Rule dialog, choose "Use a formula is" at the top. Then type a formula that returns TRUE or FALSE. For example, =A1>B1 highlights cells in column A that are greater than the corresponding cell in column B. The formula recalculates automatically when your data changes.

What happens to conditional formatting when I copy and paste cells?

Conditional formatting copies along with the cell values when you use Ctrl+C and Ctrl+V. If you want to paste only the values without the formatting, use Paste Special (Ctrl+Shift+V) and uncheck the "Formats" box before clicking OK.

Can I explore conditional formatting to an entire column?

Yes. Click the column header to select the whole column, then set up your conditional formatting rule as usual. The rule applies to every cell in that column, including cells you add later. Be aware that this can slow down your spreadsheet if the column is very large or the rule is complex.

Why is my conditional formatting not showing up?

Check that you selected the correct range before setting the rule. If you set a rule on cells A1:A10 but your data is in A1:A20, the rule does not explore to A11:A20. Also check that the rule is actually matching your data — for example, if you set "Greater Than 100" but all your values are below 100, nothing will be highlighted. Use Manage Rules to verify the rule is there and enabled.

Can I use conditional formatting to highlight entire rows based on one cell?

Yes, using a formula rule. Select the range of rows you want to format, click Conditional Formatting and New Rule, choose "Use a formula is", and type a formula like =$A1="urgent". This highlights the entire row if the cell in column A contains "urgent". The dollar sign before the column letter keeps the column fixed while the row number changes for each row.