To make a chart update as your data changes, turn the source range into an Excel Table, then create the chart from that Table. The chart will include new rows added to the Table and reflect edits to existing values. For summaries that need grouping or totals, use a PivotChart based on a Table and refresh it when the source changes.
What You’ll Need
- Excel for Microsoft 365 on Windows, or a recent Excel version with Tables and charts.
- A worksheet with a clear header row and data arranged in columns, such as Date, Product, and Sales.
- At least one category column and one numeric column to plot. Remove blank rows or columns within the data before making the chart.
Step-by-Step
- Organize the source data. Put one field in each column and give every column a distinct header, such as Month, Region, and Revenue. Keep one record per row. Avoid merged cells, completely blank rows within the list, and extra notes inside the data area. For a line chart of monthly sales, for example, place the month names in column A and sales figures in column B, with headers in row 1. Make sure values intended as numbers are stored as numbers, not text.
- Convert the range to a Table. Click any cell in the data, then choose Insert > Table. Check that the selected range covers all the headers and records, tick My table has headers, and select OK. You can also use Ctrl+T on Windows or Command+T on Mac. Excel adds Table formatting and a Table Design tab when the Table is selected. The formatting can be changed later; the important part is that the data is now an Excel Table.
- Give the Table a useful name. Click inside it, open Table Design, and edit the name in the Table Name box. Use a short, descriptive name such as SalesData, with no spaces. Naming the Table is not required for a basic chart, but it makes the source easier to identify if you later build formulas, reports, or PivotTables from it.
- Create the chart from the Table. Select a cell in the Table, then choose Insert > Recommended Charts. Review the suggestions, choose a suitable chart, and select OK. For categories over time, a line chart is often useful; for comparing amounts across categories, consider a column or bar chart. You can also select a chart type directly from the Insert tab. Starting with a selected Table cell helps Excel use the Table as the chart source.
- Check the plotted series and labels. Confirm that category labels appear along the intended axis and numeric columns appear as data series. If Excel chose the wrong orientation, select the chart and go to Chart Design > Switch Row/Column. To inspect or adjust the ranges, choose Chart Design > Select Data. In the dialog, check the chart data range, the legend entries (series), and the horizontal category axis labels. Use this check before styling the chart so you do not format the wrong data.
- Test whether new rows are included. Click the first empty row directly below the Table and enter a new record in each relevant column. Excel usually expands the Table as you type or paste directly underneath it, and the chart should add the new category or point. If it does not, click in the Table and check whether the new row received the Table formatting and filter controls. Rows pasted several lines below the Table, with a blank row in between, may remain outside it.
- Use the Table’s resize command if needed. If a new record was not added to the Table, select the Table and choose Table Design > Resize Table. Extend the range to include the new row or columns, then select OK. Another option is to type directly in the row immediately below the Table and let Excel expand it. After resizing, confirm that the chart includes the added data and that the axis labels are correct.
- Check how edits and extra columns behave. Change an existing number in the Table and confirm that the chart updates. To add another data series, add a clearly headed numeric column inside the Table; then check Chart Design > Select Data if Excel does not include it automatically. Adding a new column well outside the Table will not make it part of the chart source. If a new category field is needed, add it to the Table and review the chart’s series and axis setup.
- Optional: make a summary chart with a PivotChart. If you need totals by month, product, or region, select a Table cell and choose Insert > PivotTable. Create the PivotTable, arrange fields in the Rows, Columns, and Values areas, then select a cell in it and choose Insert > PivotChart. A PivotChart is useful for interactive summaries, but it does not necessarily refresh just because the source values changed. After adding or changing source records, right-click the PivotTable and select Refresh, or use Data > Refresh All. Because the source is a Table, newly added Table rows can be included when you refresh.
- Save and verify the result. Save the workbook, add a test row if appropriate, and check the chart after saving. If other people will use the file, explain where to add records: directly below the Table, not in a separate range. For a PivotChart, tell them to refresh the PivotTable. These small checks help prevent a chart that looks current but is still using an old or incomplete source.
Common Mistakes
Creating the chart before converting the data to a Table: A chart made from an ordinary fixed range may not include rows added later. Convert the source to a Table and create the chart from it, or repair the source through Chart Design > Select Data.
Leaving gaps in the source list: A blank row can make it harder for Excel to recognize the complete data region, and it can create gaps in a chart. Keep the records contiguous and use a consistent category value rather than an empty row to separate sections.
Assuming every chart refreshes in the same way: A normal chart linked to a Table responds to edits and Table expansion. A PivotChart needs its PivotTable refreshed to recalculate its summary. If the PivotTable uses a source range that excludes new records, update its source or use a Table as the source before refreshing.
Using totals as ordinary records: A manually typed total row can be plotted as another category and distort the chart. Keep totals separate from the data records, or use the Table’s Total Row option when appropriate; check whether the chart is plotting it before relying on the result.
Tips
- Use one descriptive header per column and keep header names unique. Clear labels make chart legends and data selection easier to understand.
- Choose the chart type for the question: a line chart can show change over time, while a bar chart can make category comparisons easier. Too many series can make either chart difficult to read.
- Use Table filters to focus on selected records. A chart based on a filtered Table generally follows the visible records, but check the chart if hidden or filtered-out data must be included. Select the chart, open Chart Design > Select Data > Hidden and Empty Cells, and review the setting for showing data in hidden rows and columns.
- When regularly importing data, paste the new records directly below the Table or use Table Design > Resize Table to include them. Avoid pasting into an unrelated area and assuming the chart will discover it.
- Keep chart titles and axis titles meaningful. If the chart is shared, state units such as dollars, units sold, or percentages so a changing display remains interpretable.
Troubleshooting
The chart does not include a new row: First check whether the row is inside the Table by clicking it and looking for Table formatting or filter arrows in its header. If it is outside, add it directly beneath the Table or use Table Design > Resize Table. Then check Chart Design > Select Data to confirm the chart source includes the Table’s full data.
The chart changes when values are edited but not when records are added: The existing chart may be linked to a fixed range rather than the Table. Select the chart and inspect Chart Design > Select Data. If the source is an ordinary cell range, create a new chart by selecting a cell in the Table, or update the range to include the Table’s data. For a PivotChart, refresh the PivotTable and confirm that its source includes the added records.
A new series or category is missing: Make sure its column or row is inside the Table and has a header. Open Chart Design > Select Data and check whether the field is listed as a series or category label. Use Add to add a series when needed, then select the correct series name and values. If Excel treats numbers as text, convert the source values to numbers and check the chart again.
The chart shows blanks, zeros, or unexpected points: Inspect the source cells for blank strings, text values, errors, or inconsistent dates. In Chart Design > Select Data > Hidden and Empty Cells, choose how empty cells should appear, such as gaps or zeros. The best choice depends on what a blank means in your data; do not use zero if it would imply a real measured value.
The PivotChart still looks out of date: Refresh the PivotTable using right-click > Refresh or Data > Refresh All. If new records remain absent, select the PivotTable and check its data source. A Table source can expand as records are added, but the PivotTable still needs a refresh to recalculate.
Frequently Asked Questions
Do Excel charts update automatically when data changes?
A normal chart updates when values in its source cells change. If its source is an Excel Table, the chart can also include rows added to that Table. A PivotChart usually needs its PivotTable refreshed after source data changes.
Do I need a formula to make a chart dynamic?
No. For most growing lists, converting the source range to a Table is the simplest option and does not require a formula. Dynamic named ranges can be useful for special layouts, but they are more complicated to maintain.
Can a dynamic chart include new columns as well as rows?
It can when the new columns are added within the Table and Excel includes them in the chart source. Check Chart Design > Select Data if the new field does not appear. A column added outside the Table is not automatically part of its data.
Will filters change what the chart displays?
Charts based on filtered Table data commonly reflect the visible records. However, chart settings can allow data in hidden rows or columns to remain visible. Review Chart Design > Select Data > Hidden and Empty Cells if filtering does not produce the expected chart.
Does this work in Excel for Mac or Excel for the web?
Tables and charts are available in both. On Mac, use the ribbon’s Insert and Table Design commands; some ribbon labels or dialog layouts may differ slightly from Windows. Excel for the web supports Tables and charts, though some advanced chart controls may be more limited.
Related
- How to Lock and Protect an Excel Sheet in Microsoft 365
- Protect a Sheet in Excel From Viewing: What Actually Works
- How to Lock Cells in Excel to Prevent Editing on a Sheet
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Chart Types: How to Choose the Right Chart for DataThere is no single best Excel chart type: choose the one that makes your data’s main message easiest…
- How to Insert a Bar Graph in Excel: A Step-by-Step GuideTo insert a bar graph in Excel, select your category labels and values, then choose Insert > Insert…
- How to Rotate a Waterfall Chart in Excel: Axis OptionsTo change the way a waterfall chart reads in Excel, format its category axis: reverse the category order…
- Excel Pivot Table Months Not in Order? Fix the DatesIf Excel PivotTable months are out of order, first check whether the source column contains real dates or…
- Build a Gantt Chart in Google Sheets: Step-by-StepTo build a Gantt chart in Google Sheets, list each task with its start and finish dates, calculate…
- Bar Chart in Cell Excel: How to Make OneExcel does not put a standard bar chart inside a worksheet cell, but you can create an in-cell…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.