Пікірлер
@tofikqureshi6507 5 күн бұрын
Please help me google spreadsheet importrange function use but condination same formatting show data another sheet ❤❤ pls help
@bfkhung 28 күн бұрын
Great
@practicalspreadsheetsolutions 26 күн бұрын
Thank you for your comment!
@practicalspreadsheetsolutions Ай бұрын
Thank you for your comment!
@amosso3324 Ай бұрын
excellent solution, love it.
@AnilKumar-vi8oe Ай бұрын
Good explanation, you have to be more frequent in uploading videos to gain more subscribers
@practicalspreadsheetsolutions Ай бұрын
Thank you for your comment!
@fitzerald12 Ай бұрын
Very nice
@practicalspreadsheetsolutions Ай бұрын
Thank you for your comment!
@abhinaymujakari 2 ай бұрын
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
@debasishsamal4646 2 ай бұрын
Superb 👌
@practicalspreadsheetsolutions Ай бұрын
Thank you for your comment!
@johanneskeller3794 2 ай бұрын
Many thanks for this very useful tutorial . It allows filtering a table by the condition of the visibility of cells depending on selection by slicer. Very useful is the possibilty of creating a dynamic diagram depending on the selection of the underlying data by slicers .
@practicalspreadsheetsolutions Ай бұрын
Thank you very much for your comment!
@Ilovedogs100 2 ай бұрын
Well it’s not really an empty cell. You just replaced a text string with a space.
@yudansa 3 ай бұрын
Brilliant! Thanks for sharing this!
@practicalspreadsheetsolutions 3 ай бұрын
Glad you enjoyed it, thank you for your comment!
@dolcute555 3 ай бұрын
This is EXACTLY what Ive been looking for!! Many thanks!!!
@practicalspreadsheetsolutions 3 ай бұрын
Hello, thank you for your comment!
@practicalspreadsheetsolutions 3 ай бұрын
Hello, if you need an alternative to using dynamic named ranges for making the dynamic chart in Microsoft 365 and Excel for the web, then take a look at this video kzbin.info/www/bejne/f3KanJ99f8eqhKs&si=zD0d0DncCFEwopCz
@mohamadfawaz1928 3 ай бұрын
awesome
@practicalspreadsheetsolutions Ай бұрын
Thank you for your comment!
@jermiecanete2025 3 ай бұрын
I cannot do it on my excel web 😭 this is exactly what I want but cant do it. I have a different name manager view and the formula says invalid 😭
@practicalspreadsheetsolutions 3 ай бұрын
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.
@jermiecanete2025 3 ай бұрын
Omg thanks so much for ur effort! Ill do that, I really appreciate it@@practicalspreadsheetsolutions
@practicalspreadsheetsolutions 3 ай бұрын
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 kzbin.info/www/bejne/f3KanJ99f8eqhKs Hope this helps
@ericschulze5748 4 ай бұрын
Great!!
@practicalspreadsheetsolutions 4 ай бұрын
Thank you!
@bhuvansood5310 4 ай бұрын
👍🏻👍🏻
@practicalspreadsheetsolutions 4 ай бұрын
Thank you!
@JorgePille 5 ай бұрын
Amazing!
@practicalspreadsheetsolutions 4 ай бұрын
Thank you!
@geetikakapoor1426 5 ай бұрын
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 5 ай бұрын
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.🙂
@mushfiqulislam8898 6 ай бұрын
Provide the code
@practicalspreadsheetsolutions 6 ай бұрын
Hello, the format code is [Blue][<=0]0°C;[Red][>=20]0°C;[Color44]0°C The color names are language-specific
@sebastianschelper1741 6 ай бұрын
@Nino thanks for your thorough explanation, can you make your template Excel available to us? Thanks and cheers to Germany 🇩🇪 from SoCal
@practicalspreadsheetsolutions 6 ай бұрын
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 kzbin.info/www/bejne/pHzOqaOwaayrhsU Link to the Excel-file is at the end of the description
@akcireneri 3 ай бұрын
How can I calculate the required promoters to achieve a certain NPS target based on the existing passive and detractors?
@practicalspreadsheetsolutions 6 ай бұрын
If you would like to know how to make the slider chart in this video, here is the link kzbin.infoswWzYioAZN0?feature=share
@michaeldingee743 6 ай бұрын
Using these methods If you have Office 365 and are in Beta Version you can use =TAKE(GROUPBY(CHOOSECOLS(Data,14),Data[Orders],SUM,,0,1),-AY1) or =TAKE(SORT(UNIQUE(Data[StartOfMonth]),,-1),-AY1) Helper cells =SUMIFS(Data[Orders],Data[StartOfMonth],Days!AV7#)
@DataStadium0902 6 ай бұрын
Great video so far!
@practicalspreadsheetsolutions 4 ай бұрын
Thank you!
@punchole 7 ай бұрын
Wow, nice trick
@danishpastry678 9 ай бұрын
Well explained. Thanks.
@practicalspreadsheetsolutions 8 ай бұрын
Thank you very much for your comment!
@slmlmprcht 9 ай бұрын
This has helped me a lot thank you but for some reason mine is showing the future instead of the past. Any ideas what may have gone wrong?
@practicalspreadsheetsolutions 8 ай бұрын
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 :)
@raymondlee8951 Жыл бұрын
Beautiful charts 🌷🌷
@practicalspreadsheetsolutions Жыл бұрын
Thank you for your comment!
@godfrycunio3404 Жыл бұрын
Promo sm
@mushfiqulislam8898 Жыл бұрын
it's helpful
@practicalspreadsheetsolutions Жыл бұрын
Thank you for your comment!
@guppi1 Жыл бұрын
Nice trick. But the index match formula little bit hard to follow
@practicalspreadsheetsolutions Жыл бұрын
Thank you for your comment, I'll try to explain better next time :)
@MUAISC Жыл бұрын
You are awesome
@practicalspreadsheetsolutions Жыл бұрын
Thank you, I am glad that you liked the video.
@ericssontest2332 Жыл бұрын
…
@shadediffusion Жыл бұрын
I love your tutorials, thanks for sharing and keep making more videos
@practicalspreadsheetsolutions Жыл бұрын
Thank you!
@Michael_Alaska Жыл бұрын
Thank you for the quick response and answers to questions.
@practicalspreadsheetsolutions Жыл бұрын
Thank you for interesting questions.
@j.rjunior5584 Жыл бұрын
I've done something very similar to what you've done in the video, but except I used a pivot chart to graph my data, but I want to do it without the pivot chart. The way I want to do it is, I want a start year as my first criteria, and a end year as my second criteria for my bar chart, and I think I just figured it out LoL.
@practicalspreadsheetsolutions Жыл бұрын
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.
@Michael_Alaska Жыл бұрын
Nice video, thank you. Assuming the second chart can be a pie chart as well. Is there a file download available?
@practicalspreadsheetsolutions Жыл бұрын
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 (kzbin.info/www/bejne/aHWVfH2oaK99hq8).
@SaraTierney-c5y Жыл бұрын
Great video. Is there a way to replace the label "Others" with the selection? i.e "Chocolate" and then show the breakdown in the second chart.
@practicalspreadsheetsolutions Жыл бұрын
Hello Sara, I found your question very interesting and decided to make a video on it (kzbin.info/www/bejne/aHWVfH2oaK99hq8), this topic is covered at 02:07 and in the last part about troubleshooting. Thank you for your comment.
@holidaycat Жыл бұрын
The idea is ok but the usability is terrible because of low contrast. Needs a dark overlay
@practicalspreadsheetsolutions Жыл бұрын
That's a good idea, thank you for your comment!
@yussiffiankomaverick193 Жыл бұрын
Thanks for this video really helped
@practicalspreadsheetsolutions Жыл бұрын
Thank you for your comment, I am very glad the video helped
@practicalspreadsheetsolutions Жыл бұрын
Hello everyone! The video description can be seen on a desktop or laptop device, so in case you cannot see it on your mobile device, here is the code of the macro again: Private Sub Workbook_Open() Beep Application.Speech.Speak "Your text here" MsgBox "Your text here" End Sub Happy holidays!