Excel Conditional Formatting Number Formats Explained

How-to basics · Updated November 19, 2026

Yes—Excel conditional formatting can apply a number format when a rule is true, without changing the cell’s underlying value. In Microsoft 365 for Windows, create or edit a rule, choose Format, then set the display style on the Number tab.

What conditional number formatting does

A number format controls how Excel displays a stored value: for example, as a date, percentage, currency amount, or rounded number. Conditional formatting lets a rule switch that display when a condition is met. A cell could show a whole-number currency style when its value exceeds a threshold, and return to its ordinary display format when it does not.

The format changes what you see, not what Excel stores or calculates. If a cell contains 0.25 and a conditional rule gives it a percentage format, it displays as 25%, but formulas still use the numeric value 0.25. Conditional formatting does not convert numbers to text or round the stored value. This distinction matters for sorting, arithmetic, charts, and later calculations.

Number formatting is only one part of a conditional format. A rule can also apply a font, fill, border, or combinations of those styles. The rule’s condition determines whether its formatting is used; the number format determines how a qualifying value is shown.

How the rule and number format work together

Excel evaluates a conditional formatting rule against the cell or range it applies to. When the condition is true, Excel applies the formatting specified by that rule. When the condition is false, the conditional format no longer applies, and the cell’s ordinary formatting is visible again. If the value changes, Excel reevaluates the rule.

For example, a sales report might contain decimal values such as 0.18 and 0.07 in a discount column. A rule could format values at or above 0.1 as percentages with one decimal place. Qualifying values would display as 18.0% or 10.0%, while other cells would keep their normal format. The underlying decimals remain available to formulas.

Rule-based formatting and regular cell formatting are separate. You can set the ordinary number format first, then add a conditional format for a special case. If the rule stops applying, the ordinary format is still there. This is often more reliable than trying to make one rule handle every possible display state.

Apply a number format to a conditional formatting rule

For a straightforward threshold, use a rule that formats cells based on their values. These steps apply to Excel for Microsoft 365 on Windows:

  1. Select the cells to format, such as the values in B2:B100.
  2. Choose Home > Conditional Formatting > Highlight Cells Rules, then select a suitable comparison, such as Greater Than.
  3. Enter the threshold in the dialog. Choose a preset format or select Custom Format to open the Format Cells dialog.
  4. Open the Number tab and choose a category such as Number, Currency, Percentage, or Date. Set the options you need, then select OK.
  5. Confirm the rule, then check both a value that meets the condition and one that does not.

To edit an existing rule, select a cell in its range and go to Home > Conditional Formatting > Manage Rules. Choose the rule, select Edit Rule, and use its format controls. In the Manage Rules window, inspect the Show formatting rules for setting; it can show rules for the current selection, the worksheet, or other scopes. A rule may exist but appear to be missing if the wrong scope is selected.

On Excel for Mac, the corresponding commands are also on the Home tab under Conditional Formatting, although dialog layout can differ by release. Excel for the web supports many common conditional formatting tasks, but some rule-editing controls can differ from desktop Excel. If a particular number-format option is unavailable in the browser, open the workbook in the desktop application.

Examples of useful conditional number formats

Conditional number formats are useful when the same range contains values that readers should interpret differently, or when a threshold deserves a more compact display. The rule should communicate a real distinction rather than simply decorate the worksheet.

  • Show percentages only above a cutoff: If a column stores rates as decimals, a rule can display values at or above a chosen level as percentages. Make sure the values really are decimals such as 0.2; formatting the number 20 as a percentage would display 2,000%.
  • Show whole dollars for large amounts: A threshold rule can display larger values as currency with zero decimal places while smaller values retain cents. This changes visible precision only; it does not round the values used in calculations.
  • Emphasize negative balances: A rule for values below zero can use a number format that displays negatives in parentheses, as well as a red font. This is distinct from merely coloring the cell: the rule can set both the numeric presentation and the text color.
  • Display dates differently after a deadline: A date is stored as a serial number. A conditional rule can apply a date display such as a short month-and-day style to overdue entries while other dates keep the range’s ordinary format.

For a formula-based rule, with the values to test in B2:B20 and the rule applied starting at B2, the formula =B2<0 is true for negative values. In Home > Conditional Formatting > New Rule, choose the formula-based rule option, enter the formula, then select Format and set the Number tab options. Because the reference is relative, Excel adjusts it for each cell in the applied range. If the formula is meant to test one fixed cell for every row, use an absolute reference such as =$B$2<0 instead.

Choose a number category that fits the data

