Spreadsheets

Budget progress bar with SPARKLINE in Google Sheets

Draw a budget progress bar inside a cell with the Google Sheets SPARKLINE function, charttype bar, a max set to the budget and a colour that turns red when over.

Editorial illustration for Budget progress bar with SPARKLINE in Google Sheets

In Google Sheets, =SPARKLINE(C2,{"charttype","bar";"max",B2}) draws a bar in the cell whose length shows spent (C2) against the budget (B2). Add a colour option that depends on an IF and the bar turns red when you go over. SPARKLINE is a Google Sheets function; Excel does not have this exact formula.

What is the simplest progress bar?

Put the budget in column B and the spent amount in column C. In D2:

=SPARKLINE(C2,{"charttype","bar";"max",B2})

The first argument is the data, here a single number. The second argument is a set of options in curly brackets: each pair is a name and a value, with a comma between them, and a semicolon starts the next pair. The "max" option sets the value at which the bar is full. With max equal to the budget, a bar at half length means half the budget is spent.

Widen column D so the bar has room. The bar scales to the cell width, so a wider cell shows a more detailed bar. Row height matters too: a taller row makes a thicker bar.

How do I change the colour when I am over budget?

Add the colour option and let IF choose it:

=SPARKLINE(C2,{"charttype","bar";"max",B2;"color1",IF(C2>B2,"red","green")})

The bar can never grow beyond max, so an overspend shows as a full red bar. For three colours, with an amber warning from 90 percent:

=SPARKLINE(C2,{"charttype","bar";"max",B2;"color1",IF(C2>B2,"red",IF(C2>=0.9*B2,"orange","green"))})

Colour names such as red, orange and green work; you can also use hex codes in quotes, for example "#2e7d32".

How do I show a number next to the bar?

Add a percentage in column E: =IF(B2=0,"",C2/B2), formatted as a percentage. Because the bar stops at full, this is where you read how far over you are: 115 percent means 15 percent above budget. Hypothetical rows:

CategoryBudgetSpent% usedBar colour
Groceries₹12,000₹8,40070%Green
Fuel₹3,000₹2,85095%Orange
Dining out₹4,000₹4,600115%Red

Check: 8,400 divided by 12,000 is 0.70. 2,850 divided by 3,000 is 0.95, inside the 90 to 100 band. 4,600 divided by 4,000 is 1.15, so it is over and the bar is red and full.

Can I show spent and remaining in one bar?

Yes. Give SPARKLINE two values in curly brackets, spent and remaining, and it draws them as two coloured segments:

=SPARKLINE({C2,MAX(0,B2-C2)},{"charttype","bar";"color1","orange";"color2","lightgray"})

The first segment is what you have spent; the second is what is left, never negative thanks to MAX. The bar is scaled to the sum of the two values, so bars in different rows will have different lengths when budgets differ. If you want every row to be comparable, keep the single-value version with max set to the budget.

Do colours alone work for everyone?

Not always. Red and green look similar to people with some kinds of colour blindness, and a printed copy may be black and white. Keep the percentage column beside the bar and, if you like, add the flag text from an IF formula such as =IF(C2>B2,"Over",""). The bar gives the quick picture and the words and numbers give the detail, so nothing depends on telling two colours apart.

Making it fill down

  1. Write the formula in the first row only.
  2. Drag the fill handle down, or double-click it.
  3. Add a header such as Progress.
  4. Check the first and last row; the references should move with the row.

Can I show the same bar for a savings goal?

Yes, the idea is identical with the roles swapped. Put the target in B, the amount saved so far in C, and use the same formula with a single colour, since being over a savings target is good news. A bar that creeps towards full over the months is a simple way to see progress without a chart.

What does not work

SPARKLINE draws only inside Google Sheets. If you download the file as Excel, the cell shows the formula result as plain text or blank, so share the sheet itself rather than an export. If the bar looks empty, check that the spent cell is a number and not text.

For a progress view without building formulas, SpendixAI has budgets on Pro.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports