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

Hi Andreas, unfortunately it didn't work. The file is in xlsx format. I have created testing version, I have tried this file in both my work and my personal PC, It is still not working. I don't know what I did wrong. May I please trouble you to have a look ? thank you.

https://www.icloud.com/iclouddrive/0VSNTuxIWIfiTZCdVwg8_VtMg#Testing2

Answer 3

I wouldn't develop this file further that way, nor introduce new technologies to fix old problems, that just ends up with even more problems.

But I see a way to get all the new technologies to work and automatically add the new years to chart's etc.

Reformat and clean your data into tables, load them into Power Query, unpivot the year columns and load the table into the Data Model. Now you can create Pivot Tables and so Pivot Charts from the Data Model.

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

In this example file when you add a new column and click Data \ Refresh All the column is added to the Pivot table and the Pivot chart as well.

Andreas.

Answer 4

Thank you Andreas. Is there any chance you would teach me why the 1st methodology doesn’t work for the 2nd testing workbook. Is it because the formula links with other worksheets in the workbook? This happened to me once with another workbook of mine. It must because the way how I set up the data. I would like to learn the rage cause, soI would know when I use PQ next time. Thank you very much again for your tips and advice.

Answer 5

Hi,

You can do this with a formula approach. Suppose your data is laid out as follows:

I have made this example as simple as possible, in particular the file is named Book1.xlsx.

1. Create two range names:

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

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

When you define them Excel will modify the names as shown on the Refers to line:

2. Edit one of the SERIES formulas, I chose Actuals:

We change it from:

=SERIES(Sheet5!$A$2,Sheet5!$B$1:$G$1,Sheet5!$B$2:$G$2,1)

To:

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

I did this on the Formula Bar, but you can also do it through the Chart Design, Select Data command.

Important:

1. If the filename includes spaces or special characters you may need to quote it:

'Book 1 - Charting.xlsx'!Actuals

2. Notice we have changed the reference from a sheet name to a file name!

Now when you add data for July the chart will automatically expand to the right.