Excel Variance Charts: Actual to Previous Year or Budget Comparisons
HTML-код
- Опубликовано: 30 мар 2017
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Discover how to create dynamic column charts in Excel that automatically update colors based on data changes. This tutorial is perfect for anyone looking to enhance their Excel skills, especially in visually representing sales data. Here's a quick overview of what you'll learn:
⬇️ Grab the workbook from here: pages.xelplus.com/variance-ch...
Dynamic Color Coding: Learn how to set up Excel so that positive changes in data are shown in green and negative changes in red, without manually coloring each data point.
Synchronized Sales Data Charts: Find out how to display actual sales data alongside the change from the previous year, with both charts perfectly aligned for easy comparison.
Step-by-Step Guide: Follow clear, easy steps to set up your charts from scratch, including removing unnecessary elements for a clean, professional look.
🎓 Get access to the complete Excel Dashboard Course: www.xelplus.com/course/excel-...
Colorful Data Labels: Not just the bars, but also learn how to change the color of the data labels based on their position relative to the axis.
Conditional Formatting of Charts: Master the art of conditional formatting within charts to make your data more insightful and visually appealing.
Custom Number Formatting: Dive into custom number formatting to control the display and color of your numbers directly in Excel cells.
Practical Examples: Watch as we demonstrate these techniques using actual sales data, making it easier for you to apply these skills in real-world scenarios.
In this video I show you how you can use conditional formatting in Excel Column or Excel Bar charts.
I also show you how you can conditionally format the data labels in Excel graphs to show a different color if the values are positive to when the values are negative.
The technique in the video shows a variance column chart but it works in the same way for a bar chart.
This technique works for Excel 2010, Excel 2013 and Excel 2016. For Excel 2007 and below, you need to use a different technique. You will need to create two additional series, one for positive number and another for negative numbers and format each series accordingly - and also overlap these by 100%.
Part 2 - Better Variance charts • How to Create Variance...
Part 3 - Arrow Variance Chart • How to Create Dynamic ...
★ My Online Excel Courses www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
Grab the file I used in the video from here 👉 pages.xelplus.com/variance-chart-file
Hi Leila, I have to tell you that your videos are awesome. Especially appreciate how you explain even complicate things in a simple way.
Thanks a lot Subha. Glad you like them.
Thank you Leila, I could watch you all day but I’ve got to get ready for work and try out your charts.
Haha. Hope it will be helpful at work.
Your videos are amazing ! every video of yours requires at least double the time to practice and understand the technique behind it. you are truly an excel genius !
I appreciate that!
Nice tips!! Big fan of your vids, so helpful and simple. I ALWAYS learn something new. Kudos
Very happy to hear that Sher!
Thank you Leila, I'm adding one of these charts to my dashboard right now.
That's great!!!!
Hi Leila, thank you very much for this very interesting video. You are amazing!
Hello Leila, I loved this video, I will certainly implement it in my work, thanks a lot, you are the best.
I really enjoyed the video. Thanks a lot.
Excellent Leila glad to watch your videos!!!!
Glad you like them Jose.
Once Again... Great Information!
Thanks for the fun with Variance : )
You're welcome Mike :)
Leila Lives Excel... Super Impressed and Extremely Useful Videos...
Awesome lesson! Thanks so much for this tip
Hi Leila, great job, It is really useful for my dashboard.
Hello Leila, your videos are awesome. Stay blessed. Your variance video has helped me a lot.
Hi Robert - Very glad to help out :)
Thank you Leila, very useful tutorial. You are the chart-master :-).
You're very welcome. Thank you for watching :)
Thanks Leila really clear now to apply this to Power BI
thank you for explaining to us how to put in red the negative parts of a chart and its values, great!😉
Excellent tutorial
You are always awesome... with lot of patience you explain. even a kid can understand ur videos... Keep it up...
This video has helped me so much -- thank you very much for your clear explanation!
Glad it was helpful!
great explanation thanks Leila !
Thank you Kevin. You're very welcome.
Leila you're the best🌸
I love your videos!!!
I'm subscribed. Your videos are fantastic. Thank you!!!
Glad to hear that Matthew :)
THIS IS SUPER AMAZING
Beautiful toutorial. Really useful!.
Thanks Jorge. Glad you like it.
Awesome .. Thank you very much...
always great
Very useful lesson 👌👍
You are excellent. I like to watch your uploads. Your topics are very meaningful...
Thank you. I'm very happy to hear that.
Very useful chart. Thank you very much Leila :-)
You're very welcome Alex.
The best trainer!!!
Super advice! As always
Thank you Christos.
Excellent.
Nice trick!
Amazing !
Beautiful 👍
This is very better.
you are master, a good skill to teach.
Thank you Ritesh.
very useful
What a lovely video Leila!! Do let me know if you have created something where we need to find out how much amount have been paid to a contractor and remaining balance so that it can be highlighted for balance pending . Thanks!!
Leila, I'm loving you!
nice number format tricks. thanks
You're welcome Douglas.
Thank You !!
You're welcome.
Great and thanks...I love the way you go into details and make it all very easy to comprehend! Please keep up the good work. :)
Great Video , Thanks Leila Gharani Ma'm :) (Y)
Hi Leila. Thanks for this wonderful video.Just wondering if we can make comparisons for previous year and vs budget in same chart. Will it be possible ?
Well Nice This One
Glad you like it Ibraheem.
Nice.
Thank you Leila. I'd like to contribute by sharing how to align the charts much faster:
With CTRL+SHIFT drag down and drop the first chart so that you duplicate the chart exactly under it. Then you just change the data it is using by dragging the colored rectangles that show what data it is taking (I hope it is more less understandable)
Thanks for sharing!
Thank you for such great demonstrations!
I am trying to figure out one thing though, where I haven't been able to find a solution.
How does one create a line chart, that shows ie. sales numbers per day for a given period 10th of February to 31st of March 2021, and also shows the sales values for the previous year, 2020, on the same chart for the same period?
Thank you so much for sharing your knowledge with all of us.
I have a query here.... When we have big data and need to use a pivot table and slicers the chart that we create initially includes all data (sales and sales PY) since for 1st chart we all need is sales data can we do it on pivot data.
Hi Leila. Great tip as always. :) One quick question though as I can´t seem to get it to work (if it works). How would you do the label color change if your data labels are picked up from the "Value From Cells" option? The color doesn´t change when doing it that it seems, any suggestions? I.e. if I base the bar chart on numeric values, but I want to actually only show a percentage variance (= Values From Cells), any negative percentage doesn´t change to red.
Many thanks
thanks
Cool!
Thank you...
You're welcome.
Very nice! You can also specify the exact width of a graph by going to the format menu under chart tools.
True :) thanks.
This video is very interesting
I have a question mam
For the variance calculation what is the equation if the figures are negative? because when I calculate as u show in video the percentage of variance is increasing even if the loss is increasing
Kindly revert ❤
Hi Leila. Great video. Could you please share the link for excel color index table
great video. but if i use Pivot Chart, the color is returning back to automatic once i close the file and open it again. How to fix this please?
Would going to layout and use the x,y Coordinates for the black chart is the same for the variance charts …. I’ve used PowerPoint too much lol, it was really annoying before I leaned to set the shapes to be the same.
Hi Leila, is there anyway that we add provision in the chart, like not necessary what we budgeted is all spent something out of it is still balanced or we have provisioned it. How can we add in the similar chart
Thanks for the vid, you mentioned that you have video about Number formatting, is it here on youtube? Thanks
I have a detailed video on this in my advanced online course. On RUclips I have this, video: ruclips.net/video/tGY70sdpaLc/видео.html which also does a quick explanation of the rules behind custom formatting.
Hi
it's very helpful trick, I have a question how can we count total criteria (drop down where we can check an uncheck ) in filter data with help of vba?
You can use the specialcells method and use the xlCellTypeVisible - you can find more info here: msdn.microsoft.com/en-us/vba/excel-vba/articles/range-specialcells-method-excel -
I have a few videos on the specialcells method in my upcoming VBA course. I'll add your query though to my RUclips list for future videos :)
Potentially useful, but perhaps a little over-complicated just to get different font colours for +ve and -ve data labels. If you want red and green columns for +ve and -ve values, and are content not to vary the font colour, it is easier just to use "invert if negative" and choose the colours you need there instead.
Hi Ian. Agree. Inverting bar color is probably enough in most cases.
Leila, any advice for an Excel variance chart when a company does not budget for one of their accounts (Adjusted Budget of $0) but has spent money in this account (Actual of $XX.XX)? Unfortunately dividing over zero does not work when trying to find the variance percentage.
How can we change the color of the bars in the graph based on values in the table. For example, if the value is above 95% it should be green else it should turn red. Please help
big brain
Thanks a lot for this video. It was a great help to me.
Need some more help in this. Like as the -ve number is changing its color is it possible the color should change if it is not meeting a specific target. for e.g. (it's higher the better) if the target is 60% any number below it should change to red.
Thanks in advance :)
You're welcome Ajay. Yes that's certainly possible, you just need to use a different technique. Instead of using "invert if negative" you need to add a new dynamic series to your data table that shows the numbers if they are below 60% - you then add this series to your chart - overlap 100%. That's it. This is a good idea for a video. I'm going to add this in, in May.
Thanks Leila. :)
How to change colour of donut chart in excel selection of each category ?
How to remove the custom format code under label option?
Do you have a video showing how to invert the colors if negative on an area graph? Plotting positive and negative values results in a sub par graph.
Currently not but the solution would probably be to use a second series.
@@LeilaGharani That's what I'm currently doing actually. But the point where the graphs cross over zero don't exactly line up, so you end up with your color ending up on the wrong side of the horizontal axis.
Any chance of an update for current excel?
Hello Leila, your way to color the number is not working with !, when i use brackets for color the numbers in chart appear like 0.123548 or 005 in black color (% sign is removed), do you know how to solve this , I use english excel 365 version
Hello
I want to setup Excel so that every time I put in a chart it has the Title, X and Y Axis Titles, the The Range Values, Markers, Trend Line, Data Labels and the Grid Lines and Equations all come in the same every time
Like a Pre-Set
This way all my charts would be formatted the same without me having to change the Text Color, Size, Font
Is this even possible?
You can create the chart once and add save it as a chart template (just right-mouse click you get the option). Then go to insert -charts and then find your template, right-mouse click and set as default. This way, all you need to do is highlight your next data range and press Alt+F1 and your chart is there. This makes a good video actually...I'll add it to my list. Thanks for the suggestion.
Interesting, but not as dynamic as I would have expected
My original chart has $ values up the left and Dates along the bottom
When I pick a different data set with $ values on the left but number values on the bottom the chart still sees the numbers across the bottom as dates
Yes, the Colors and Text sizes are right, but it seams odd that the X axis is fixed to Dates when the values are clearly not dates
I think I will need a Template for each type of chart based on the Values of the X and Y axis
But, that means I only need to build them once each instead of changing them every time
So this has helped a lot
Thanks
James
ps - If you make a video on Chart Templates please send me a link
Hi I want to create a chart based on my pivot result.but the problem is I want project and Managers name to reflect on chart based on week's report mean w11 w12 ..... How to do that dynamic chart to show in term of percentage
I'm not sure about your question....best is to post your specific question either here: www.mrexcel.com/forum or here: www.excelforum.com/ - you can post a link to your question here too and if I get a chance I can take a look, otherwise you will get answers from others...
boring