Dynamic Charts in Excel Using the OFFSET Function Explained

Charts & pivot tables · Updated November 19, 2026

Yes. In Excel for Microsoft 365 on Windows, you can make a chart expand as new rows are added by defining workbook names with OFFSET, then using those names for the chart’s category labels and values. For a list with headers in A1:B1 and uninterrupted data below, the key is to make both named ranges use the same row count.

What OFFSET Does for a Chart

OFFSET returns a reference that starts at a specified cell and extends by a chosen number of rows and columns. A chart normally points to a fixed range such as A1:B6; when you add a record in row 7, that fixed reference may not include it. A defined name based on OFFSET can instead calculate the height of the source range from the number of filled cells.

OFFSET does not make a chart dynamic on its own. The chart needs to use a defined name that contains the OFFSET formula. As Excel recalculates the name after the source data changes, the chart reads the updated reference and plots the additional point. The chart’s physical size and position on the worksheet do not change; only its source range does.

Prepare the Source Data

Start with a simple, consistent layout. In this example, the worksheet is named Data, column A contains months, and column B contains sales. The first row has headers, and data starts in row 2. Keep each record on one row and avoid blank rows within the list.

  • A1: Month; B1: Sales
  • A2:A6: month labels; B2:B6: corresponding sales values
  • New records are added directly below the last existing record.

Use real Excel dates in the month column if you want a date axis, and format them to display as month names if appropriate. Text labels also work, but they are treated as categories rather than dates. The count should be based on a column that is filled for every record, such as a date, ID, or required label column.

Create the Dynamic Names

Open Formulas > Name Manager > New. Create a name called ChartMonths, set Scope to Workbook, and enter the following in Refers to. This assumes headers in Data!A1:B1, contiguous records starting in row 2, and a nonblank label in column A for every record:

=OFFSET(Data!$A$2,0,0,MAX(1,COUNTA(Data!$A:$A)-1),1)

COUNTA counts nonempty cells in column A, including the header; subtracting 1 gives the number of data rows. MAX(1,…) prevents a zero-height reference if there are no data labels yet. With five month labels in A2:A6, COUNTA of column A is 6, so the name refers to A2:A6.

Create a second workbook-scoped name called ChartSales. With the same layout and one sales value in column B for every label in column A, enter:

=OFFSET(Data!$B$2,0,0,MAX(1,COUNTA(Data!$A:$A)-1),1)

Notice that the height is counted from column A for both names. That keeps the label and value ranges the same size, even if a sales cell is temporarily blank. If your sheet name contains spaces, enclose it in apostrophes in the reference, as in ‘Monthly Data’!$A$2. Use a workbook-level name so the chart can refer to it regardless of which sheet is active.

Build the Chart and Connect the Names

  1. Select the existing headers and data, such as A1:B6 on Data, and choose Insert > Recommended Charts or pick a chart type from the Insert tab. A line chart is suitable for a sequence of months; a column chart is useful for comparing values by category.
  2. Select the chart, then choose Chart Design > Select Data. In Legend Entries (Series), select the sales series and choose Edit. Set the series values to the ChartSales defined name. In Horizontal (Category) Axis Labels, choose Edit and set the labels to ChartMonths.
  3. Confirm the dialog boxes and check that each plotted value lines up with the correct label. If Excel requires workbook qualification, use the workbook name together with the defined name, as Excel’s chart dialog indicates.

Because chart-reference fields can display workbook-qualified names differently depending on the file name and chart context, it is often easiest to select the name from Excel’s available references or let the dialog qualify it. The important point is that the chart points to the defined names, not to a fixed address such as Data!$B$2:$B$6. Test the setup by entering one new label and value directly beneath the last record; the chart should include the extra point after Excel recalculates.

How the Example Changes When Rows Are Added

Suppose the original data has five months, from January to May, in rows 2 through 6. ChartMonths and ChartSales each refer to five cells. If June and its sales figure are entered in row 7, column A now has seven nonblank cells counting the header. The calculated height becomes six, so the two names extend through row 7 and the chart includes June.

OFFSET uses a starting reference, row and column movement, height, and width. Here the movement is zero rows and zero columns because each range starts at its first data cell. The width is one column. The changing part is the height, calculated from the number of labels. This is why the formula’s anchor cell and the count column must match the data layout.

Important Limits and Common Problems

