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

That looked amazing, thank you . However, I don’t know the actual steps.

I have read some how to use power query web page. May I also trouble you whether following steps are correct:

1. open the testing work book
2. go to data and click from table
3. close and load to the existing work book
4. click load

Here are come a list of my questions:

1. the WB closes completely, what did I do wrong?
2. whether I need to change the source data of the chart from the PQ sheet?
3. do you mind to teach me what do I need to do with regards to “ place chat tables” on the spreadsheets?
4. there are a number of tabs which I need to use this method, I assume that I fools the same steps

I truly thank you for your help.

Cheers

Answer 3

Try this:

Open your test file

Delete the existing charts

Select a cell inside the table

Data \ From Table (PQ opens)

On the right there is a list of applied steps, delete the "Change Type" step.

Close & Load

After that you (should) have a new sheet with a copy of the original data

Switch back to sheet1

Select a cell inside the table

Data \ From Table (PQ opens)
On the right there is a list of applied steps, delete the "Change Type" step.

Filter out the products B & C from the Description column

Close & Load

After that you (should) have a new sheet with a copy of a part of the original data

Now create 2 charts from this 2 new sheets/tables and move them into Sheet1, done.

Andreas.

Answer 4

Thank you so much Andreas. The workbook is still keeping shutting down once I clicked the save and load button in PQ. Sometime it pops up an warning message "Excel found a problem with one or more formula references in this worksheet, check that the cell references, range names, defined names, and links to other workbooks in your formulas are all correct". I have tried to copy and past data to another worksheet, but it still keeps shutting down on me every time when I click the save and load button. I have even checked the file name according some recommendations from some blogs.

Would this relates to my setting? I do have to mention that the worksheets in the workbook are all linked by formulas. Would this cause the problem?

thank you again for your time to help me.

Answer 5

Sometime it pops up an warning message "Excel found a problem with one or more formula references in this worksheet, check that the cell references, range names, defined names, and links to other workbooks in your formulas are all correct".

Would this relates to my setting? I do have to mention that the worksheets in the workbook are all linked by formulas. Would this cause the problem?

I can not answer that without to see your file, but just a test:

Make a copy of your file
Remove any chart in there
Is it a XLSM file? If so, save the file as XLSX, close Excel and reopen

Now try to create a PQ

Does that work?

Andreas.