The Number tab offers categories designed for different kinds of values. Choosing the right category prevents misleading displays:

  • Number controls decimal places and digit grouping. It is appropriate for counts and general measurements, but not usually for identifiers such as product codes.
  • Currency and Accounting display a currency symbol. Accounting formatting aligns symbols and decimal points in a column, while Currency places the symbol close to the amount.
  • Percentage displays a value multiplied by 100 with a percent sign. A stored value of 0.05 displays as 5%, not 0.05%.
  • Date and Time display date or time serial values in readable forms. A date display does not turn an arbitrary number into a meaningful date.
  • Text is generally not a substitute for a numeric display format. Text values do not behave like numbers in many calculations, sorting operations, and charts.

Also consider consistency. If a column normally shows two decimal places, a conditional rule that switches a few cells to zero decimal places may be deliberate, but it can also make comparisons harder. Use a different display only when it helps readers recognize a meaningful category or threshold.

Rule order, overlapping rules, and format conflicts

A cell can be covered by more than one conditional formatting rule. In Manage Rules, Excel shows the rules’ priority order. If multiple applicable rules set the same formatting element, the higher-priority rule can determine that element. Different formatting elements may be combined when rules do not conflict—for example, one rule may set a number format while another sets a fill color.

The Stop If True option can prevent lower-priority rules from being evaluated for a cell when a higher-priority rule is true. Use it only when that behavior is intended. For instance, if a high-priority rule applies a special number format to overdue values, stopping lower-priority rules may prevent a broader rule from adding another style. Test overlapping cases rather than assuming every true rule will contribute all its formatting.

When results are unexpected, select a cell in the affected range and inspect Home > Conditional Formatting > Manage Rules. Check the rule order, the applied range, each rule’s condition, and whether another rule also sets a number format. A rule can be correct but apply to the wrong cells, or its number format can be obscured by a higher-priority rule.

Common problems and how to fix them

  • The value looks rounded but calculations are unchanged: This is normal. A number format changes display precision, not the stored value. Use a rounding formula only if the calculated value itself must change, and consider the effect on later calculations.
  • A percentage looks 100 times too large: Check the stored value. Percentage formatting multiplies the displayed value by 100, so 1 displays as 100% and 0.01 displays as 1%.
  • The conditional format never appears: Verify that the condition is met, the rule applies to the selected cell, and the rule is enabled in the correct scope. Check whether the values are numbers or text; a number stored as text may not behave as expected in a numeric comparison.
  • Only some cells change: Inspect the rule’s Applies to range in Manage Rules. For formula-based rules, confirm that relative and absolute references match the top-left cell of that range.
  • The format appears to disappear: The condition may no longer be true, or another rule may take priority. The cell’s ordinary number format is visible whenever the conditional number format is not active.
  • A date displays as a serial number: Apply a date category to the ordinary cell format or to the rule’s Number tab settings. The value may be a valid date serial even though the current number format displays it as a number.

Conditional formatting versus other approaches

Use conditional formatting when a value should remain numeric and its display should respond automatically to a rule. It is especially useful for reports that update as values change, because the same rule can continue to apply to new qualifying values within its range.

Use ordinary number formatting when every cell in a range should display the same way, regardless of its value. This is simpler to maintain and makes the worksheet’s baseline presentation clear. Conditional formatting is an additional layer, not a replacement for choosing a sensible default format.

A helper column can be preferable when the classification itself needs to be visible, filtered, counted, or used in later calculations. For example, a separate Status column could contain labels such as “Overdue” and “Current,” while conditional formatting highlights the overdue rows. A formula using the TEXT function can produce formatted text for a report, but its result is text rather than a number; that can interfere with arithmetic and numeric sorting. Avoid using TEXT merely to make a cell look different when a number format can do the job.

Frequently Asked Questions

Can conditional formatting change a cell’s number format?
Yes. Edit or create a rule, choose Format, and set the category and display options on the Number tab. This affects how the qualifying cell appears, not its stored value.

Can I show a value as a percentage only when it meets a condition?
Yes. Create a rule for the desired threshold, then choose Percentage on the rule’s Number tab. Confirm that the stored values are decimals in the expected scale; 0.2 displays as 20%.

Does conditional number formatting change the value used in formulas?
No. It changes presentation only. A value displayed with no decimal places may still contain and calculate with decimal digits that are not visible.

Can a rule apply a number format and a color at the same time?
Yes. In the rule’s Format Cells dialog, set the number format and, if desired, choose a font or fill on the other tabs. Be aware that another higher-priority rule may set a conflicting format element.

Why does the format disappear when I change a cell?
The condition may have become false, so Excel stops applying the conditional format and shows the cell’s ordinary formatting. Check the rule’s condition and the cell’s base number format if the new appearance is unexpected.

Practical takeaway

Set a sensible ordinary format for the range, then add conditional formatting for the specific cases that deserve a different display. Choose the rule condition carefully, set the number style in the rule’s Format dialog, and verify the result with values on both sides of the threshold. This keeps the data numeric and makes the worksheet’s changing presentation easier to understand and maintain.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.