Видео

Hello! You are welcome!

Thank you for your comment!

Thank you for your comment!

Thank you for your comment!

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.

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

Thank you for your comment!

Thank you very much for your comment!

Glad you enjoyed it, thank you for your comment!

Hello, thank you for your comment!

Thank you for your comment!

Hello, thank you for your question. The web version functions a bit differently than the desktop version. The trick with dynamic named ranges in the web version is, that you have to include the sheet name and an exclamation sign in the formula before every cell reference (cell references still have to be fixed). Then you will not get the response that the reference is invalid. But you will run into the next problem with the chart. In the web version you cannot add chart series one by one. You can only add the whole source. If the whole source is just a named range, then it will get changed to fixed cell references and the chart will always display the same number of points. But there is a workaround (I was actually planning a video on this). Because you are in the web version, the offset function will spill. So add the formula somewhere on the sheet instead of the name manager. Type your headers on top of this spill range. Then click on one of the spill range cells, insert a chart, and you will get a dynamic chart, that will display the number of categories as in the spill range. The trick here is, that the whole spill range has to come from one formula, so if you have nonadjacent columns, then the offset function has to be set to return multiple columns (last argument of the function) and it has to be wrapped in the choosecols function to get the columns you need. Hope this helps.

Omg thanks so much for ur effort! Ill do that, I really appreciate it​@@practicalspreadsheetsolutions

Hello, the new video with an alternative to making the chart with dynamic named ranges in Microsoft 365 and Excel for the web is here ruclips.net/video/IA7klGJbwNI/видео.html Hope this helps

Thank you!

Thank you!

Thank you!

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 ruclips.net/user/shortsg0DDGnkwgrM?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.🙂

Hello, the format code is [Blue][<=0]0°C;[Red][>=20]0°C;[Color44]0°C The color names are language-specific

Hello Sebastian, here is the link to the file from the video drive.google.com/file/d/1AzxEbQjcaN7_kGGCbFXvXB6UD9bRbv25/view?usp=sharing I also have a similar video where I make a regular dynamic chart linked to a pivot table, so if your pivot chart should lose formatting (happens sometimes), then this video may be interesting for you ruclips.net/video/nKkxpz4GxPc/видео.html Link to the Excel-file is at the end of the description

How can I calculate the required promoters to achieve a certain NPS target based on the existing passive and detractors?

Thank you!

Thank you very much for your comment!

Hello, thank you for your comment! If you used the OFFSET function, then I think that you may have used a positive number in the fourth argument of this function (height) and need to use a negative number there. Positive height means the range forwards and negative height means the range when going backwards. Hope this helps :)

Thank you for your comment!

Thank you for your comment!

Thank you for your comment, I'll try to explain better next time :)

Thank you, I am glad that you liked the video.

Thank you!

Thank you for interesting questions.

Hello J.R Junior, thank you for your comment. I guess you need two drop-down lists then and a formula with index and match functions.

Hello Michael, I am still working on a website, so I don't usually offer file download yet, but I posted a download link in the description of the new video (ruclips.net/video/2D2KJr3JJPM/видео.html).

Hello Sara, I found your question very interesting and decided to make a video on it (ruclips.net/video/2D2KJr3JJPM/видео.html), this topic is covered at 02:07 and in the last part about troubleshooting. Thank you for your comment.

That's a good idea, thank you for your comment!

Thank you for your comment, I am very glad the video helped