Perfect exactly what I was looking for and so easy to follow... I never knew you could copy into a chart like that...can't believe I didn't know that THANK YOU
Hey Chandeep- this is still the best target v actual video on the internet!-- Ive just built one of these coupled with dynamic arrays--its like totally awesome dude!!
This is fantastic, thank you! It will support my PhD data presentation where I have a mechanical limit that I need to show if it's over- or under-achieved across a series. I've been struggling for a day on this...your video looks perfect, so thanks again
This video has been very helpful. I would love to know how to create the same thing in a horizontal format (bars rather than columns). Is this possible?
Hi, I have build the same chart for the practice. However, when the numbers are changed for over-achieved, then in the chart it shows zero for series, Need your input only for new changed numbers. Thanks,
thanks a lot for such a wonderful information.. Still need your help on how to stack the last info on top of the dummy target . Basically struggling in selecting the base value on which th eoversale will be stacked.
hey Chandeep! Thanks for the video. I can't begin to explain how useful this chart is for the work I do. Quick question - what are the shortcuts you used at 03:49 to highlight from cell E39 to E25 and then to 'drag it down'? Thanks!
This tip is really helpful for quick analyze. Could you please share what if I have another set of this data and I want to compare their performance in the same? Thank you very much again.
Thank you for the useful video! I am trying to reproduce it. After the end, how can I show for example for the blue just the data labels that are overachieved and not all of them, many of which are 0, because they are not overachieved? For the reds as well - i want the data label to show only where we have reds, and not to all bars?
Great video, thanks, how to compare three KPIs like Sales, Gross profit, and net profit at the same time, since the secondary axis way in column chart can do only two KPIs at once? I want to compare all these three KPIs at once to show a portion of Sales at 100%, then GP out of it and then NP out of it?
Can we have a dynamic line for this.. let their be only one line showing consolidated actual/target and differential... once u select an agent from a slicer the bar should get changed showing the respective info
This chart is AWESOME very eye catching and brilliant!! Love it!! Thank You for sharing. I do have a question though. On 2010 Excel, under Data Label, it will show "2 Target Labels" due to one Target on Horizontal axes and the other Target on Secondary axis. How do I make the Target Label show only once? Thank You.
Hey first off.. sorry for replying so late. Can you send me your excel file at goodly.wordpress@gmail.com I'll take a look and fix the problem If you want to do it yourself then just right click on the data series (for which you want the data labels) and choose "Show Data Labels". It wouldn't appear for the one on the secondary axis Cheers Chandeep
I really like this chart, however, I was not able to get the data labels to work in the end. For some reason, the data labels only work for the secondary axis. I cannot get the chart to differentiate for the primary axis. As such, my data labels read "0" for the primary axis. Please help.
Hi Leon, to fix the problem 1. Please select the data in the spreadsheet. 2. Press Ctrl+1 to open the format cells box. 3. Then under number tab go to custom 4. In the Type box write the following code 0;; 5. Most likely your problem will be solved Thanks
Hi Chandeep, Excellent video !! Can you please guide on how this method would change, if we replace the "Sales Man" with "Months". So scenario is that I tried to replicate the same chart and calculation for Projects that I am tracking. However, I came across a weird situation, which is - if say for month of MAY, the target was 9 Projects and 1 of them got completed earlier (in APR) and remaining 8 was completed in MAY itself. So when we work out the calculation, there will be a deficit of 1 for month of MAY but its actually something which was completed before time and not that it missed the target. How to modify this chart for showing the target vs achieved per month, considering if anything gets closed before time shouldn't be shown as "target not met"
Goodly thank you for the response. Can you please tell me how I could use a marker horizontally? If you could just point me in the right direction I believe I can figure it out from there. I tried the dradient fill, but that didn’t work too well. Thanks, again!
Well done! This trick with =MAX is cool. I used to know only with IF... but one idea, that means this can be also used as =MAX(B1-A1;NA()) to get an "NA" errors for instance using on line chart without breaks in between?
How do you set up your under and above values if the targets are negative? I tried with MIN and it worked. However, if the targets move from negative to positive and the opposite, MAX and MIN do not work. Please provide clarification in this case if actual moves in opposite color. Thank you!
Super awesome video, i was looking for some classy target video and this came across, gives a new look to your work all together. Just one question, what font you used in that excel, if you recal?
Thanks man! Glad you liked it! Try using "Segoe UI" or "Gotham" font family! And check out this chart as well - www.goodly.co.in/threshold-chart-in-excel/
+Bosseau Marx Please make sure that none of the two axis (prim & sec) are deleted. It should not be a problem if you are doing it correctly. Would you be able to share your excel file with me - goodly.wordpress@gmail.com. I'll take a look :)
Perfect exactly what I was looking for and so easy to follow... I never knew you could copy into a chart like that...can't believe I didn't know that THANK YOU
Thanks Erica! See this video you'll find the tricks interesting- ruclips.net/video/fV6fdPYbgVI/видео.html&lc=Ugxg98WCGFXdEircJfV4AaABAg
THANK YOU OMG I've been looking for this everywhere!!!!
Hey Chandeep- this is still the best target v actual video on the internet!-- Ive just built one of these coupled with dynamic arrays--its like totally awesome dude!!
I'm from Brazil!
Thank you! Amazing this video and your explanation!
This is fantastic, thank you! It will support my PhD data presentation where I have a mechanical limit that I need to show if it's over- or under-achieved across a series. I've been struggling for a day on this...your video looks perfect, so thanks again
Glad it helped!
This saved my life 🤩🤩 I was always struggling with having multiple series, but now it will be easier with your trick. Thank you soooo muchhhhh
Happy to help Marwa!
Good work ..could you build your measurs step by step to understant well
This video has been very helpful. I would love to know how to create the same thing in a horizontal format (bars rather than columns). Is this possible?
How is this video 8 years old yet very relevant today?. Thanks for the video
Hi,
I have build the same chart for the practice. However, when the numbers are changed for over-achieved, then in the chart it shows zero for series, Need your input only for new changed numbers.
Thanks,
Thank you for this nice video, its really useful to me. May I know, how to make the same in Power Bi
Hi,
Very precise and to the point; pace is moderate, anyone can catch up; thank you Guru.
thanks.. m glad you found it helpful
Can this be done on a pivot table and make it dynamic with a slicer?
Hi.
I have been able to show the target not achieved but for sales overachived is showing part of the target and overachived together
More simple & detailed 👌
thanks a lot for such a wonderful information.. Still need your help on how to stack the last info on top of the dummy target . Basically struggling in selecting the base value on which th eoversale will be stacked.
I followed exactly your guide & managed to impress my boss..Thanks a lot..!!!
Glad it worked for you!
Excellent Video!!! could we do with a Pivot Chart? I tried copy and paste values as this example, but doesn't work
Great. Thanks. Is it possible in power bi ?
Hi
Is there way I can email you to show you the problem in the chart
hello, can you please make the spreadsheet available for download. thank you so much.
Hey, it's a clear explanation, thank you and keep posting new charts.
Hey, I am glad you loved it :)
hey Chandeep! Thanks for the video. I can't begin to explain how useful this chart is for the work I do. Quick question - what are the shortcuts you used at 03:49 to highlight from cell E39 to E25 and then to 'drag it down'? Thanks!
George Mathai hey Brother.. glad you liked it! The shortcut is Ctrl D
I HAVE to try this !!!!! Thank You.
for the marker no need to paste to change to line, go to marker, select built in and change to line
This tip is really helpful for quick analyze. Could you please share what if I have another set of this data and I want to compare their performance in the same? Thank you very much again.
Exactly what I am looking for, comprehensive, nice, helpful video
Thanks.👍👍
Very helpful and Nicely explained..👌
Quite an amazing teaching. Subscription rightly earned. Thank you.
Absolutely helpful. Thank you for the tips along the way.
Thank you for the useful video! I am trying to reproduce it. After the end, how can I show for example for the blue just the data labels that are overachieved and not all of them, many of which are 0, because they are not overachieved? For the reds as well - i want the data label to show only where we have reds, and not to all bars?
You did such an excellent job. This was very helpful. I really appreciate it. I am subscribing to your channel from now on.
Glad you liked it
excellent tutorial and graph.. thank you for sharing!
Great video, thanks, how to compare three KPIs like Sales, Gross profit, and net profit at the same time, since the secondary axis way in column chart can do only two KPIs at once? I want to compare all these three KPIs at once to show a portion of Sales at 100%, then GP out of it and then NP out of it?
Can we have a dynamic line for this.. let their be only one line showing consolidated actual/target and differential... once u select an agent from a slicer the bar should get changed showing the respective info
I love it! Great job, clear explanation
Coach Aries glad you liked it :)
Superb .... this made me WOW moment in my office
Glad it helped you Pavan.. Cheers!
I just used this bar chart on a report for work. Thank you!
Can you do the same chart in power BI
Thank you very much really appreciate .... The knowledge you shared with us ....
This chart is AWESOME very eye catching and brilliant!! Love it!! Thank You for sharing. I do have a question though. On 2010 Excel, under Data Label, it will show "2 Target Labels" due to one Target on Horizontal axes and the other Target on Secondary axis. How do I make the Target Label show only once? Thank You.
Hey first off.. sorry for replying so late.
Can you send me your excel file at goodly.wordpress@gmail.com I'll take a look and fix the problem
If you want to do it yourself then just right click on the data series (for which you want the data labels) and choose "Show Data Labels". It wouldn't appear for the one on the secondary axis
Cheers Chandeep
I have difficulty in adding data labels for actual targets in primaxy axis, it is selecting only secondary axis duplicate data
Thank you so much! I only wish i would have seen this a few weeks ago! Definitely subscribing to your channel sir.
Thanks..Glad you liked it !
I really like this chart, however, I was not able to get the data labels to work in the end. For some reason, the data labels only work for the secondary axis. I cannot get the chart to differentiate for the primary axis. As such, my data labels read "0" for the primary axis. Please help.
Hi Leon, to fix the problem
1. Please select the data in the spreadsheet.
2. Press Ctrl+1 to open the format cells box.
3. Then under number tab go to custom
4. In the Type box write the following code 0;;
5. Most likely your problem will be solved
Thanks
@@GoodlyChandeep No luck. Is there anyway I can email you the workbook?
@@leonamaryland shoot me a mail here - goodly.wordpress@gmail.com
Wow! I love this chart...this a really great tip! Many thanks !!!
Hi Chandeep,
Excellent video !!
Can you please guide on how this method would change, if we replace the "Sales Man" with "Months". So scenario is that I tried to replicate the same chart and calculation for Projects that I am tracking. However, I came across a weird situation, which is - if say for month of MAY, the target was 9 Projects and 1 of them got completed earlier (in APR) and remaining 8 was completed in MAY itself. So when we work out the calculation, there will be a deficit of 1 for month of MAY but its actually something which was completed before time and not that it missed the target.
How to modify this chart for showing the target vs achieved per month, considering if anything gets closed before time shouldn't be shown as "target not met"
Great video! I definitely learned a lot! Can something like this be done in a bar graph horizontally?
Yes.. the same logic can be applied to the horizontal bar chart
Goodly thank you for the response. Can you please tell me how I could use a marker horizontally? If you could just point me in the right direction I believe I can figure it out from there. I tried the dradient fill, but that didn’t work too well. Thanks, again!
Honestly a brilliant video, thanks kindly.
This was a life saver! Thank you!
Great to hear!
How to do that for several months like in pivot table?
This is awesome! Thank you so much!
Glad you like it Tammi !
Superb, I will use this in my new monthly dashboard
how to use it on power bi?
Awesome Tutorial!
I simply can't get the overachieved to work properly. The shaded area goes below my target line. Do you know what I'm doing wrong?
Can't say whats going wrong unless I take a look at your data. Can you send it ?
goodly.wordpress@gmail.com
Well done! This trick with =MAX is cool. I used to know only with IF... but one idea, that means this can be also used as =MAX(B1-A1;NA()) to get an "NA" errors for instance using on line chart without breaks in between?
Very neatly explained, thanks a ton !!!
Well done, very well explained and super helpful! :)
Adrian Gafa glad you found it helpful:)
WOW. neatly explained. Thanks!
Glad you liked it :)
Appriciated
Q: How can we remove value "0" from Chart ?
Very good - Greetings from Germany
Bien!!! Queremos más gráficos de ese tipo.
Nice but when dummy target value I put into secondary exis it going up... Pl let me know how that would be rectified.
Try checking the axis values (BOUNDS: max, min, UNITS: major, minor). Both should be the same for primary and secondary axes
Good stuff.. I created a dashboard using this template. Thank you!
Isabel Velasco hey I am glad it helped you. Let me know if you need more help. Cheers
I try it and made it but last Sales over not made it.
Hi, is it possible in power BI.
Video coming out soon :)
@@GoodlyChandeep waiting for this video in power bi
How do you set up your under and above values if the targets are negative? I tried with MIN and it worked. However, if the targets move from negative to positive and the opposite, MAX and MIN do not work. Please provide clarification in this case if actual moves in opposite color. Thank you!
Hi Yordan, Can you please send me a dummy of your data to goodly.wordpress@gmail.com
Absolutely. Thank you very much.
Greetings, Goodly. I sent you the dummy chart. Thank you for your direction.
Super awesome video, i was looking for some classy target video and this came across, gives a new look to your work all together. Just one question, what font you used in that excel, if you recal?
Thanks man! Glad you liked it! Try using "Segoe UI" or "Gotham" font family! And check out this chart as well - www.goodly.co.in/threshold-chart-in-excel/
Anyone help to make this in tableau?
I was able to create a good chart with this variance chart, thanks
Vinod Raja I am happy it worked for you! Cheers
Thank you so much! Brilliant work!
Very useful!! thanks fir the video
Miguel Manco glad you liked it :)
I am working in 2010. When I try to stack the target on the secondary axis, it stacks it on the primary axis, and I can't get it right. Any ideas?
+Bosseau Marx Please make sure that none of the two axis (prim & sec) are deleted. It should not be a problem if you are doing it correctly. Would you be able to share your excel file with me - goodly.wordpress@gmail.com. I'll take a look :)
I have Excel 2010 so i can't copy & paste the shape I made. Is there an alternative?
Hi Michelle, You can copy and paste the shape. It has nothing to do with the version of Excel. Let me know if you get stuck
Thanks for this teach
Just one thing if you tweak the values a little it does not work. Can you please check and get back
Can you give me an example of the values that you inputting? You can write me an email - goodly.wordpress@gmail.com
Chandeep Chhabra will send the file I'm working with
Good tricks. Thanks
Excellent!!!
Thanks!
Legend of targret not showing. How to put the line?
Thank you so much
Fantastic!!
Thanks!
Great chart. Have you ever attempted this in Pivot Charts? I have tried to recreate with no success. Formatting is not as straightforward.
Hey Mark.. Glad you liked it. Would you send me your chart data and I'll try and work on it using a pivot chart. goodly.wordpress@gmail.com
I would just recreate using your same data table. Trying to make a somewhat dynamic pivot chart.
I would just recreate using your same data table. Trying to make a somewhat dynamic pivot chart.
Well done. Thank you for this.
thank you Grace!
Nice video with good content!!! :)
But you didn't teach how to add data lables to it.
Thank you
You need to unify the axis value and remove one of them
Gentleman - thanks!
Fair Enough..... :) Too good man.. thumbs up.
Thank You!!!!!
😇
Such a nice trick!!
THANK YOU SO MUCH!!!!
Glad it helped!
I like that one so easy to read
Great video! Thanks for sharing :-)
Glad you liked it Alex. Let me know if you have any more questions around charts/excel
Here is my email - goodly.wordpress@gmail.com
Thanks very interesting
You are awesome!!
Amazing. Tanks.
amazing
Nice
Terimakasih
Great! Nice video
Thanks glad you liked it