Excel Variance Charts: Actual to Previous Year or Budget Comparisons

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:

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.

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

🚩Let’s connect on social:

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

[rajeshvkumar]

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

[zumbawithyasmin]

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

[lesportautrement]

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

[HemanthKumar-lbxt]

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

[puranyashkapoor]

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 !

[nmejiai]

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

[Pegasus]

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)

[leenanathanni]

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

[Citobiopsias]

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

[tahirshah]

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

[abdulsalam]

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

[leighbecky]

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

[robertakwasiadjei]

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

[jatinab]

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

[ALPHERATZ]

Excellent Leila glad to watch your videos!!!!

[diannedrechsler]

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

[mazarata]

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

[computertutoring]

Thanks Leila really clear now to apply this to Power BI

[kameshsubramanian]

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

[ubaidyaseencubaidyaseenc]

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 ❤