Dynamic Sales Score Meter Chart with Form Control Combo box and List box
ฝัง
- เผยแพร่เมื่อ 18 เม.ย. 2020
- #Speedometer #FormControls
In this video, you will learn how to create a dynamic sales score meter chart. In this chart we have displayed the sales core on a speedometer chart. We have displayed the sales rating also as Worst, Bad, Average, Good and Best. We have created 2 different version of this chart. We have used Form control combo box and list box to make it dynamic.
Please download the practice file from below given link:
www.pk-anexcelexpert.com/dyna...
Download our free Excel utility Tool and improve your productivity:
www.pk-anexcelexpert.com/prod...
See our Excel Products:
www.pk-anexcelexpert.com/prod...
Visit to learn more:
Chart and Visualizations: www.pk-anexcelexpert.com/cate...
VBA Course: www.pk-anexcelexpert.com/vba/
Download useful Templates: www.pk-anexcelexpert.com/cate...
Dashboards: www.pk-anexcelexpert.com/exce...
Watch the best info-graphics and dynamic charts from below link:
• Dynamic Graphs
Learn and free download best excel Dashboard template:
• Excel Dashboards
Learn Step by Step VBA:
• VBA Tutorial
Website:
WWW.PK-AnExcelExpert.com
Facebook:
/ pkanexcelexpert
Telegram:
t.me/joinchat/AAAAAE2OnviiEk5...
Pinterest:
/ pkanexcelexpert
************ Suggested Books ********
VBA: amzn.to/2TMMikX
Excel Dashboard: amzn.to/2WZi2Fj
Power Query: amzn.to/2Ibd7xR
Power Pivot and Power BI: amzn.to/2DCg8BB
Exam Ref 70-778 (Power BI): amzn.to/2GnWYTN
************ My Stuff ***************
Mic : amzn.to/2TLnF88
Video Editor: screencast-o-matic.com/refer/...
Such an amazing video PK. I love your videos.
Hi PK. I love this video. You did not leave ANYTHING to chance misinterpretation. You created a THOROUGH, CLEAR and easy to follow video on a fairly complex topic. Thank you!
PK, you are the all round best Excel expert i have seen. Your teaching methods is wow!! Thanks for the priceless help your tutorials offer and the amazing work.
Wonderful, I never think this would be possible.. pure magic. I had to wacht several times to complete my chart. Thanks
Thanks for your valuable feedback
Very Simple.
You are truly and expert.
This is very beautiful chart. thank you
Thanks and welcome
Merci beaucoup pour la vidéo
Excellent presentation! You're an Excel chat guru.
Thanks
Thank you very much. You are the best 👌
Thanks for your valuable feedback
Wow..... new learning again. Thanks so much, Sir!..... I've been waiting for this kind of topic.
Most welcome!
Perfect very helpful, thank you
Most welcome🙏
Your explanation are so easy to understand. Instead just try to download templates this learning is very interested. Thank Yo!
Glad it was helpful!
Super duper Excel format ......
Most powerful and popular on TH-cam...
Kindly create Excel with vba and more design for SCHOOL MANAGEMENT....
Thanks for your valuable feedback. I will definitely try to make such video
Very Good !!!
Thanks for your valuable feedback
Very good... Thanks
Thanks for your valuable feedback
Thank you very much
Welcome
Amazing 👍
Thanks for your valuable feedback
I take this Godsend opportunity to thank you for the good work you are doing to educate. Please I need your assistance. Make for me a Score Meter Chart like the Dynamic one but mine is for my students and let it have the following parameters-0%-39% Fail with Grade E, 40%-49% Fail with Grade D, 50%-64% Pass with Grade C, 65&-74% Pass with Grade B (Credit), 75%-100% Pass with Grade A (Distinction). I will really appreciate
Beautiful
Thank you! Cheers!
Great sir
Thanks🙏
wow, beautiful chart and well explanatory. what if i have negative values and positive values combine, how will i go about it?
Excellent
Thank you so much 😀
Nice work
Thanks 🙏
Nice 👍
Thanks for your valuable feedback
Nice, ,,,,,,, we want to a video or series that Will show when we make a transaction it will automatic update ledger, trial balance, balance sheet.
Thanks for your valuable feedback. I will definitely try to make such video
Very Interesting sir…. 👍💐👌But when we have number name its very difficult to pull out the name. So kindly make it searchable drop down.
😍😍😍
Nice
Thanks for your valuable feedback
great work from Borneo Island, Malaysia. would like to seek your expertise whether we can change combo box to text box in which user input the key value to lookup and show at meter chart. Reason being we have long list if using combo box. appreciate your comment, thanks.
Hi, you can use the INDEX, MATCH function or VLOOKUP function for the cell "A1" that express the row number corresponding to the name of the employer. As seen in the video from 24:10 to 25:10.
For vLookup function, be sure to add a helper column on the right of the "Employee Name" by numbering 1,2,3,4,5, etc downwards until "14" where the last name is "william", how it looks like is as shown in vid 24:30 to 24:40 or 25:00. This is so, that when you reference your lookup value in the cell you type in the name (must be exactly; Capital and small letters as there are, gaps and no space accordingly) with the table array that has all the answers you want inside, type "2" for "Col_index_num", "False" for [range_lookup]
Then, the rest is as what PK has. just that you reference to the number that would have been generated with the combo or list down box.
I would recommend the dropdown list even though you may have a large list since typing in requires one to be focus. The lookup value has to be exactly what it is in the table array, hidden spacings or differences in capital or small letter will result in "#NA", "No result avaliable" to pop up.
Videos that teaches you INDEX, MATCH & INDEX function or VLookup function; as follows, courtesy of excelisfun:
How to use Vlookup and INDEX MATCH function
1) th-cam.com/video/yFH01fuVyGs/w-d-xo.html (From 00:00 to 03:20), also omit the "&" in the formulas since we are dealing with one search value.
Complimentary videos for extracting datas with two and more way values,
1) th-cam.com/video/TaLc6ZToY3Q/w-d-xo.html
2) th-cam.com/video/tbgmC2u-VfI/w-d-xo.html (VLookup with match using row and column lookup values to get answer)
3) th-cam.com/video/F9UwNmLUHNo/w-d-xo.html (This teaches you have to extract information even with duplicate values).
Or click on formula when typing in the cell to allow microsoft's help bar to pop up on the right hand side.
Hey PK. A great tutorial. Thanks for taking us through it. I have a question though. How do I make the needle, or indicator, show on top of the slices? Is it possible, as I see yours doesn't do that. Other tutorials you have done does show it on top. Any advice would be appreciated.
Hi, you will have to head to "Chart Tools --> Format" tab on the header tabs right at the top of excel, on the left, there be a dropdown box, select (Series "Ratingscolor" or what you have named for the color coding pie chart), then select "Format Selection", then on the right, the selection box shows "Format data series", select the dropdown for "Series options", select (Series "Ratingscolor"), for the Pie explosion, give a bigger percentage than the current 0% with the goal of selecting the indicator below it.
explode the ratings until you observe that the indicator is reveal from the colors, Now have a percentage bigger than the 0% that was set in the "indicator" cell to select the black needle. Then, right click, select "Change Series Chart Type", order is important, unselect "Secondary Axis" for "Ratingscolor" first, then head to "sales score", select "Secondary Axis" for the needle to pop up to the top most. Then, the "Angle of first slice" will reset to 0%, you will have to set it 270%.
if you want the arrow to head to the back again, you can't simply unselect it, you will have to Unselect "Secondary Axis" for the "sales score" then select "Secondary Axis for "Ratingscolor" again with "Ratings" remaining as secondary axis throughout. The "Angle of first slice" will reset to 0%, you will have to set it 270%.
If you cant select "Sales Score" to get to 270% for the slice, head to "Chart Tools --> Format" tab on the header tabs right at the top of excel, on the left, there be a dropdown box, select (Series "Sales Score" or what you have named for the color coding on the indicator), then select "Format Selection", then on the right, the selection box shows "Format data series", select the dropdown for "Series options", select (Series "Sales Score") then have the slice set to 270%.
If this doesn't work, then you have to play around with the "Change Series Chart Type" and see which combination gets the needle to be above. Whichever the case, the "Angle of first slice" might reset to 0%, you will have to set it 270%. also to select the various series, follow the step that shows how to select the "Series Options" as stated in the previous paragraph.
Also, reset the pie explosion back to 0% for the "ratingscolor" should you see that the pie colors are shrinked.
Thanks.
Hi Pk
As commented and mentioned previously, the last slice (Best) shows white and not green. Why is it like that??
Also when we change the percentage, all other slices sometimes also gets change to green,why?? Please guide
Hello Pk; I have a question about power BI (edit mark) does not display on my app.what I have to do to display it.
Hi sir, I didn’t get the option of doughnut size tweak option. I had just got the first 2 options and I tried it on excel 2021 version. In Which version have you done this ?
How we can use one dropdown list to connected with multiple charts
Would it be possible to have a gauge (or speedometer) chart...using doughnuts similar to your speedometer...but that not only show the 0% - 100% range (horizontally, where the 0% is far left and the 100% is the far right of the gauge)...but to also extend the gauge to include negative values (that would extend the gauge to the left, going backwards...making a circle below the horizontal axis) as well as values that exceed 100% (and would go to the right, going forwards...making a circle below the horizontal axis) that currently stops at 100%?
How I can link this meter chart with Power Point, so that I will have similar feel like using meter chart in Excel?
I have question : In a donut chart or pie chart why we click on secondary axis at times i feel stuck when it comes to chart visibility please help
Hi Pk
Thank you for this vdo.
however, there is a small doubt and limitation in this vdo
if you would have observed, then at 23:21 mins, while filling in the color for BEST part, as 50% transparent green, its white when we make 90%. so we need to fill in manual color. but while again making percentage change to 65% for example, either the previous color is spreading entirely back to GOOD slice as well.
even later when you make 30%, the entire slice till average is getting GREEN. this seems to be a error or limitation here.
requesting you to kindly guide here.
Hi, do check if The "Angle of first slice" has been reset to 0%. You will have to set it 270% if the relocation of the formula from Chart V1 to that of Chart V2 has cause such reset. Also, the formula from 18:00 mins to 21:15 mins is as what he has to have the ratings shown as per the video.
Dear Sir
There's any Android app can run excel macro vba file?? If "yes", please I need.
Thank you
Hi sir if you copy and paste then how can a new learner will know to create those data table
Hello Sir,
I have sent a query over mail however no response. I need your help with VBA in MS ACCESS. Please reply
Sir i can unable to connect sales score together
Sir plz tell me how to solve it
can you give us the file please
Sir, Could you please help me ..as while adding the data for Colour Ratings and Ratings...i always get the Colour Ratings on the top, due to which i am not able to move further, Please help me on this.....
I have a similar issue here, have you found a solution?
Hi, perhaps you meant to select the various pies and doughnut chart that is overlapping each other.
To do so, select the various Series "ratings", "ratingscolor" or "Sales Score", head to "Chart Tools --> Format" tab on the header tabs right at the top of excel, on the left, there be a dropdown box, select (Series "Ratingscolor" or what you have named for the color coding pie chart), then select "Format Selection", then on the right, the selection box shows "Format data series", select the dropdown for "Series options", select (Series "Ratingscolor"), for the Pie explosion, give a bigger percentage than the current 0% with the goal of selecting the indicator below it.
Explode the ratings until you observe that the indicator is reveal from the colors, Now have a percentage bigger than the 0% that was set in the "indicator" cell to select the black needle or to select the doughnut. As a work around to select the stuff behind the pie chart that has now been unfill but still unable to select the doughnut or needle.
Sir how can we insert devloper tab in my excel table
Go to the office button than click the excel options than u can see in popular baar and top options for the working with excel and click the 3rd option show developer tab in the ribbon
I have the same problem. The Doughnut is behind the pie chart. I can't select it and format it.
were you able to solve this problem ?
Hi, you will have to head to "Chart Tools --> Format" tab on the header tabs right at the top of excel, on the left, there be a dropdown box, select (Series "Ratingscolor" or what you have named for the color coding pie chart), then select "Format Selection", then on the right, the selection box shows "Format data series", select the dropdown for "Series options", select (Series "Ratingscolor"), for the Pie explosion, give a bigger percentage than the current 0% with the goal of selecting the indicator below it.
Explode the ratings until you observe that the indicator is reveal from the colors, Now have a percentage bigger than the 0% that was set in the "indicator" cell to select the black needle or to select the doughnut. As a work around to select the stuff behind the pie chart that has now been unfill but still unable to select the doughnut or needle.
You wanna know how to have the needle at the top, then follow through below as i reply to a post above yours.
For having the indicator at the top,
head to "Chart Tools --> Format" tab on the header tabs right at the top of excel, on the left, there be a dropdown box, select (Series "Ratingscolor" or what you have named for the color coding pie chart), then select "Format Selection", then on the right, the selection box shows "Format data series", select the dropdown for "Series options", select (Series "Ratingscolor"), for the Pie explosion, give a bigger percentage than the current 0% with the goal of selecting the indicator below it.
explode the ratings until you observe that the indicator is reveal from the colors, Now have a percentage bigger than the 0% that was set in the "indicator" cell to select the black needle. Then, right click, select "Change Series Chart Type", order is important, unselect "Secondary Axis" for "Ratingscolor" first, then head to "sales score", select "Secondary Axis" for the needle to pop up to the top most. Then, the "Angle of first slice" will reset to 0%, you will have to set it 270%.
if you want the arrow to head to the back again, you can't simply unselect it, you will have to Unselect "Secondary Axis" for the "sales score" then select "Secondary Axis for "Ratingscolor" again with "Ratings" remaining as secondary axis throughout. The "Angle of first slice" will reset to 0%, you will have to set it 270%.
If you cant select "Sales Score" to get to 270% for the slice, head to "Chart Tools --> Format" tab on the header tabs right at the top of excel, on the left, there be a dropdown box, select (Series "Sales Score" or what you have named for the color coding on the indicator), then select "Format Selection", then on the right, the selection box shows "Format data series", select the dropdown for "Series options", select (Series "Sales Score") then have the slice set to 270%.
If this doesn't work, then you have to play around with the "Change Series Chart Type" and see which combination gets the needle to be above. Whichever the case, the "Angle of first slice" might reset to 0%, you will have to set it 270%. also to select the various series, follow the step that shows how to select the "Series Options" as stated in the previous paragraph.
Also, reset the pie explosion back to 0% for the "ratingscolor" should you see that the pie colors are shrinked.
Thanks PK for the hardwork. I know it be difficult to reply many people. Hoped that i can be of service.
how to create this chart on a scale from -100% to 100%
please tell me if doughnut is hiding under pie what is the solution after create it
same problem
same problem
Hi, you will have to head to "Chart Tools --> Format" tab on the header tabs right at the top of excel, on the left, there be a dropdown box, select (Series "Ratingscolor" or what you have named for the color coding pie chart), then select "Format Selection", then on the right, the selection box shows "Format data series", select the dropdown for "Series options", select (Series "Ratingscolor"), for the Pie explosion, give a bigger percentage than the current 0% with the goal of selecting the indicator below it.
Explode the ratings until you observe that the indicator is reveal from the colors, Now have a percentage bigger than the 0% that was set in the "indicator" cell to select the black needle or to select the doughnut. As a work around to select the stuff behind the pie chart that has now been unfill but still unable to select the doughnut or needle.
You wanna know how to have the needle at the top, then follow through below as i reply to a post above yours.
For having the indicator at the top,
head to "Chart Tools --> Format" tab on the header tabs right at the top of excel, on the left, there be a dropdown box, select (Series "Ratingscolor" or what you have named for the color coding pie chart), then select "Format Selection", then on the right, the selection box shows "Format data series", select the dropdown for "Series options", select (Series "Ratingscolor"), for the Pie explosion, give a bigger percentage than the current 0% with the goal of selecting the indicator below it.
Explode the ratings until you observe that the indicator is reveal from the colors, Now have a percentage bigger than the 0% that was set in the "indicator" cell to select the black needle. Then, right click, select "Change Series Chart Type", order is important, unselect "Secondary Axis" for "Ratingscolor" first, then head to "sales score", select "Secondary Axis" for the needle to pop up to the top most. Then, the "Angle of first slice" will reset to 0%, you will have to set it 270%.
if you want the arrow to head to the back again, you can't simply unselect it, you will have to Unselect "Secondary Axis" for the "sales score" then select "Secondary Axis for "Ratingscolor" again with "Ratings" remaining as secondary axis throughout. The "Angle of first slice" will reset to 0%, you will have to set it 270%.
If you cant select "Sales Score" to get to 270% for the slice, head to "Chart Tools --> Format" tab on the header tabs right at the top of excel, on the left, there be a dropdown box, select (Series "Sales Score" or what you have named for the color coding on the indicator), then select "Format Selection", then on the right, the selection box shows "Format data series", select the dropdown for "Series options", select (Series "Sales Score") then have the slice set to 270%.
f this doesn't work, then you have to play around with the "Change Series Chart Type" and see which combination gets the needle to be above. Whichever the case, the "Angle of first slice" might reset to 0%, you will have to set it 270%. also to select the various series, follow the step that shows how to select the "Series Options" as stated in the previous paragraph.
Also, reset the pie explosion back to 0% for the "ratingscolor" should you see that the pie colors are shrinked.
Thanks PK
when i change Ratings to Doughnut and select it as secondary it disappears
Hi, you will have to play with the "Change Series Chart Type" since the pie chart for the "ratingscolor" is set as "secondary axis" probably, causing it to be at the top. Unfill the colors first as shown by PK to reveal the "ratings" in Doughnut form behind it.
Also, to select the various Series "ratings", "ratingscolor" or "Sales Score", head to "Chart Tools --> Format" tab on the header tabs right at the top of excel, on the left, there be a dropdown box, select (Series "Ratingscolor" or what you have named for the color coding pie chart), then select "Format Selection", then on the right, the selection box shows "Format data series", select the dropdown for "Series options", select (Series "Ratingscolor"), for the Pie explosion, give a bigger percentage than the current 0% with the goal of selecting the indicator below it.
Explode the ratings until you observe that the indicator is reveal from the colors, Now have a percentage bigger than the 0% that was set in the "indicator" cell to select the black needle or to select the doughnut. As a work around to select the stuff behind the pie chart that has now been unfill but still unable to select the doughnut or needle.
Thanks.
What does happen when I insert value up 100%?
It will not work for more than 100%
@@PKAnExcelExpert How I can do if I need to insert a restriction when the value is more than 100%, then diplay 100%
Othe question, I do not have the option "programmer" there is alternative option to configurate the selection data?
going through the comments, you only answer the complements, but when there is something wrong with your video and people have questions about it you don't answer.. ill unfollow you. have a good day