Yes, the great uncle Sam.. :)) very informative, not sure why I spent more than 10 minutes of my life on this and I'm still smiling (maybe thinking of how creative people can be)
Thank you for sharing both versions. While I prefer the look of Sam’s version, I really doubt all that fine-tuning is going to have a significant impact on a business presentation. It brings to mind the law of diminishing returns. So for the sake of efficiency and effectiveness, I’d just use your version and spend more time on making the business case more attractive instead.
Couldn't agree more. Sometimes we get caught up in the small intricacies of our curiosity and imagination, but it can often hinder us from what's priority: which is just developing a presentable and easy to follow report/dashboard for decision making. That's it!
I used to play with more mathy charting in Excel 2003 when we could do snappy dynamic charting from VBA. But then it slowed down with the shared charting between Word and Excel in 2007. This piques my interest in the mathy bits again. Thanks Sam and Leila!
I'm lost with the sin/cos functions because its have been a long time since the last time I used it, but its fun to know sam method, now , i will try to understand this and find another uses to it... thanks :)
for real. While this solution is brilliant it is more aptly an example of how horrendously behind Microsoft is in employing such a simple shape feature to their doughnut chart. This should be a one click solution for 'rounded edges' in the chart format page.
Your excel tutorials are my go to when I am stuck up. Thank you so much for guiding me in my quest for better excel knowledge. I already have taken up your VBA course in udemy. :) Once again Thank you so much.
Hi all, I believe the 2nd solution is adding half a circle's area to the completed ring to both ends (so a full circle) which changes the visual proportions. Better would be if the whole of the circular disk was inside the completed ring, with the circumference tangent to it. This may not be much of a problem if the completed percentage is high enough, but it does skew the visual info the graph is communicating. Also, if the completed percentage is small, like 1% or even 5%, this will certainly give the wrong picture to the audience. It will start to become apparent. In the case of small percentages, we risk the radious of the circle being to large to depict them. As such with this method we can show up to a certain minimum of completed percentage. I just saw this and i don't have the time to create and test it (02:50, here in the UK and i have to be in the office in 5 hours...) but if i had to guess i'd say it shows a full circular disk even at 0%. A better way to implement something like this would be to have ellipses at the end of the ring, where the a,b parameters would be dynamically linked to the completion percentage, with possibly an upper bound, to avoid extremes. The equation of an eclipse is (x^2/a^2) + (y^2/b^2) = r^2. a,b and r are given in squares for symmetry. For a=b we get a circle (the above equation becomes x^2 + y^2 = r^2*a^2 (or *b^2), which is basicaly x^2 + y^2 = R^2 (just a new r, as a,b are parameters). Can you please check what happens for 0% or 1% ? I feel the first solution is correct.
It would have been nice to see the results of both methods at 99%, 98%, ..., etc. And then see at which % point does each method not have overlapping pixels.
I agree, but given the series are provided through a calculation i feel like we can simply subtract the radius of the scatter plot markers to account for the difference. 1 important thing though will be that one needs to add the removed radius back to the doughnut when the input value = 100% Otherwise my solution will give you a slight area where the grey is still showing although the percentage is 100%. I'll be using my adaptation of Sams solution here. Great work, thanks for sharing both! :)
I just tried it with the workbook. At 98% the circle is full but you can still see the dents from the rounded edges to indicate it's not 100%. Works slightly better on Leila's version.
I loved the 2nd way of representation. I have studied trigonometry, but never used practically. Could you please explain the Cos and Sin function used in the second method ??
Actually I find that using Chart Title in place of the Textbox to display the center text isnt too bad too. It optimizes the object space too. (And I just spent minutes trying to group the textbox and the chart to no avail, didnt know about the trick to select the chart area first before creating a textbox) Wow, both are very eye-opening. Who said trigonometry and geometry is useless after leaving school, lol!
Awesome stuff, Leila. I'll try your method. Immediately thought that it can be used for Dashboards or to visualize metrics. Is it possible to have the color be dynamic? I mean, let's say if the number is below 70%, The progress bar would be Red. And then if greater than or equal to 70% but less than 90%, it would be Yellow or Amber. If greater than of equal to 90%, it would be Green.
@@LeilaGharani How about using 4 data for a single chart? A1 = value if below 20 A2 = value if 20 - 80 A3 = value if 80 - 100 A4 = remainder Example: If 19, then A1 = 19, A2 = 0, A3 = 0, A4 = 81 If 35, then A1 = 0, A2 = 35, A3 = 0, A4 = 65
This looks is really cool and all, but the only problem with this style is accuracy. You may find it confusing when it comes close to somewhere between 95% to 98%.
Definitively the first one. The second one is nicer but show bigger progress than real, the extremities of the marker go beyond the doughnut: 97% looks like 100%
Can u please make a automatically update column List of Income or Expenses from a Excel bank statement 🙏 for eg. If i write Salary the cell in GROUP Column shows INCOME..if Personal Exps. then GROUP Column shows EXPENSES...etc. correctly n automatically 🙏❤️
If I have three slices to the pie. In progress, declined and completed. I want the declined to highlight red, the inprogress blue and the completed light green. (I know my color choice is awful)...but my problem is when I have three slices... and I add the border width, the slices overlap. I would want my "In Progress" to be the one which is bigger than the other two slices. But I get the Declined overlapping with my In progress...it's on top of my In progress bar. Is there a way to "Send to Back" the slice you don't want focus on? Your videos are amazing, thanks so much for making them so EASY to understand and follow!
I prefer to use the one that comes ready with excel without complications. Because when I come back after one month and try to add something I wouldn't know what this is all about.
Nice! But... I think that, although it might be more aesthetically pleasing to some people, the second version is somewhat untrue since the blue sector will always be larger than the given value (by the radius of the dots) !? Yes I know that I'm being a boring, nit picking £#@
You could put a grey rounded circle on the start position (12 o'clock), this compensates for the end blue round (keeping the surface correct) and makes the graph look like it can "clip" into itself when you get to 100%.
This has changed my work life. I do have a question though. Is there a way to make this application of the doughnut chart segmented into months, and each month show a different color for progress? For example, January met 100% of a goal - 1/12 of doughnut Green; February met 60% of goal, next 1/12th yellow...and so on. A doughnut monthly KPI chart.
Sam's nice tweak solution is to - Add Start and End points as Filled Circles at a calculate points (Simple Trigonometry) - Fix the graph so the circles are plotted at expected points - Define the scale of each axis Personally i prefer the simpler solution but i am fascinated with the math.
I need to display the incremental progress on my finance sheet after every milestone invoice completion. How do I do it using excel chart ( Half Pie Preferably) e.g. Total Project amount is $100,000. Milestone 1 will be Q1 - 20,000 , Q2 - 30,000 and Q3 - 50,000.
Grab the file I used in the video from here 👉 pages.xelplus.com/progress-circle-chart-file
Sine, Cosine? You mean there's actually a practical use for this????? I should have paid more attention in school.
That's exactly what I thought 🤭
Anywhere you see things are moving in a circle, you can use trigonometric functions to solve problems easily.
Lol
The world is full of circles.
Ha, really if they only had have told it me at school! They do not tell it us in all countries! 😄😄
This is amazing. I was looking for this technique from last many months and not getting any hint. Thank you so much for sharing this.
I could live a thousand life times and I never would have come up with Sam's solution. I'll never use this method but it's cool knowing it exists. ;-)
It's always good to have something up your sleeve just in case :)
Agree. My poor brain was hurting
😂😂😂. I was thinking the same too
Yes, the great uncle Sam.. :)) very informative, not sure why I spent more than 10 minutes of my life on this and I'm still smiling (maybe thinking of how creative people can be)
Thank you for sharing both versions. While I prefer the look of Sam’s version, I really doubt all that fine-tuning is going to have a significant impact on a business presentation. It brings to mind the law of diminishing returns. So for the sake of efficiency and effectiveness, I’d just use your version and spend more time on making the business case more attractive instead.
Couldn't agree more. Sometimes we get caught up in the small intricacies of our curiosity and imagination, but it can often hinder us from what's priority: which is just developing a presentable and easy to follow report/dashboard for decision making. That's it!
Sam is pretty GENIUS! Thanks Leila for sharing it.
It sure is :)
After watching this my excel skills went down to 0.0001%
😄😄😄😄😄👍👍👍
🤣🤣🤣
we are in the same boat... mine is probably even smaller lol
😅😅
That was very satisfying! Sam's method is gold...I would never have thought of that!
Thank you! Cheers!
That was amazing both solutions, and sam's was simply...brilliant
I like Sams solution the most, it is impressive, but would never make things that complicated in practical use, and definitely go for Leilas solution.
Sam's solution really is genius.
I used to play with more mathy charting in Excel 2003 when we could do snappy dynamic charting from VBA. But then it slowed down with the shared charting between Word and Excel in 2007. This piques my interest in the mathy bits again. Thanks Sam and Leila!
Thanks for sharing!
Truly elegant solution.
I never thought of using pie charts for making circle progress indicators. I love this. Thanks!
Food for thought Luciano :)
We are celebrating teachers day today in India and I want to wish you happy teachers day as I had learned some great stuff in excel from you .. 👍🏻
Thank you very much! I feel special today :)
Mam.. plz make an elaborative tutorial on how to coustomize index on advanced level in Excel ...
It will be great support to everyone..
Ma'am Happy Teacher's Day!! Wish you all the love & Happiness in you life!!!
Thank you very much!
That's impressive use of cos and sin functions.
finally jeje
Just like apple watch ring! Been searching for this hacks for ages. Thanks
Leila very helpful, i dont miss Thursday watching UA-cam, because for your video
That makes me happy :)
Happy teachers day Leila. You are my favorite teacher. Video is excellent. I like Sams version.
Thank you! Sam's version is genius.
I've apply for my dashboard report. I really thank for this 👍👍🙏
Thank you for a very good and clear video. I appreciate that you have made the work available in Excel for downloading.
Thanks Leila you always Queen of Excel
Both are great.
1st simple for many of us.
2nd advanced for more fancy reports.
I'm lost with the sin/cos functions because its have been a long time since the last time I used it, but its fun to know sam method, now , i will try to understand this and find another uses to it... thanks :)
I still don't really understand it :)
Sam's was sick! I almost spit out the water I am drinking when I saw the rounded edges after the setup! Sin/cos!!!! Ftw!
😂
Thanks! All your videos teach me a bunch.
That's good to hear Victor.
You are AWESOME!!! EXACTLY what I was looking for.
Very clever. Congratulation.
How long has Excel been around? I'd have thought they would built this in by now as a line style or something.
for real. While this solution is brilliant it is more aptly an example of how horrendously behind Microsoft is in employing such a simple shape feature to their doughnut chart. This should be a one click solution for 'rounded edges' in the chart format page.
Your excel tutorials are my go to when I am stuck up. Thank you so much for guiding me in my quest for better excel knowledge. I already have taken up your VBA course in udemy. :) Once again Thank you so much.
That's great to hear! Thank you for your support.
Oh yess... Lovely!!!
Happy Teachers Day Leila Mam.
Thank you very much!
Beautiful Charts, Leila!!! Thanks, Teammate!
Thank you Teammate Mike!
Both are cool - kudos to both of you!
You are unbelievable, I'm really impressed
Thanks Leila,
Version 1 take little efforts and time too.
However the credit goes to Sam for rounded shape dynamic chart. ☺️
Thank you both.
Sam really created something great there.
Crazy idea. Thanks a lot 👍
Most welcome 😊
Thanks Leila and Sam... great tutorial
Great work
Hi Leila, A real beauty! Easy to understand and reproduce! Thank you :-)
I love it. They are both great manipulations. But I think I would use Sam's.
Great video and explanation!
That was REALLY good 👌"Thank You", thank you very much for sharing your support and time 😉
Glad you enjoyed it
Hi all,
I believe the 2nd solution is adding half a circle's area to the completed ring to both ends (so a full circle) which changes the visual proportions. Better would be if the whole of the circular disk was inside the completed ring, with the circumference tangent to it.
This may not be much of a problem if the completed percentage is high enough, but it does skew the visual info the graph is communicating.
Also, if the completed percentage is small, like 1% or even 5%, this will certainly give the wrong picture to the audience. It will start to become apparent.
In the case of small percentages, we risk the radious of the circle being to large to depict them. As such with this method we can show up to a certain minimum of completed percentage. I just saw this and i don't have the time to create and test it (02:50, here in the UK and i have to be in the office in 5 hours...) but if i had to guess i'd say it shows a full circular disk even at 0%.
A better way to implement something like this would be to have ellipses at the end of the ring, where the a,b parameters would be dynamically linked to the completion percentage, with possibly an upper bound, to avoid extremes.
The equation of an eclipse is (x^2/a^2) + (y^2/b^2) = r^2.
a,b and r are given in squares for symmetry.
For a=b we get a circle (the above equation becomes x^2 + y^2 = r^2*a^2 (or *b^2), which is basicaly x^2 + y^2 = R^2 (just a new r, as a,b are parameters).
Can you please check what happens for 0% or 1% ?
I feel the first solution is correct.
It would have been nice to see the results of both methods at 99%, 98%, ..., etc. And then see at which % point does each method not have overlapping pixels.
I agree, but given the series are provided through a calculation i feel like we can simply subtract the radius of the scatter plot markers to account for the difference.
1 important thing though will be that one needs to add the removed radius back to the doughnut when the input value = 100%
Otherwise my solution will give you a slight area where the grey is still showing although the percentage is 100%.
I'll be using my adaptation of Sams solution here. Great work, thanks for sharing both! :)
None is better than you 😍
I wonder if something like 98% would cover all the chart, since the endpoints are adding some extra area. I like Leila’s solution more:)
I just tried it with the workbook. At 98% the circle is full but you can still see the dents from the rounded edges to indicate it's not 100%. Works slightly better on Leila's version.
Well.. If you would like to be that precise, you can consider making the doughnut narrower
Could also just have the leading edge rounded off, and the starting edge a flat line (no point)
=98-2
Thank you very much for your continuous efforts...
My pleasure. Always nice to see your comments.
speechless!! wonderful trick.
Thanks Leila, you are great!!!
I loved the 2nd way of representation. I have studied trigonometry, but never used practically. Could you please explain the Cos and Sin function used in the second method ??
How remarkable! Just creative.
Actually I find that using Chart Title in place of the Textbox to display the center text isnt too bad too. It optimizes the object space too. (And I just spent minutes trying to group the textbox and the chart to no avail, didnt know about the trick to select the chart area first before creating a textbox)
Wow, both are very eye-opening. Who said trigonometry and geometry is useless after leaving school, lol!
I prefer your version because it's much simpler
Awesome stuff, Leila. I'll try your method. Immediately thought that it can be used for Dashboards or to visualize metrics.
Is it possible to have the color be dynamic? I mean, let's say if the number is below 70%, The progress bar would be Red. And then if greater than or equal to 70% but less than 90%, it would be Yellow or Amber. If greater than of equal to 90%, it would be Green.
Would conditional formatting solve this question?
Killer stuff Leila, killer stuff.
Glad you like it :)
Great video! Of the two methods, I prefer the look of Sam's. I have a couple places where I can use it, so I'm going to give it a try.
That's great Michael! Let us know if it worked out.
Hi, Very nice video 👍
Your every single lecture is always awesome..!!! Especially Chart tutorial, is always wonderful.
Waooo waooo . Very nice explanation. Very great video.
Glad you like our little circle fun :)
Great Video Liela
that was very good!
Great video as usual.
Can we use conditional formatting in that pie chart?
Like red colour for below 20%, orange upto 80% and green above 80%?
Yes - Create the data prep table with an If condition. So for example if we just have bad and good, then the bad series has this formula =IF(B2
@@LeilaGharani How about using 4 data for a single chart?
A1 = value if below 20
A2 = value if 20 - 80
A3 = value if 80 - 100
A4 = remainder
Example:
If 19, then A1 = 19, A2 = 0, A3 = 0, A4 = 81
If 35, then A1 = 0, A2 = 35, A3 = 0, A4 = 65
@@LeilaGharani please make a video in that regard
Just found your channel. Very useful.
To you have a session on how to create a progress pie to populate in a 2nd excel sheet once information is enter from the 1st sheet
Nice work
Thnx
This looks is really cool and all, but the only problem with this style is accuracy. You may find it confusing when it comes close to somewhere between 95% to 98%.
Microsoft developers should see this!
Yes it will be much more convenient than going through all this
Thank you so much my master Leila👍
Definitively the first one. The second one is nicer but show bigger progress than real, the extremities of the marker go beyond the doughnut: 97% looks like 100%
It`s wonderful! Thanks for video!
My pleasure. I'm glad you like it.
Option 2 is fabulous...until your sales people beat their goals...over 100%. so I threw in an if clause to the remainder formula =IF(1-B6
You're very welcome. That's a good call with the sales people there :)
Can u please make a automatically update column List of Income or Expenses from a Excel bank statement 🙏 for eg. If i write Salary the cell in GROUP Column shows INCOME..if Personal Exps. then GROUP Column shows EXPENSES...etc. correctly n automatically 🙏❤️
I would use power bi for data presentation.
If I have three slices to the pie. In progress, declined and completed. I want the declined to highlight red, the inprogress blue and the completed light green. (I know my color choice is awful)...but my problem is when I have three slices... and I add the border width, the slices overlap. I would want my "In Progress" to be the one which is bigger than the other two slices. But I get the Declined overlapping with my In progress...it's on top of my In progress bar. Is there a way to "Send to Back" the slice you don't want focus on? Your videos are amazing, thanks so much for making them so EASY to understand and follow!
Hi Leila,
Is there a more elegant/simpler way of doing Part II now that we are four years later?
thanks for this video , really amazing
Awesome!
I do prefer Sam's as it looks more smart, however; Leila's is much easier
Glad you like it. Always a good thing to have options :)
Hello, can you please help me out with a speedometer chart half circle values between -100% to +100% and 0% should be at 90° angle.
I prefer to use the one that comes ready with excel without complications. Because when I come back after one month and try to add something I wouldn't know what this is all about.
Good Job Leila.. Now I can't even add Excel in my resume 😰
Hi Leila, I have a much simpler version. I will share with you soon. All the best.
U r awesome 😊😊
Your colleague version is good
Nice! But... I think that, although it might be more aesthetically pleasing to some people, the second version is somewhat untrue since the blue sector will always be larger than the given value (by the radius of the dots) !? Yes I know that I'm being a boring, nit picking £#@
Thanks for your feedback Thomas. At least, it's some food for thought, right? :)
@@LeilaGharani Definitely!! Always good to have this knowledge. You never know when it will come in handy 👋
You could put a grey rounded circle on the start position (12 o'clock), this compensates for the end blue round (keeping the surface correct) and makes the graph look like it can "clip" into itself when you get to 100%.
Leila, have you ever heard rose diagram (used in geology)? Please show us how to do it please. @t
Thanks in advance
Your big fan
MS excel should add Rounded edge option to the graph to make life easy for all.
That's true :)
thank you, and thanks for Sam.
This has changed my work life. I do have a question though. Is there a way to make this application of the doughnut chart segmented into months, and each month show a different color for progress? For example, January met 100% of a goal - 1/12 of doughnut Green; February met 60% of goal, next 1/12th yellow...and so on. A doughnut monthly KPI chart.
Hi Mam, I want cell update in invoice automatically, cell value were enter update on another dataset or sheet by added new row.
Its very nice; but if you have to show 99% up to 96%; then the rounded ends touch each other.
Sam is way too smart for me!
Sam's nice tweak solution is to
- Add Start and End points as Filled Circles at a calculate points (Simple Trigonometry)
- Fix the graph so the circles are plotted at expected points
- Define the scale of each axis
Personally i prefer the simpler solution but i am fascinated with the math.
Me too. I would've never been able to come up with such a solution.
thanks...
NICE
ótimo conteúdo, muito útil o conhecimento aqui repassado.
Att, Gledson Programador Excel VBA
Can you make video about speedometer charts
I need to display the incremental progress on my finance sheet after every milestone invoice completion. How do I do it using excel chart ( Half Pie Preferably) e.g. Total Project amount is $100,000. Milestone 1 will be Q1 - 20,000 , Q2 - 30,000 and Q3 - 50,000.
I'll go with the first one
That's. genius. Thanks for posting.
Glad you like our little circle fun Sam :)
I want to understand the sine and cosine maths of it... also the 1.15 max and minimum.
It's the mathematical formula for a circle. www.mathopenref.com/coordparamcircle.html
Nice ; Impressive