@@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. 😊
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! ruclips.net/user/excelwizardinminutes?submission=1
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
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).
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!
@jacqueshollands5630 one idea is to add textboxes: Step 1: Set Up Your Data Input Values: Assume you have the following values: Cell A1: 5 (for the right bar) Cell A2: -5 (for the left bar) Cell A3: Create a formula in this cell to calculate the sum, for example: =A1 - A2 This will yield 10 for the sum of 5 - (-5). Step 2: Create Your Chart Insert a Chart - same as video Step 3: Insert Text Boxes Insert a Text Box: Go to the Insert tab on the Ribbon. Click on Text Box and draw the text box on your worksheet. Link the Text Box to the Sum: Click on the text box to select it. In the formula bar, type = and then click on the cell where you calculated the sum (A3). For example: =A3 Press Enter. The text box will now display the value of 10. Step 4: Position the Text Box Above the Right Bar Copy and Move the Text Box: Click on the text box, then drag it to position it above the right bar in your chart. You may need to resize the text box for better visibility. Format the Text Box (Optional): Right-click on the text box and select Format Shape to change the appearance as desired (e.g., background color, font size). Step 5: Repeat for Additional Bars For Additional Values: If you have more sets of values, repeat the above steps: Create a new cell for each new sum calculation. Insert a new text box for each sum and link it to the corresponding cell. Position each text box above the respective bar in the chart.
@jacqueshollands5630 one favour, plz do not hesitate to share the channel with anybody who is struggling with Excel: ruclips.net/user/excelwizardinminutes?submission=1 that will make my day :)
Thank you for this amazing tips, much appreciated I have a question though : would it be possible de modify the central point which is 0 ? For example, to have 20% as the standard rating ?
@hakimfilalibaba4679 i think there might be a workaround to add a line at 20%. (Havent tried it). It s a bit hard to do,i think i have done something similiar in my shorts (quick tips) at the bottom of my playlist where i have added the current date to a chart. Here's how u might try for this chart: Add a new data series to your data representing the 20% line. Add a single constant value of 20% for all categories *Select the new series and change its chart type to Line (in a combination chart) to represent a baseline at 20% %.
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.
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
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.
Have another look at the video. you have to switch those 2 columns in the data potentially. 1st try changing the name to anything and see where u ll get the name in the chart. Then you can just switch the data
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 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
Really helpful and exactly what I was looking for in a great looking chart. Thanks!
Great i helped ! 😊
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 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
This video was amazing and I did exactly what I needed
I just needed to add the % values to the chart
Hey Cara, I believe there is a comment below with the same question. try the answer i provided :)
Thank you it was delightful
😊
Thank you for this. Super helpful!!
Thanks Boss, it's very informative
Best trick, thanks so much! 🙌
Thank you sooo much :)
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!
ruclips.net/user/excelwizardinminutes?submission=1
This was great, thank you!
Very helpful. Thank you!
Welcome! 😊
Excellent!
Thanks for the feedback Kathy :)
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
Awesome, thank you
You 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
Nice!! Thanks for sharing! ❤
You are welcome! :)
Thank you ..very helpful video
Thanksss 😊😊
Thank you very helpful
Great! 😊😊
This is really really good. just in time
thank you very much
@@aliengg89 you are welcome :))
Thank u so much! Now I can do better graphs :)
U are welcome! :)
Brother you are awesome, thank you so much
Thank you Chetan! 😊
Great! Many thanks
Thanks Sir! :)
Great video!
Thanksss a lot! :))
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!
Excellent work!! 👏
Thank you! 😊
thank you so much!!!!!!!!!
You are welcome! 😊😊
Excellent video. How would one add data labels for the 2 neutral fields as one consolidated field?
@jacqueshollands5630 one idea is to add textboxes:
Step 1: Set Up Your Data
Input Values:
Assume you have the following values:
Cell A1: 5 (for the right bar)
Cell A2: -5 (for the left bar)
Cell A3: Create a formula in this cell to calculate the sum, for example:
=A1 - A2
This will yield 10 for the sum of 5 - (-5).
Step 2: Create Your Chart
Insert a Chart - same as video
Step 3: Insert Text Boxes
Insert a Text Box:
Go to the Insert tab on the Ribbon.
Click on Text Box and draw the text box on your worksheet.
Link the Text Box to the Sum:
Click on the text box to select it.
In the formula bar, type = and then click on the cell where you calculated the sum (A3). For example:
=A3
Press Enter. The text box will now display the value of 10.
Step 4: Position the Text Box Above the Right Bar
Copy and Move the Text Box:
Click on the text box, then drag it to position it above the right bar in your chart.
You may need to resize the text box for better visibility.
Format the Text Box (Optional):
Right-click on the text box and select Format Shape to change the appearance as desired (e.g., background color, font size).
Step 5: Repeat for Additional Bars
For Additional Values:
If you have more sets of values, repeat the above steps:
Create a new cell for each new sum calculation.
Insert a new text box for each sum and link it to the corresponding cell.
Position each text box above the respective bar in the chart.
@@EXCELWIZARDINMINUTES Perfect solution. Thank you kindly. Don't know why I didn't think of it 😂
@jacqueshollands5630 hahaha. I think this is better than trying to use data labels and changing the values 😅
@jacqueshollands5630 one favour, plz do not hesitate to share the channel with anybody who is struggling with Excel: ruclips.net/user/excelwizardinminutes?submission=1 that will make my day :)
Perfect
Thank you so much!:)
Super helpful
Thank you Jade ! :))
Thank you for this amazing tips, much appreciated I have a question though : would it be possible de modify the central point which is 0 ? For example, to have 20% as the standard rating ?
@hakimfilalibaba4679 i think there might be a workaround to add a line at 20%. (Havent tried it). It s a bit hard to do,i think i have done something similiar in my shorts (quick tips) at the bottom of my playlist where i have added the current date to a chart.
Here's how u might try for this chart:
Add a new data series to your data representing the 20% line.
Add a single constant value of 20% for all categories
*Select the new series and change its chart type to Line (in a combination chart) to represent a baseline at 20%
%.
excellent job
Thank u 😊😊
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.
Thank u so much
thankyouuuu
Happy to help 😊😊
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
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 know how to remove the negative percentages
How? Please share
what if you have inputs that are zero how can i still use this?
@@cozycatandme 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
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
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.
in my case, my disagree bar is on left side of strongly disagree :(
Have another look at the video. you have to switch those 2 columns in the data potentially. 1st try changing the name to anything and see where u ll get the name in the chart. Then you can just switch the data
Did not get it!