Excel Variance Charts: Actual to Previous Year or Budget Comparisons

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/variance-chart-file

[@rajeshvkumar1]

Hi Leila, I have to tell you that your videos are awesome. Especially appreciate how you explain even complicate things in a simple way.

[@LeilaGharani]

Thanks a lot Subha. Glad you like them.

[@zumbawithyasmin1]

Thank you Leila, I could watch you all day but I’ve got to get ready for work and try out your charts.

[@LeilaGharani]

Haha. Hope it will be helpful at work.

[@puranyashkapoor7468]

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 !

[@LeilaGharani]

I appreciate that!

[@HemanthKumar-lb4xt]

You are always awesome... with lot of patience you explain. even a kid can understand ur videos... Keep it up...

[@lesportautrement]

thank you for explaining to us how to put in red the negative parts of a chart and its values, great!😉

[@rajeshmajumdar4999]

The best trainer!!!

[@nmejiai]

Hello Leila, I loved this video, I will certainly implement it in my work, thanks a lot, you are the best.

[@hemvaidya]

THIS IS SUPER AMAZING

[@tahirshah6979]

You are excellent. I like to watch your uploads. Your topics are very meaningful...

[@LeilaGharani]

Thank you. I'm very happy to hear that.

[@excelisfun]

Thanks for the fun with Variance : )

[@LeilaGharani]

You're welcome Mike :)

[@1877Pegasus]

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)

[@LeilaGharani]

Thanks for sharing!

[@elhamakbari7825]

Leila you're the best🌸

[@leenanathanni2082]

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!!

[@robertakwasiadjei8223]

Hello Leila, your videos are awesome. Stay blessed. Your variance video has helped me a lot.

[@LeilaGharani]

Hi Robert - Very glad to help out :)

[@abdulsalam4695]

Hi Leila, great job, It is really useful for my dashboard.

[@ALPHERATZ3650]

Excellent Leila glad to watch your videos!!!!

[@LeilaGharani]

Glad you like them Jose.

[@Citobiopsias]

Hi Leila, thank you very much for this very interesting video. You are amazing!

[@leighbecky]

This video has helped me so much -- thank you very much for your clear explanation!

[@LeilaGharani]

Glad it was helpful!

[@diannedrechsler4792]

Thank you Leila, I'm adding one of these charts to my dashboard right now.

[@LeilaGharani]

That's great!!!!

[@M.A.Sammani]

Very useful lesson 👌👍

[@Allmytravelz7072]

Nice tips!! Big fan of your vids, so helpful and simple. I ALWAYS learn something new. Kudos

[@LeilaGharani]

Very happy to hear that Sher!

[@kameshsubramanian2553]

Hi Leila. Great video. Could you please share the link for excel color index table

[@sanemyk]

I love your videos!!!

[@ubaidyaseencubaidyaseenc8207]

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 ❤

[@bhardwajgovinda911]

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.

[@computertutoring]

Thanks Leila really clear now to apply this to Power BI

[@powerbiselfstudyguide]

Excellent.

[@jatinab]

Leila Lives Excel... Super Impressed and Extremely Useful Videos...

[@mazarata73]

Thank you Leila, very useful tutorial. You are the chart-master :-).

[@LeilaGharani]

You're very welcome. Thank you for watching :)

[@hazemali382]

always great

[@sjn7220]

Very nice! You can also specify the exact width of a graph by going to the format menu under chart tools.

[@LeilaGharani]

True :) thanks.

[@mohammedal-sabbagh8463]

Awesome .. Thank you very much...

[@imrandaud478]

Beautiful 👍

[@kowsergazi]

I really enjoyed the video. Thanks a lot.

[@ivanrybalchenko7225]

Nice trick!

[@matthewbrierley88]

I'm subscribed. Your videos are fantastic. Thank you!!!

[@LeilaGharani]

Glad to hear that Matthew :)

[@sbkucin222]

Excellent tutorial

[@basembilal1463]

Amazing !

[@MySpreadsheetLab]

great explanation thanks Leila !

[@LeilaGharani]

Thank you Kevin. You're very welcome.

[@sivabalanmoorthy]

very useful

[@jerg83]

Beautiful toutorial. Really useful!.

[@LeilaGharani]

Thanks Jorge. Glad you like it.

[@christoskonstantinou1074]

Super advice! As always

[@LeilaGharani]

Thank you Christos.

[@rksudera]

Once Again... Great Information!

[@RiteshSharma-it5ks]

This is very better. you are master, a good skill to teach.

[@LeilaGharani]

Thank you Ritesh.

[@smithy559]

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.

[@LeilaGharani]

Hi Ian. Agree. Inverting bar color is probably enough in most cases.

[@alexrosen8762]

Very useful chart. Thank you very much Leila :-)

[@LeilaGharani]

You're very welcome Alex.

[@deepikamisra2910]

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. :)

[@federicomassimo3898]

Leila, I'm loving you!

[@SaqibAli-we3fb]

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 ?

[@douglaszulu6281]

nice number format tricks. thanks

[@LeilaGharani]

You're welcome Douglas.

[@nobilismaximus]

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.

[@magdolinagomes6485]

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

[@AndreasBodin]

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?

[@michelleturner9069]

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.

[@mattias5063]

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

[@hosseinhosseinpoor4845]

thanks

[@EricaDyson]

Nice.

[@mattparker4493]

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.

[@LeilaGharani]

Currently not but the solution would probably be to use a second series.

[@mattparker4493]

@@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.

[@DavidPattiruhu]

Cool!

[@hasanalamoudi436]

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

[@jamesgoodwin9058]

Any chance of an update for current excel?

[@mageshkannashanmuganathan594]

How to remove the custom format code under label option?

[@casper3842]

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?

[@pawanmishra2855]

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?

[@LeilaGharani]

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 KZbin list for future videos :)

[@ytforum]

Well Nice This One

[@LeilaGharani]

Glad you like it Ibraheem.

[@shanayatanwar1-f223]

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

[@automationguide3498]

Thank You !!

[@LeilaGharani]

You're welcome.

[@123456789mumbai]

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 :)

[@LeilaGharani]

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.

[@123456789mumbai]

Thanks Leila. :)

[@userme2803]

Thanks for the vid, you mentioned that you have video about Number formatting, is it here on youtube? Thanks

[@LeilaGharani]

I have a detailed video on this in my advanced online course. On KZbin I have this, video: kzbin.info/www/bejne/qni8aGOpmdWUgsU which also does a quick explanation of the rules behind custom formatting.

[@avinashk285]

Thank you...

[@LeilaGharani]

You're welcome.

[@bimalnaha]

How to change colour of donut chart in excel selection of each category ?

[@falconeye577]

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?

[@LeilaGharani]

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.

[@falconeye577]

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

[@sanjayTECH04]

Great Video , Thanks Leila Gharani Ma'm :) (Y)

[@sasikumarkumar8098]

big brain

[@parimalaarya2041]

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

[@LeilaGharani]

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...

[@shrikantbadge3978]

boring

[@violetnha]

Awesome lesson! Thanks so much for this tip