Do Google Sheets Charts Update Automatically From Source Data?

Google's chart documentation explains how to select chart data and change its range. The example below defines the inputs, formula output, chart range, and expected totals so an analyst can reproduce the setup.

Define a small monthly revenue example

Create three tabs with the exact names P&L, Charts, and Assumptions. Use a spreadsheet locale with commas as formula separators, or adapt separators to your locale.

In P&L!A1:C4, enter the following table. Dates must be actual date values; use the listed DATE formulas. Revenue and EBITDA must be numbers, not text such as "$4.2M". Format columns B and C as currency after entry.

Enter Month, Revenue, and EBITDA as the headers in row 1. Keep this source table free of subtotal rows and notes in columns A:C. There should be one row per month.

In Assumptions!A3:B4, enter:

The formulas in this guide sum revenue in column B and apply date criteria to column A. Column C contains EBITDA and is not part of the revenue total.

Make Google Sheets charts update from a formula-driven table

Start with an empty Charts!A1:B1000 range so the formula can expand without overwriting existing values.

In Charts!A1, enter:

=QUERY('P&L'!A1:B1000,"select A, B where A is not null order by A",1)

The final argument is 1 because the source range includes one header row. The formula returns the Month and Revenue columns in date order, including their headings. Do not separately type headings into Charts A1 or B1. Google documents the QUERY header argument and column types; mixed date/text or number/text columns can produce missing values, so keep input types consistent.

Format Charts!A2:A1000 as dates and Charts!B2:B1000 as currency. Your output should show the three monthly rows with revenue of $4.2M, $4.4M, and $4.6M.

1. Select Charts!A1:B1000 and choose Insert > Chart.
2. Open the Chart editor's Setup panel. Confirm the data range is Charts!A1:B1000.
3. Choose a line chart or column chart. Use column A as the X-axis and column B as the Revenue series. Treat row 1 as headers.
4. If an existing chart uses a different range, select the chart, open its three-dot menu, choose Edit chart, and change the data range in Setup.
5. Check that the chart shows three dates and the corresponding revenue values. Blanks beyond the query output are not additional reported months. If the chart displays blank categories, inspect the axis, series, and source types rather than adding dummy zeros.

This is an explicit 1,000-row setup, not an unlimited range. Increase both the source QUERY range and chart range before the data grows beyond row 1,000. Keep unrelated content outside the reserved Charts output range.

Verify that a changed source value reaches the chart

Put Source quarterly revenue in Charts!D1 and this formula in E1:

=SUMIFS('P&L'!$B$2:$B$1000,'P&L'!$A$2:$A$1000,">="&Assumptions!$B$3,'P&L'!$A$2:$A$1000,"<="&Assumptions!$B$4)

Put Chart-output quarterly revenue in D2 and this formula in E2:

=SUMIFS($B$2:$B$1000,$A$2:$A$1000,">="&Assumptions!$B$3,$A$2:$A$1000,"<="&Assumptions!$B$4)

In D3 enter Difference; in E3 enter =E1-E2. Both totals should be $13.2M, and the difference should be zero. These are inputs for this illustrative example, not a claim that a real company's model has been validated.

Now change P&L!B3 from 4400000 to 4500000. The February point must become $4.5M. Both quarterly totals must become $13.3M, and E3 must remain zero. A correct total alone does not prove the chart range is correct: also inspect the February point and chart-series range.

Google's SUMIFS documentation places the values being summed first, then each criterion range and criterion. Here revenue belongs in the sum range and dates belong in the date criterion ranges.

Verify that a newly added month appears

In P&L!A5:C5, enter =DATE(2026,4,30), 4800000, and 900000. The query output should now include April, and the chart range already includes that row. Verify that the fourth plotted revenue point is $4.8M.

For an all-month check, put All chart revenue in D4 and =SUM(B2:B1000) in E4. After the February change and April addition, E4 must be $18.1M. The quarterly formulas in E1 and E2 still show $13.3M because Assumptions B4 remains March 31. If you change B4 to =DATE(2026,4,30), both date-filtered totals become $18.1M.

A fixed range such as 'P&L'!A1:B4 does not include row 5 unless the chart's assigned range is extended. The point is to verify what is actually assigned after your update, rather than assume a refresh button or an appended row has expanded it.

Choose the right chart-source pattern

Use a clean output table when a P&L contains notes, subtotal lines, or scenario sections. The QUERY in this example selects nonblank dates; it does not replace source hygiene or accounting controls. A duplicate month or incorrectly typed date can still create a misleading chart.

If this feeds a board pack, confirm the reporting period, currency, units, and month completeness against the approved source model. Keep the source and chart-output controls visible to the next analyst who inherits the workbook. The same control approach is useful alongside an AI formula review.

Use assistance while keeping the controls visible

ModelMonkey can help read and search spreadsheet ranges, analyze tables, and work with formulas, formatting, and charts. Select the source cells in Google Sheets, open the ModelMonkey sidebar, and ask it to inspect missing months or duplicate labels. When its workflow presents a preview, review proposed changes before proceeding.

Do not assume it connects to every source system or automatically refreshes arbitrary external data. Keep responsibility for the reporting period, source values, and the $13.3M or $18.1M checks with the analyst reviewing the report. Start with a selected range and one specific check rather than an open-ended request to make a board pack correct.