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. 😊
@@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.
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).
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! ua-cam.com/users/excelwizardinminutes?submission=1
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 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.
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.
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.
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.
@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
@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
Thank you for this. Super helpful!!
This was great, thank you!
Thanks Boss, it's very informative
Thank u so much
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!😊😊
Learned a few tricks AND it looks awesome. Thanks!
Thanks for the kind words Juan Emilio! 😀
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
Great! Many thanks
Thanks Sir! :)
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
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.
Excellent work!! 👏
Thank you! 😊
Very helpful. Thank you!
Welcome! 😊
Awesome, thank you
You are welcome!
Best trick, thanks so much! 🙌
Thank you sooo much :)
Nice!! Thanks for sharing! ❤
You are welcome! :)
Thank u so much! Now I can do better graphs :)
U are welcome! :)
Super helpful
Thank you Jade ! :))
thank you so much!!!!!!!!!
You are welcome! 😊😊
Great video!
Thanksss a lot! :))
Thank you very helpful
Great! 😊😊
Perfect
Thank you so much!:)
Thank you ..very helpful video
Thanksss 😊😊
Brother you are awesome, thank you so much
Thank you Chetan! 😊
excellent job
Thank u 😊😊
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
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!
ua-cam.com/users/excelwizardinminutes?submission=1
thankyouuuu
Happy to help 😊😊
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 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
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.
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
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
I know how to remove the negative percentages
How? Please share
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!