OmniDocHow to make a dynamic chart in Excel, it will automatically change after the data is updated
By default, the chart only references the fixed area selected by the frame, and new data will not automatically appear in the chart. This article talks about three ways to make Excel charts automatically change with the data, and demonstrates in detail the steps and verification methods of using defined names to create dynamic areas.
Key points of this article
- When the data is continuously appended with details, convert the area into a table first. After the chart references the table columns, it will automatically expand with the new rows.
- The dynamic area calculates the number of rows by adding INDEX to the defined name, and can accurately control the range, but the premise is that there can be no blank rows in the middle of the data.
- The chart refers to the name rather than the fixed cell range. There is no need to change the charts one by one after modifying the name formula.
- Pivot charts are suitable for statistical tables summarized by categories. After the source data is updated, it needs to be refreshed to reflect the new data.
- After finishing, use a row of test data to verify the chart, and then delete the test row after confirmation to avoid leaving it in the summary by mistake.
If you make a sales trend chart and add data for a new month at the end of the table a few days later, you find that the chart is still in the old range and the new data does not appear in the chart at all. This is a situation that many people will encounter when making weekly or monthly reports: the chart defaults to the fixed cell that was originally selected. If the table becomes longer, the chart will not automatically grow longer.
This article is written for people who need to update data frequently, such as salesmen who record additional sales every month, warehouse managers who maintain inventory tables, and teachers who regularly update attendance and grades. After reading this article, you will be able to distinguish three ways to make charts change automatically, and you will be able to use the flexible "dynamic area" method to allow the data source to automatically expand and contract as the number of rows increases or decreases.
The key difference is that by selecting a fixed range of cells, you get a static data area; dynamic charts allow the data source itself to scale. Suggestions for selection are given below.
If you want the Excel chart to automatically change with the data, which method should you use? One sentence conclusion
If the data is a detailed list that continues to be appended, convert the data into a table first; if precise control of the range is required, use a defined name to make a dynamic area; if the chart is a statistical result of classification and summary, use a pivot chart and refresh it after updating the data.
| your situation | Recommended practices | reason |
|---|---|---|
| Rows are appended to the end of the table every month, and the table structure is fixed | Convert to table (super table) and then build the graph | New rows will be automatically included in the table, and the chart source will be expanded accordingly, requiring less operations. |
| Only want to display the most recent rows, or there may be blank rows in the middle of the data | DEFINITION NAME + INDEX DYNAMIC AREA | The starting and ending lines can be precisely controlled, and the formula can be adjusted |
| Summarize by category and frequently switch statistical calibers | PivotTable + PivotChart | The summary is completed by the pivot table, which can be refreshed after updating the data. |
| Data is spread across multiple worksheets or files | First organize it into the same table, and then apply the above method | The maintenance cost of dynamic range across tables is high, so it is safer to unify the structure first |
Before you start: Confirm these points first
- Whether the data is continuous: Dynamic areas usually calculate the number of rows based on the number of non-empty cells in a certain column. If there are blank rows in the middle, the calculation result will not include the following data, so check whether the data is tightly arranged before starting.
- Header position: It is recommended that the header be fixed on the first row and the data start on the second row. The starting point of the formula must be aligned with the data. If there are merged cells in the header, it is recommended to cancel the merge first.
- Update method: Append at the end, or modify a value in the middle? When you only modify the existing value, the chart will change because it refers to the cell itself. Dynamic ranges or tables are only needed when appending new rows.
- Chart type: Line charts and column charts are suitable for dynamic data sources. Pie charts are more suitable for situations with fewer categories. If there are more rows, they will appear crowded. Dynamicization cannot change this.
- Backup file: Make a copy before modifying the name and data source. Wrong formulas in the name manager are not easy to spot at a glance, so you can roll back with confidence only if you have a backup.
Steps to create dynamic areas with defined names
The following is a common example: Column A is the month, column B is the sales volume, row 1 is the header, and data entry starts from row 2.
- Check whether the data area is clean: Make sure that columns A and B are filled in continuously starting from row 2, with no blank rows in the middle and no merged cells.
- Open Name Manager: Find the name manager in the formula-related functions of Excel and prepare to create a new name. The name here is an alias given to a dynamic area, and the chart will refer to it.
- Create a new "month" name: fill in
=INDEX(Sheet1!$A:$A,2):INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))at the reference location. The meaning is starting from A2 and extending to the last row of non-empty cells in column A. - Create a new "sales" name: fill in
=INDEX(Sheet1!$B:$B,2):INDEX(Sheet1!$B:$B,COUNTA(Sheet1!$A:$A))at the reference location. Note that the number of rows is still based on column A, so the two names must be the same length. - Modify the data source of the chart: Select an existing chart, open the settings for selecting data, change the horizontal (category) axis to "Workbook Name! Month", and change the series value to "Workbook Name! Sales", such as
='销售报表.xlsx'!月份. If there is no chart yet, you can first create a chart using any area and then replace it in this way. - Add a row of test data at the end: Enter a month and a value below the last row of the table, and observe whether there is an extra data point in the chart.
- Check item by item when it does not take effect: Check whether the starting positions of the two name references are both row 2, whether the column letters in the formula are written correctly, and whether there are blank rows mixed in the data.
- Delete the test row after confirmation and save it: Leaving the test data in the table will affect the summary and charts. Delete it after confirming that the function is normal, and then save the file.
Check each item after completion
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Whether the new data appears in the chart | Add a line of test values at the end to see if the chart has one more data point | Does not appear: Check whether the column of the name formula is correct and whether the data source has been changed to name |
| Are categories and values aligned? | See if the month of the horizontal axis corresponds to the corresponding column or broken line point one by one | Dislocation: the starting line or the number of rows of two names are not the same and are counted in the same column. |
| Is there a blank line in the middle? | Observe whether the line numbers are continuous, or use filtering to check blank cells | Blank lines: delete the blank lines or use the table method instead |
| Does the chart mistakenly contain headers or total rows | See if the words "month" or "total" appear on the horizontal axis. | Put the total line outside the dynamic range, or exclude it from the name formula |
| Test whether the data has been cleaned | Check whether there is a trial at the end of the form. | Delete the test line and confirm that the summary results have been restored |
# Core comparison
The difference between the three methods is mainly in the update trigger mode and the applicable data form.
- Super Table : select the data area and convert it to a table (the common shortcut key is Ctrl+T), and then make a drawing after confirming the header. Later, enter directly at the bottom of the table, the table will automatically expand, the chart will follow the change, there is no need to write the formula. The steps above- define name + INDEX**: fall into this category. It can control the start and stop lines, for example, only show a certain interval, high flexibility, but need to check the formula and data alignment. - PivotChart : create a PivotTable based on the data source, and then insert the PivotChart accordingly. The source data proposal is also a table or expanded scope, and the perspective and summary will not be synchronized until the data is updated manually.
| Method | Difficulty in getting started | Update mode | Appropriate data |
|---|---|---|---|
| Super watch | Low | Enter and expand | Continuous addition of details |
| Definition name + INDEX | Medium | Automatic calculation range of formula | Sequences that require precise control of the range |
| Data perspective | Medium | Refresh manually after updating source data | Statistical tables summarized by category |
# frequently asked questions (FAQ)
1. With the addition of data, why does the chart remain unchanged?
Usually the chart still refers to the old fixed area, or the number of rows in the name formula is miscalculated. First see if the data source of the chart points to the name or table column, and then check that the name formula counts with the correct column.
2. What's the difference between OFFSET and INDEX?
OFFSET can also do dynamic regions, but it is a volatile function, each table calculation will recalculate, a large amount of data may slow down the file. INDEX is not a volatile function and is more secure in general reports, so it is recommended first in this article.
3. If there are blank lines in the middle of the data, what happens to the dynamic chart?
A formula based on the count of non-empty cells treats the blank line as the location where the data ends, causing the data after the blank line to not be displayed. The appropriate way is to delete blank lines and keep the data continuous.
4. I can't write a formula, is there any way to update the chart automatically?
Yes. Convert the data into a table, and use the table column to build the chart, the chart will be automatically expanded after adding rows, and there is no need to write any formula. If the chart is summary statistics, you can also use the PivotChart to update the data and refresh it.
# Summary
Excel charts change automatically with the data, and the key is to make the data source scalable. The continuous addition of the details is easy to use the table, when you need to precisely control the scope, use the definition name as the dynamic area, and the statistical summary is handed over to the PivotChart. If you test it according to the checking steps in this article, you can safely use the chart in each updated report.
