Trick to connect any chart to a pivot table + an awesome dynamic sunburst chart in Microsoft Excel

[@yudansa]

Brilliant! Thanks for sharing this!

[@practicalspreadsheetsolutions]

Glad you enjoyed it, thank you for your comment!

[@fitzerald12]

Very nice

[@practicalspreadsheetsolutions]

Thank you for your comment!

[@debasishsamal4646]

Superb 👌

[@practicalspreadsheetsolutions]

Thank you for your comment!

[@mohamadfawaz1928]

awesome

[@practicalspreadsheetsolutions]

Thank you for your comment!

[@punchole]

Wow, nice trick

[@abhinaymujakari]

very informative concept. i was trying this with my pivot table , i got till the bar chart but when i am trying to change the chart type to sunburst it is not happening.

[@practicalspreadsheetsolutions]

Hello, thank you for your comment. That is an interesting problem. Did you use Excel for the web? I could replicate your problem only in Excel for the web, but not the desktop versions. In Excel for the web if you use a named range that is referencing a part of a pivot table as chart source, then the whole chart gets converted to a pivot chart and you get the same problem that sunburst chart is not supported by pivot charts, so exactly what the video is trying to overcome. But there is another method that you can use and it is compatible with the desktop version of Microsoft 365 as well. Just type the formula in one of the cells to create a spill range: =offset(a4,0,0,counta(a:a)-1,counta(3:3)) and just apply the sunburst chart to the spill range. This chart will be dynamic. Hope this helps. You can also wrap the If function around Offset to remove 0s from the spill range when collapsing parts of the pivot table, but macros will not run in Excel for the web, so you will need to expand/collapse manually.

[@practicalspreadsheetsolutions]

So the spill range of the offset function should give back the pivot table values and categories and the chart should be linked to this spill range instead of the original pivot table. Then there is no need for named ranges any more

[@geetikakapoor1426]

When there is no data in a cell, the pivot table shows that as "blank", written in its place. This also is displayed in the sunburst chart. How to avoid thi?

[@practicalspreadsheetsolutions]

Hello Geetika, thank you for an interesting question. I would suggest first of all fixing the source data, because missing data can impact the insights of the analysis. But if you want to continue with missing data, then you could do following. In pivot table 5 right click and under Pivot Table Options uncheck "For empty cells show" box (don't leave values empty, there should at least be 0 displayed). Then take a look at how to remove blanks in my short video on how to remove blanks in a pivot table under kzbin.infog0DDGnkwgrM?feature=share Depending on how you want to visualize the missing data you can either leave the now missing blanks empty or collapse them all in the pivot table by just clicking on the - button next to one of the blanks (this solution is dynamic and new blanks will also collapse). By the way, if the brand (field of the slicer) contain blanks, then as long as you don't remove blanks in the other pivot table 9, then as soon as the macro is triggered, the empty brands will be collapsed. You can also remove the commas that are left in the data labels by selecting all labels and in the Format Data Labels pane choosing a space as a separator instead of comma. Hope this helps.🙂