034. A better way to create Charts for SURVEY RESULTS in EXCEL
ฝัง
- เผยแพร่เมื่อ 25 ส.ค. 2024
- In this lesson, I want to show you how to create Charts for SURVEY RESULTS in EXCEL. You will learn a new type of chart to display survey results. With a few tricks, you will improve the quality of the discussion around the data you have collected.
Channel content: Excel, excel tutorial, Microsoft excel, learn excel, how to use excel, excel basics, basic excel, ms excel, office 365, excel for dummies, excel functions, learn excel basics, microsoft excel for beginners, excel course, Mr Excel, excel help, microsoft excel tutorial, excel tricks, excel tips, excel guide, how to in excel, tricks in excel, tips and tricks, pivots tables, excel formulas, advanced techniques for excel.
#Excel #MsExcel #Chart
![ทางเดินชีวิต - ธีเดช ทองอภิชาติ x อ.ไข่ มาลีฮวนน่า [Official MV]](http://i.ytimg.com/vi/1f3wcG16dd4/mqdefault.jpg)
![[UNCUT] The Loyal Pin ปิ่นภักดิ์ EP.4 (2/4)](http://i.ytimg.com/vi/MN-p1DHjPtI/mqdefault.jpg)
Learned a few tricks AND it looks awesome. Thanks!
Thanks for the kind words Juan Emilio! 😀
I had another way of creating these charts but this was much simpler. Thanks for sharing!
Thanks Mike! Am interested to know the other way
@@EXCELWIZARDINMINUTES My way was not as efficient. I didn't think to put a negative sign in front of the disagree values. To make this work and not look weird I had to first convert everything to percentages. Then I had to create two new data series that would add empty space to the beginning and end of each bar. This was done to better center the bars within the chart, otherwise the chart was very lopsided since all the values are positive numbers. (don't worry if this doesn't make sense, your way was easier and got a very similar result).
One thing I did that wasn't complicated and would be a nice addition to your chart is add markers at the end of each bar with data labels to show the averages. I made these appear differently for the min and max values for a little more visual interest. I'm pretty confident you know how to do this but I'm happy to explain if it would help.
One last trick that I find helpful. If you're using Office 365, dynamic arrays can speed up your formulas. E.g., instead of typing a formula and copying it to other cells (=IF(B4=MAX(B4:B10),B4:B10), you can use a dynamic reference once and excel will apply the formula to the appropriate cells (=IF(B4:B10=MAX(B4:B10),B4:B10). This saves having to copy or fill a formula any time you change something and doesn't require locking a reference. In your video, you could also use this to put a negative sign in front of numbers (e.g., = -A2:E10) which saves having to add the negative sign by hand.
Thanks again for the video, I learned a couple nice tricks.
Thank you very much! Merci beaucoup pour cette vidéo!👏You helped me make a lovely Likert scale analysis for my University final thesis! Wishing you all the best. Have a great year 2024. 😊
De rien! 😊😊 have a great 2024 and hopefully my other videos will help as well
THANK YOU SO MUCH ❤ I’ve been struggling with this and you’ve made more sense than a university lecturer! I’ve liked and subscribed
U r welcome!😊😊
Thank you for this. Super helpful!!
Thank you very very much. I would not have done it alone. It helped me with my university Thesis project!
Oh woow! I am 😊! Good luck for defending the thesid
This is the best Excel Tutorial I've ever seen on youtube. Straight forward but not missing any details! Thank you for your advice!
However, I do have one question. Have you ever managed to get the labeling of the rows over the bars?
@timobllscn3954 hello! I have not tried it but try 1) manually moving them. U grab a label, keep the mouse pressed, them move the label 2) there is an otion to change the place of the labels. Have a look at the options, i am sure u ll find it
This was great, thank you!
Awesome, thank you
You are welcome!
Thanks Boss, it's very informative
Best trick, thanks so much! 🙌
Thank you sooo much :)
Thank you very helpful
Great! 😊😊
Thank you ..very helpful video
Thanksss 😊😊
This is really really good. just in time
thank you very much
@@aliengg89 you are welcome :))
Nice!! Thanks for sharing! ❤
You are welcome! :)
Very helpful. Thank you!
Welcome! 😊
Thank u so much! Now I can do better graphs :)
U are welcome! :)
A really nice video! Thank you. The only question I have concerns the ability to show the data values and whether you have a shortcut for displaying the values associated with the neutral midpoint (given that it's split between two fields).
What you could do is have 1 dataset for the chart and another dataset to show the values
Great! Many thanks
Thanks Sir! :)
Thank you very much. This is an excellent demo. I would like to know how to add the data labels as percentage.
This is a bit tricky.
1) Add 5 columns next to your 6 columns used for the chart (SD, D, N, A, SA)
2) Transform your number into %. for example, if you have 100 disagree, 50 neutral and 50 agree, you can make them 50, 25,25
3) Add Data Labels to your chart (there is a + sign when you click on the chart)
4) Remove the Labels for 1 of the neutral bars on the chart by clicking on them and pressing delete
5) For each set (example, Disagree), click on them, under formatting, under the 3 bars, you will find label options. Under Label contains, select value from cells and select the cells where you have the calculated numbers. Unselect the other options such as "Value"
That should do it. I hope you make it!
Please feel free to share the channel with others to spread the knowledge!
th-cam.com/users/excelwizardinminutes?submission=1
thank you so much!!!!!!!!!
You are welcome! 😊😊
Perfect
Thank you so much!:)
Great video!
Thanksss a lot! :))
Super helpful
Thank you Jade ! :))
Brother you are awesome, thank you so much
Thank you Chetan! 😊
Excellent work!! 👏
Thank you! 😊
excellent job
Thank u 😊😊
You deserve a million subs
That s so nice of you! Please spread the word to colleagues and friends 😇 i m tryin to help as many ppl as possible with excel. I know excel can be such a headache!
Thank u so much
thankyouuuu
Happy to help 😊😊
Thank you!!!! This helped me out for a presentation! What If you have 'blank' data to contend with?
How about cleaning the data before putting them on a chart. This way the blank data are excluded
My Neutral column points to the agree section. The neutral column is not in the middle of agree and disagree like yours. What did I do wrong?
There is a place in the video where we reverse the columns. U have to change the order in the dataset. I think u might have omitted this step
Thank you so much !! it was very helpful. Only difference in my chart is that the x axis has numbers of participants instead of percentages. How can I fix that?
U can calculate what 20%, 40% etc is in terms of number of ppl and manually change the labels of the x axis. Eg 20% could means 30 ppl. So you click on 20% on the graph and change the label.
what if you have inputs that are zero how can i still use this?
@@notebynote3 it will still show on the graph. On the place where there is 0, the bar will not be visible. So i guess it still works
I know how to remove the negative percentages
How? Please share
Very useful thank you, however, i cant get the axis line (the black vertical, minute 5.40 in the video)? When I click, becuase there are x2 Netural, i get two lines? Any idea on how to rectify this please?
The graph is a stacked chart with positive and negative numbers. Regardless if you have 2 data points called Neutral, you should get 1 line as there is only 1 x axis. My hypothesis is that you are doing 1 of the steps wrong or omitting a step. For example, you might select the wrong graph type or forget to click on a button. What you could do is try to make a graph like this with only 2 columns (e.g., only the 2 neutral columns): 1 with positive numbers and 1 with negative numbers. This way, it simplifies it and you catch the error.
also, you might be trying to color the gridlines instead of the axis
I reversed the columns but still the strongly disagree and neutral are in the wrong place. Is there some other way to reverse them so neutral is in the middle?
I am thinking what you might have done wrong. Maybe u missed a step. Try to do it again step by step to see where u have an issue. Try also to switch the titles and see what happens.
@@EXCELWIZARDINMINUTES thanks for the quick reply! I figured out that by trying to change the legend to display in the correct order with strongly disagree at the far left, it completely counteracted the order of the table.
How do I make this with a don’t know/unsure answer option in addition to all these
Option 1: you recalculate the % without the dont know and make a graph with only the ppl who know
Option 2: same way but u will have to decide where to put this answer (ie where to draw the middle line in the chart)
I prefer option 1 with another chart thay shows the percentage of ppl who said don't know.
@@EXCELWIZARDINMINUTES thank you so much. I can’t wait to impress my manager this coming Monday. Graphs are looking lovely
@norahmutale4391 welcome! :) feel free to check out other areas. They might come in handy and save u lots of time! And of course, it would be great if you could share the channel link if anybody is interested
I’m back. How do I get rid off the negatives
@norahmutale4391 u have to folloe the steps in the video. What i suggest id to start with dummy numbers and replicate exactly what is shown. Then u can do it on ur own data
Did not get it!