Automatically update the excel chart

Question

I want my charts updated automatically each time when I add a new column. I have converted the data into a table, also defined the names of the row. However the 2nd chart isn’t updating automatically as I wanted. This chart select the total sales row and the percentage calculation row. Here is my testing file if anyone can help me. Thank you

https://www.icloud.com/iclouddrive/0PSaNEG8-IOvMTkPXcWEAytpA#test

Answer 1

With this formula

=OFFSET($A$2,0,1,,COUNTA($B$2:$M$2))

We assume the title is in A2 and the first item of data is in B2. OFFSET from A2 by 0 rows and 1 column, that is B2. The COUNTA portion is the width of the data, column B to M is 12 columns. Hence our data is 12 months long.

=OFFSET($A1,0,1,,COUNTA($B$1:$M$1))

This formula had a typo which I have corrected above. It is OFFSET from A1 not A12. These are the 12 month labels Jan...Dec. It works exactly as the first OFFSET formula above.

("worksheet",workbookfilename&worksheet,1)

Here you must use the Range Names. So

=SERIES("Actuals",Book1.xlsx!Months,Book1.xlsx!Actuals,1)

"Actuals" is the name you want to give the series.

Book1.xlsx is the file name

Months is the range name you defined earlier.

So Book1.xlsx!Months is the title row or, in the chart it becomes the horizontal axis., the x-axis.

Book1.xlsx!Actuals is the series or the first line in the chart.

1 tells Excel which line or series in the chart this SERIES formula is entering. If you have 3 series in a chart they would get the numbers 1, 2, 3. And in most cases which SERIES gets which number is not important. But, for example if you have a stacked column chart, this number controls which series position in the column: is it at the bottom, center or top.

Following these steps is a little more difficult if your filename is something like "My Budget 2021". In this case the SERIES example, using the same range names becomes:

=SERIES("Actuals",'My Budget 2021.xlsx'!Months,'My Budget 2021.xlsx'!Actuals,1)

In this case you need to quote the filename.

Range names can be done from the Formula, Name Manager dialog box or the Define Name dialog box as shown below:

You can test the formula in a blank area of the spreadsheet and then copy it into the Refers to line of the box above. If you return the the Name Manager you will see that Excel has added the sheet name to the formula: =OFFSET(Sheet1!$A$2,0,1,,COUNTA(Sheet1!$B$2:$M$2))

As for the SERIES formula, I create the chart from the data range first, here I would highlight the range A1:M2 and choose Insert, Chart, Line Chart, Line. I comfortable with editing the formula in the chart, so I select the series line on the chart: (Notice I didn't bother selecting all the data, because I'm going to modify the formula.)

Then you select the SERIES formula on the Formula Bar:

=SERIES(Sheet1!$A$2,Sheet1!$B$1:$G$1,Sheet1!$B$2:$G$2,1) and make the necessary changes:

=SERIES("Actuals",'My Budget 2021.xlsx'!Months,'My Budget 2021.xlsx'!Actuals,1)

By changing the SERIES function you automatically change the title, x-axis and you change the series.

Shane

Answer 2

The technical detail why the 1st chart updates is that it refers to the whole table, and when the table resizes the chart shows the data inside the whole table.

The 2nd chart shows only a part of the table and therefore Excel is not able to recognize which part you like to show. I know, that look really simple to recognize for a human, but for a todays PC => impossible.

Andreas.

Answer 3

Thank you Andreas, is there a way to work around it?

Answer 4

I can do a detour, I suppose by creating individual tables with those select rows only. Is there any other way to work around it? Thank you.

Answer 5

I can do a detour, I suppose by creating individual tables with those select rows only.

Well, the idea is on the right track, but that is no solution in the sense of this thread.
The problem is that the 2nd table did not resize automatically if you add another column to the 1st table.

The workaround for that is to use Power Query and simply filter the data from the 1st table into the 2nd. Of course you need to click Data \ Refresh. To make that process "smooth", I would use a copy of the 1st table using PQ also:

And you need to place this "Chart Data tables" on separate sheets, because we do not know how large they get.

When it's worth the effort...

https://www.dropbox.com/s/d3pb5swpzj8s2mp/0313e9cb-c27c-4774-9624-9b52782260e0.xlsx?dl=1

Andreas.