The method works best with a clean, contiguous list. COUNTA counts filled cells, not the position of the last used row. If a label cell in the middle is blank, a later record can be omitted from the calculated range because the count is too small. Remove blank rows, fill required labels, or base the count on another column that is reliably populated. Also avoid notes or unrelated content farther down the counted column: those cells can make the calculated range longer than the actual data.

  • Unequal series and labels: If the two names use different counting rules, Excel may show a warning or plot categories incorrectly. Use the same anchor count for both when every record shares a row.
  • Wrong starting row: If the data starts in row 3, change the anchor to row 3 and adjust the header-count logic. Do not copy the example unchanged into a differently structured sheet.
  • Blank cells: A blank sales value may create a gap or a zero-like display depending on chart settings and the data. A blank label in the counting column can also affect the range height.
  • No data yet: MAX(1,…) leaves a one-cell reference in place rather than producing a zero-height range. That cell may be blank, so the chart can look empty or show an unwanted category until data is entered.
  • Chart does not update: Confirm that the chart refers to the defined names, that the names have Workbook scope, and that new rows are directly below the list. Check Excel’s calculation setting if formulas are not recalculating.

OFFSET is a volatile function: Excel recalculates it whenever a calculation occurs, even if its direct inputs have not changed. A couple of small chart ranges are unlikely to be a concern, but many volatile formulas in a large workbook can add calculation work. If performance becomes an issue, consider an Excel Table or a nonvolatile reference design.

OFFSET Names Compared with Excel Tables

An Excel Table is often the simpler choice for a regularly growing dataset. Select the data and choose Insert > Table, making sure My table has headers is checked. Charts built from table data generally expand as rows are added to the table, and structured references are easier to read than formulas based on whole-column counts. Tables also make it convenient to filter and format records.

OFFSET is useful when a workbook already relies on named ranges, when a chart must use a particular range design, or when you need a calculated reference without converting the source list into a table. It also makes the range logic explicit. However, the names require careful setup, and the chart’s range will only grow according to the counting rule you defined. If a new record is entered several rows below the list with gaps in between, the example formula is not a reliable way to find the final row.

Dynamic-array functions such as FILTER can create a changing output list for more advanced charting, but they solve a different problem: they return calculated arrays rather than simply sizing a reference to a contiguous source list. FILTER is available in Microsoft 365 and Excel 2021 or later. For a straightforward chart that expands as you append rows, a Table is usually easiest to maintain; OFFSET is a workable option when you specifically need a named reference.

Version and Platform Notes

The menu paths above apply to Excel for Microsoft 365 on Windows. Excel for Microsoft 365 on Mac includes defined names and chart data selection, though ribbon placement and dialog appearance can differ slightly. If you cannot find a command, use Excel’s search box to locate Name Manager or Select Data. Excel for the web can display many charts and workbook names, but creating or editing this particular named-range chart may be more limited than in the desktop app; use desktop Excel if the chart reference controls are unavailable.

OFFSET is a long-standing Excel worksheet function, not a Microsoft 365-only function. The specific formulas shown use commas as argument separators, which is standard in many English-language installations. Depending on regional settings, Excel may display a different list separator; use the separator required by your installation if the formula is rejected.

Frequently Asked Questions

Does an OFFSET chart update automatically when I add data?
Yes, provided the new record is entered in the next row of the contiguous list, the chart uses the defined names, and Excel recalculates the formulas. The plotted range expands; the chart object itself does not resize.

Why is my chart not including the newest row?
Check that the row is directly below the existing data, the count column is populated, and the chart uses ChartMonths and ChartSales rather than fixed cell addresses. Also verify that the names have Workbook scope and that there are no blank labels inside the list.

Can I use OFFSET when my data has blank rows?
The example is not designed for blank rows. COUNTA counts nonempty cells rather than measuring the last occupied row, so gaps can make the calculated range end too early. A Table or a formula designed specifically to locate the last row is generally more dependable for irregular data.

Is OFFSET better than using an Excel Table?
Not usually for a simple expanding list. Tables are easier to maintain and typically expand with appended records. OFFSET is useful when you need named ranges or want to preserve a workbook design built around them, but it requires more attention to its counting assumptions.

Can the chart show dates instead of text categories?
Yes. Store actual Excel dates in the label column and choose a chart type and axis formatting suited to dates. Text such as Jan or Feb can be used as category labels, but Excel will not treat those text entries as date values.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.