How to resize a dynamic pivot chart

[Alyena]

Hi all,

i have a dynamic pivot chart. It works well, except the chart area size, it remains the same. Is there any way how the chart area can be automatically adjusted/resized (see the example attached)? Maybe with VBA?

Thank you!

[stephenloky]

You can set the range of the pivot to be a 'table' and then when the table resizes the range of pivot is already set to the table.

[Alyena]

Thanks for your suggestion. However, i am afraid it wouldn't work in my case as i need to keep the filter. Also, this is a dynamic dashboard (report), i am not sure how would it fit. Would it be too much effort for you to apply this idea in my excel example?

Thank you.

[oeldere]

Is there no way to refresh the chart?

You can refresh the pivot table i.v.m. add new data.

So I expect a chart can also be refreshed, but I don't know this for sure, since I don't make mutch graph.

[Alyena]

Well, the data in the chart is refreshing together with the table, but not the size of the chart area. The problem comes if i have, for example, only 2 lines instead of 35 (as my data is dynamic), then i am getting the "wonderful huge" graph with only 2 **** columns)))

[stephenloky]

Well, the data in the chart is refreshing together with the table, but not the size of the chart area. The problem comes if i have, for example, only 2 lines instead of 35 (as my data is dynamic), then i am getting the "wonderful huge" graph with only 2 **** columns)))

Can you post a sheet showing your desired result? I'm not understanding really well what your problem is.

[Alyena]

Sure, please, see 2 attached sheets. One has only line of data and the pivot chart is proportional with the pivot table, quite small. The second sheet has already 8 lines of data and the pivot chart got bigger and looks proportional as well. In both cases i had to resize (not refresh) the graph manually, but i would it it to be resized automatically depending on the amount of data. Maybe there should be a vba code behind it?

[stephenloky]

Sure, please, see 2 attached sheets. One has only line of data and the pivot chart is proportional with the pivot table, quite small. The second sheet has already 8 lines of data and the pivot chart got bigger and looks proportional as well. In both cases i had to resize (not refresh) the graph manually, but i would it it to be resized automatically depending on the amount of data. Maybe there should be a vba code behind it?

Hmm...I can't think in a fast way to do that....but lemme ask, why would you have a chart to show only one bar in it? :P I Think you should leave the chart in a 'good' size and if someone apply filters and need to really 'show' something, then, he/she should design the chart accordingly to his/her needs, which is more appropriated.

[Alyena]

I would like to have a chart, that shows as many bars as filtered in a pivot table, this is exactly the point. Assuming, i am the target group, who will get this report. I've got the data and then i am starting to filter my data in a pivot table. Here the problem comes as the pivot chart data refreshing together with the pivot table, which is good, but the chart window remains the same and this is bad. So data doesn't really look nice if i make by default the big chart area size and then filter my data to one line. Sure, it's 2 sec work to adjust the window manually, but it's not what i would offer to do to the target group. I would offer only the ready to use report.