Great question! This is such a common scenario when working with spreadsheets and charts, and the short answer is yes—you can absolutely configure things so your chart automatically adds a new data series whenever you add a column to your source table. Let’s walk through how to do this in the two most widely used tools: Excel and Google Sheets.

Excel 实现方案 #

方法1：使用Excel表格（List Object）——最简单的方式 #

This is the most straightforward approach, built right into Excel:

• First, convert your source data into an Excel Table: select any cell in your data range, press Ctrl+T (or go to the Insert tab and click Table). Check the "My table has headers" box if your data has column titles, then click OK.
• Create your chart using this table as the data source.
• Now, whenever you add a new column directly to the right of the table, Excel will automatically expand the table to include the new column—and your chart will instantly pick up the new data as a series. If you add the column elsewhere, just drag the small blue handle at the bottom-right corner of the table to include the new column, and the chart will update.

方法2：使用动态数据范围（适合不想用表格的场景） #

If you prefer not to use an Excel Table, you can define a dynamic named range that automatically expands to include new columns:

1. Go to the Formulas tab, click Define Name.
2. Name your range something like DynamicChartData, then enter this formula (adjust the sheet name and starting cell to match your data):

   =Sheet1!$A$1:INDEX(Sheet1!$XFD:$XFD,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
   

   This uses COUNTA to count the number of rows with data and columns with headers, then INDEX to define the end of the range. Unlike the old OFFSET function, this is non-volatile (won’t slow down your spreadsheet).
3. When creating your chart, select this named range as the data source. Now, every time you add a new column with data/headers, the range will expand, and your chart will add the new series automatically.

Google Sheets 实现方案 #

方法1：使用QUERY函数动态获取数据 #

Google Sheets makes this easy with the QUERY function:

• In a blank area of your sheet, enter a formula like this (adjust the sheet name and range to match):

  =QUERY(Sheet1!A:Z,"select * where A is not null",1)
  

  This pulls all rows where column A has data, and will automatically include any new columns you add within the A:Z range.
• Create your chart using this dynamic query result as the data source. When you add a new column to your original table, the query will update, and your chart will add the new series.

方法2：使用动态命名范围 #

Similar to Excel, you can define a dynamic range in Google Sheets:

1. Go to Data > Named ranges.
2. Name your range (e.g., DynamicChartRange), then enter this formula:

   =Sheet1!$A$1:INDIRECT("R"&COUNTA(Sheet1!$A:$A)&"C"&COUNTA(Sheet1!$1:$1),FALSE)
   

3. Use this named range as your chart’s data source. New columns will trigger COUNTA to update the column count, expanding the range and updating the chart.

关键注意事项 #

• Make sure your new columns have clear headers—this helps the chart automatically assign a meaningful name to the new data series.
• Avoid using fixed cell ranges (like A1:D10) for your chart source—these won’t expand automatically when you add columns.

内容的提问来源于stack exchange，提问作者Chase Harper