Excel — Per-Cell Display: Integers vs Decimals

This post covers how to display one cell as an integer and another cell with 2 decimal places in Excel — within the same column — using Conditional Formatting + the MOD function. The technique works in Excel 2016, 2019, 2021, and Microsoft 365.

[01] By Default, Integers and Decimals Share the Same Decimal Setting

When you extract numeric data from a database, a CSV file, or another spreadsheet, a single column often mixes whole numbers (21, 45) and real numbers (12.34, 7.5). Excel’s default cell format applies the same decimal-place rule to every cell in the selection, which forces you to choose between two unattractive options:

• Display every value as an integer → real numbers lose their fractional part
• Display every value with 2 decimal places → integers become 21.00, 45.00 (visually noisy)

What we actually want is a per-cell, value-aware format: integers stay as integers, real numbers show two decimal places.

• The extracted data contains both integers and decimals.

Figure 1. Source data with mixed integers and decimals in the same column.

• Format → Cell Format → Category (Number) → Decimal places (change to 2)

Figure 2. Setting Number → Decimal places: 2 in the Format Cells dialog (Ctrl + 1). This applies to every cell in the selection — there is no built-in option to skip integers.

• With this approach, the format applies to every cell, so even integers display as 21.00.

Figure 3. After applying 2-decimal formatting, integers also show .00, which is visually noisy in reports and dashboards.

• We need a way to keep integer values displayed without decimals while showing real numbers up to 2 decimal places.

[02] Conditional Formatting + MOD Function

The fix is to let Excel decide per-cell whether the value is an integer or a real number, and apply the 2-decimal format only to real numbers. Conditional Formatting is the standard tool for per-cell rules, and the MOD function is the cleanest way to test “does this value have a fractional part?”.

The check is simple:

• MOD(value, 1) returns the fractional part of value (e.g. MOD(12.34, 1) = 0.34)
• For integers, MOD(value, 1) = 0
• So MOD(value, 1) <> 0 is true only when the value has a decimal part

Apply the 2-decimal format conditionally based on this test.

• Select all target cells → Conditional Formatting → New Rule

Figure 4. Home tab → Conditional Formatting → New Rule.

• Use a formula to determine which cells to format
• Formula: mod(A1,1)<>0 → choose format

Figure 5. Pick “Use a formula to determine which cells to format”, then enter =MOD(A1,1)<>0. Use the top-left cell of your selection as the formula anchor — Excel automatically adjusts the reference for every other cell in the range.

• Format: Number → Decimal places 2 → confirm all dialogs

Figure 6. Inside the rule’s Format… dialog, set Category = Number and Decimal places = 2. This format only fires when the MOD condition is true.

• Result

Figure 7. After the rule is applied: integers stay as integers, real numbers display with two decimal places — all within the same column.

• If the alignment between integers and decimals is distracting, format the integers as Number as well.

Figure 8. Optional polish: apply a base Number format (0 decimal places) to the entire column so digit-grouping and right-alignment stay consistent. The conditional rule still overrides only the decimal cells.

• Result

Figure 9. Final result — visually consistent column with integers and decimals displayed in their natural form.

[03] Notes & Edge Cases

A few things to keep in mind when applying this pattern in production spreadsheets:

Topic	Detail
Stored value is unchanged	The MOD rule only affects display. The underlying value 7.5 is still 7.5 in formulas — it just appears as 7.50 on screen.
Anchor cell	The A1 in =MOD(A1,1)<>0 must point to the top-left cell of your selection, not a fixed cell. Excel rewrites the reference relatively for every other cell.
Negative numbers	MOD in Excel returns a value with the same sign as the divisor, so MOD(-3.5, 1) returns 0.5 (non-zero) — negative decimals are handled correctly.
Very small fractions	Floating-point values like 7.0000000001 register as non-integer. If your data has such noise, wrap the test in ROUND: =MOD(ROUND(A1,2),1)<>0.
Performance	Conditional Formatting rules re-evaluate on every sheet change. For ranges over ~50,000 rows, prefer a helper column with the formatted text instead.

This pattern generalizes — swap MOD(A1,1)<>0 for any boolean test (date in the past, value above threshold, even/odd, etc.) to get per-cell, value-aware formatting in any column.