SharePoint number column formatting

Make a number column say something: green under budget and red over, a currency symbol in front, a bar or a dashboard tile instead of a bare figure.

Number columns are the easiest columns in SharePoint or Microsoft Lists to format and the ones most people leave alone. By default a Number (or Currency) column shows the raw value — 1450 — with nothing to say whether that's good, bad, or over a limit. A short piece of column formatting JSON fixes that with a comparison and two colors, and the same JSON can add a symbol, a label, or a whole different shape.

The generator has several number templates, each for a different question: Inline Number colors a value against one threshold, Budget Threshold adds a currency symbol and turns red over a limit, KPI vs Target shows the delta from a target with ▲ and ▼, Heat Cell fills the cell green → amber → red by size, and Metric Card presents it as a dashboard tile. This page uses the budget one as the worked example because it combines the two most-asked-for things — a symbol and a limit.

Before and after

What the SharePoint number formatting JSON looks like

The generator produces JSON like the example below. It's a single element whose text and colors are all expressions over the field's value:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "inlineEditField": "[$Cost]",
  "style": {
    "display": "inline-flex",
    "align-items": "center",
    "padding": "2px 9px",
    "border-radius": "4px",
    "font-size": "12px",
    "font-weight": "700",
    "cursor": "pointer",
    "font-family": "Consolas, Menlo, monospace",
    "color": "=if(toString([$Cost]) == '', '#605e5c', if(Number([$Cost]) > 1000, '#a4262c', '#107c10'))",
    "background-color": "=if(toString([$Cost]) == '', '#f3f2f1', if(Number([$Cost]) > 1000, '#fde7e9', '#dff6dd'))"
  },
  "txtContent": "=if(toString([$Cost]) == '', '—', if(Number([$Cost]) == 0, '$0', '$' + toString([$Cost])))"
}

Three expressions, one pattern. Number([$Cost]) > 1000 is the test; color and background-color each pick a value from it, giving dark-red-on-pink over the limit and green-on-mint under it — with a neutral grey first when toString([$Cost]) is empty, so a blank cell doesn't masquerade as a $0 line item. txtContent builds the label by joining the symbol to toString([$Cost]). Nothing here changes the data — the column still holds a plain number, and sorting, totals, and flows are untouched.

The threshold version, Inline Number, is the same idea without the symbol. It uses eight-digit colors like #d134381a — a normal hex color with an alpha value on the end — to get a faint tint of the same red as the text, which is an easy way to make a cell look highlighted without a hard block of color.

Three things to know about numbers in expressions

• Empty is zero. A blank number cell evaluates as 0, so it takes whatever look zero gets. If zero and blank should differ for your list, set a default value or make the column required.
• The value is raw. Expressions see the stored number, so 1450 is shown as 1450. There's no thousands-separator function in the formatting language — its toLocaleString() is for dates only.
• Limits can be other columns. Replace a fixed 1000 with [$Budget] and the test compares two fields — as long as Budget is in the same view, which is a hard rule for any cross-column reference.

How to apply it

1. Open the generator and pick the Budget Threshold template (or use the button above).
2. Enter your column's internal field name — see the note below.
3. Set the Limit and the Symbol (or leave the symbol empty for a plain number).
4. Click Copy.
5. In SharePoint: Column settings → Format this column → Advanced mode.
6. Delete what's in the box, paste, click Preview, then Save.

Common questions