When you create a chart, you select a range. Now assume, one more row of data is added. But that will not be reflected in your chart as your chart had a fixed range.
— The Excel for this article can be downloaded from Dynamic Charting —
A work around is to select more rows in your range beyond the existing range. i.e. if your current range is A1:B5, you can select A1:B10 where A6 to B10 are blanks. Hence, when data is added into A6 to B10, chart will automatically be updated. But there are two problems in this approach.
1. Chart shows placements for A6 to B10 also, hence your chart will have 10 data points in this case whereas actual graph will be for A1 to B5.
2. If your data goes beyond A10 that will not reflect in the chart unless you change the range again for the chart.
There are 3 approaches possible to overcome above limitations and create something called Dynamic Chart –
1. Table Approach
2. OFFSET Approach
3. Pivot Table Approach
Approach 1 – Table Approach
1. Make your chart on existing data range i.e. A1:B5 in above example
2. Select the data range (A1:B5) and press CTRL+T to convert the range into tables.
Note – You can also follow step 2 first and then step 1.
Now, you chart will be updated when more rows of data is added. Also even if columns are added, chart will be updated for those columns also.
But table approach has a problem in case of deletion of data. If data is deleted, the table considers deleted data as the range also as table formatting doesn't get removed from those cells. Hence, you will notice placeholders for that data. (this can be overcome by selecting those cells and Home tab > Clear > Clear All OR Clear Contents. Hence, ordinary delete from those cells would not work)
Approach 2 – OFFSET Approach
Suppose you have x axis data in A2:A10 and y axis data in B2:B10 like below
1. Define a name for x axis data. Let's say this is Month (you can choose whatever you want).
Go to formula's tab and click Name Manager and Click New.
Put Month in Name: box
Put following formula in Refers to: box
=OFFSET($A$1,1,0,COUNTA($A:$A)-1,1)
2. Similarly, define a name for y axis. Let's say that this is Sales.
Put following formula for Sales
=OFFSET($B$2,0,0,COUNTA($B:$B)-1,1)
Note – If there are more columns, just replace B with C, D……..And Name them accordingly.
3. Create a chart on the existing data.
4. Select the chart and right click on this and click Select Data.
5. Click Edit in Legend Entries (Series) box and put following
= Sheet1!Sales
(Please note that both = and sheet name is mandatory). If you are in a sheet other than Sheet1 say Sheet3, then formula would become Sheet3!Sales. Assume your sheet is named as mydata. Then, formula would become = Mydata!Sales.
If there are more columns, repeat the above steps for all other columns. You have already created names for them in step 2.
6. Similarly, do it for x axis also by clicking on Edit under Horizontal (Category) Axis labels. The formula for x axis would become-
=Sheet1!Month
(Please note that both = and sheet name is mandatory). If you are in a sheet other than Sheet1 say Sheet3, then formula would become Sheet3!Month. Assume your sheet is named as mydata. Then, formula would become = Mydata!Month.
Now, whenever you add data to the table, that will be automatically added to the chart. (Even if you delete the rows from bottom, the chart would be updated accordingly)
Approach 3 – Pivot Table Approach
1. Create a Pivot table on the entire columns. Hence, in the example Excel, I have created Pivot Table on both Columns A and B. In the values area, I have chosen Sum not Count.
3. Whenever, you add or delete a data, just refresh this pivot and your chart will be dynamically updated.
4. But there is a problem as your data range contains blanks also and this will be shown in the chart.
5. To overcome, this limitation, we need to apply a filter in the Pivot. Apply the Date Filter > After > Date of 1/1/1900. Now, it will cover entire date range for you. (Depending upon x axis, you will have to choose your Filter accordingly. I had dates, hence I wanted to show all dates. If you have text, you can have a filter of not equal to blanks)
Note – Don't apply the filter by un-ticking blanks on first column as it makes the other data fixed from pivot purpose. Hence, any addition of data will not be capture by the pivot.