A chart that updates itself: line and bar charts in Excel and Google Sheets from a log

The chart you drew in March is wrong in September because it points at rows 5–40 and the data now ends at row 200. Point the chart at a summary table instead — twelve rows of SUMIFS that never move — and it can't go stale. Here is that pattern, and the handful of chart settings that make the result readable.

Contents

Why charts go stale

A chart’s data range is an address: Sheet1!$B$5:$B$40. Add row 41 and the chart doesn’t know. Excel offers two cures — a Table (Ctrl+T) whose range grows, or a dynamic named range with OFFSET — and neither survives a trip to Google Sheets or Numbers intact. The portable cure is older and simpler: don’t chart the log. Chart a summary.

Step 1: a summary table that never moves

Twelve rows, one per month, on their own sheet. Column A holds the first of each month; columns B and C sum the log with two date bounds:

Monthly sheet, row 5 = January

A5:  =DATE($B$2, 1, 1)                                       B2 holds the year
A6:  =EDATE(A5, 1)                                           fill down to row 16

B5:  =SUMIFS(Ledger!$G$6:$G$405, Ledger!$A$6:$A$405, ">="&A5, Ledger!$A$6:$A$405, "<="&EOMONTH(A5,0))
C5:  =SUMIFS(Ledger!$H$6:$H$405, Ledger!$A$6:$A$405, ">="&A5, Ledger!$A$6:$A$405, "<="&EOMONTH(A5,0))

The log can grow to row 405 (or wherever you bound it) and the twelve summary rows don’t move. The chart points at A5:C16 forever. Change the year in B2 and every row, and the chart, re-sums.

Step 2: the chart

Excel: select A4:C16 (including the header row) → Insert → Column or Line. Excel reads the first row as series names and the first column as categories because the first column is dates.

Google Sheets: select A4:C16 → Insert → Chart. It usually guesses right; if it stacks the series or treats the dates as a series, open the chart editor → Setup → tick Use row 4 as headers and Use column A as labels.

For income vs expenses: clustered columns. For a running total (net worth, cumulative net): a line. Don’t put both on one chart with two axes unless the two scales genuinely differ — two-axis charts are how people mislead themselves.

Step 3: the months that haven’t happened yet

In September, October to December are zero, and a chart shows twelve columns with three empty ones — or worse, a line that dives to zero. Two fixes:

blank instead of zero for future months

B5:  =IF(A5>TODAY(), NA(), SUMIFS(…))          Excel and Sheets skip #N/A in charts
   or, if the table is also read by formulas that would choke on #N/A:
   keep the SUMIFS as it is, and set the chart's "show empty cells as: gaps"

NA() is the classic: charts ignore #N/A points, so the line stops at the current month. Wrap any downstream formula in IFERROR(…,0) — or, cleaner, keep a separate column for the chart with NA() and leave the real totals alone. That’s what we do: the numbers stay numbers, the chart column is the only one allowed to be #N/A.

Step 4: settings that keep it honest

Axis starts at zero. Excel and Sheets sometimes pick a minimum above zero for a line chart, which turns a 3 % change into a cliff. Format axis → Minimum: 0.
Fixed maximum when comparing charts. Two charts side by side with auto scales can’t be compared. Set the same maximum on both.
Series colours by meaning. Income one colour, expenses another, the same on every chart in the file. Our workbooks use blue for money in and the muted red for money out.
No 3-D, no gradients, gridlines light. They cost reading time and add nothing.
Title says what and when. “Income vs expenses, 2026” — not “Chart 1”.

A chart with no chart: data bars

For a twelve-row summary, a conditional-format data bar on the column often does the job and never needs a chart object: Home → Conditional Formatting → Data Bars (Excel), or a custom formula rule in Sheets. Set the bar’s minimum to 0 and maximum to a fixed number if the values are percentages; otherwise let it be the column’s max. Our budget and expense sheets use bars in the tables and a real chart only on the Year view.

What travels and what doesn’t

A chart built from a plain range on a summary sheet opens in Excel, Google Sheets, Numbers and LibreOffice. Chart styles don’t: expect colours and fonts to reset on import, which is another reason to keep the chart simple. Charts referencing Tables or OFFSET names may arrive as static pictures or lose their link. Sparklines (Excel’s in-cell mini-charts) don’t exist in Numbers and are imported as blanks; Sheets has a SPARKLINE() function that Excel doesn’t. If it must open everywhere, it’s a summary table plus a plain chart plus data bars.