How to Show Variance in Excel Bar Chart

Showing variance in an Excel bar chart is one of the fastest ways to see if you’re hitting your goals. Instead of just plotting sales numbers or website traffic, a variance chart instantly tells you if you're over or under your target. This tutorial will walk you through a clear, step-by-step process to prepare your data and create a powerful variance bar chart that makes your performance reports easy to understand.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why a Bar Chart is Great for Showing Variance

You can calculate variance in a cell, but visualizing it takes your reporting to the next level. A bar chart is particularly effective for a few key reasons:

• Intuitive at a Glance: Our brains are wired to compare lengths. A bar chart makes it obvious which categories have the largest positive or negative variance. Positive bars go one way, negative bars go the other, giving you an instant story.
• Highlights Performance Gaps: It immediately draws attention to the areas that are far from their target, whether that's spectacularly good or worryingly bad. This helps you know where to focus your time and effort.
• Clear Communication: When presenting to your team or stakeholders, a variance chart is much easier to digest than a table of numbers. It removes the mental math, allowing everyone to focus on the insights, not the calculations.

Whether you're comparing actual sales to targets, current ad spend to budget, or this month's website users to last month's, a variance chart turns raw data into actionable insight.

Step 1: Get Your Data Ready

The foundation of any great chart is well-structured data. For a variance analysis, you need your actual performance numbers and the numbers you're comparing them against (your target or benchmark). You’ll then add a simple formula to calculate the difference.

Let's use a common example: monthly sales figures versus their targets for a team of sales reps.

Start with a simple table in Excel with three columns:

1. Category: The item you're measuring (e.g., Sales Rep, Product, Marketing Channel).
2. Actual: The measured result for that category.
3. Target: The goal or benchmark for that category.

Here’s what our example data looks like:

A             B        C

1 Sales Rep Actual Target 2 Anna Smith $115,000 $100,000 3 Ben Carter $90,000 $110,000 4 Carla Davis $140,000 $125,000 5 David Evans $95,000 $100,000 6 Eva Foster $105,000 $105,000

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Calculate the Variance

Now, you need to add a fourth column to calculate the variance. The formula is simply Actual - Target. A positive result means you beat the target, while a negative result means you fell short.

In cell D2, type the following formula and drag it down for the other rows:

=B2-C2

Your table now has all the data required to build the chart:

A             B        C        D

1 Sales Rep Actual Target Variance 2 Anna Smith $115,000 $100,000 $15,000 3 Ben Carter $90,000 $110,000 -$20,000 4 Carla Davis $140,000 $125,000 $15,000 5 David Evans $95,000 $100,000 -$5,000 6 Eva Foster $105,000 $105,000 $0

With this simple four-column table, you're ready to start visualizing.

Step 2: Create a Dedicated Variance Bar Chart

While you could plot Actuals and Targets on the same chart, a dedicated variance chart is much cleaner for highlighting performance. This chart focuses solely on how much each category deviated from its goal, making it incredibly easy to read.

Insert the Basic Bar Chart

First, we’ll create a basic bar chart that uses just our variance calculation.

1. Select the data for your categories and your variance. To do this, click and drag to select the "Sales Rep" column (A1:A6), then hold the Ctrl key (or Cmd on Mac) and select the "Variance" column (D1:D6).
2. Go to the Insert tab on the Excel ribbon.
3. In the Charts section, click the "Insert Column or Bar Chart" icon.
4. From the dropdown, select 2-D Bar > Clustered Bar.

Excel will instantly generate a bar chart. Notice that it automatically handles the positive and negative values. Positive variances (like Anna's and Carla's) extend to the right, and negative variances (like Ben's and David's) extend to the left.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Customize Bar Colors for Positive and Negative Variance

This is where the magic happens. A standard bar chart shows all bars in the same color, but for a variance chart, you want to use colors to tell a story: green for good (over target) and red for bad (under target).

Luckily, Excel has a built-in feature for this that is surprisingly easy to use.

1. Double-click on any of the bars in your chart to open the Format Data Series pane on the right side of your screen.
2. In the pane, make sure you've selected the paint bucket icon (Fill & Line).
3. Expand the Fill section.
4. Check the box that says Invert if negative.

As soon as you check the box, two new color selection boxes will appear.

• The first color box is for your positive bars. Click it and choose a shade of green.
• The second color box is for your negative bars. Click it and choose a shade of red.

Instantly, your chart is transformed. The positive (above target) performances are in green and project to the right, while the negative (below target) performances are in red and project to the left. Just like that, your chart is telling a much clearer story.

Step 3: Clean Up and Refine Your Chart

Your chart is functional, but a few small tweaks can make it look professional and even easier to read.

Add a Meaningful Title

Don't stick with the generic "Chart Title." Change it to something descriptive, like "Sales Performance: Variance from Target." This gives context to anyone looking at the chart for the first time.

Adjust the Horizontal Axis

The position of your labels on the horizontal axis (which shows the variance values) can sometimes be confusing. Excel might place the y-axis labels on the far left or in the middle at the zero point. Let's move them to one side for clarity.

1. Double-click the vertical axis (where the Sales Reps' names are). The Format Axis pane will appear.
2. Look for the section for Labels.
3. Find the "Label Position" dropdown and change it from "Next to Axis" to Low. This will move all the sales rep names to the far left of the chart, making it look cleaner, especially when you have many categories.

Add Data Labels

While the bars show the scale of the variance, showing the exact numbers can be helpful.

1. Click on your chart to select it, then click the "+" icon (Chart Elements) that appears on the top-right.
2. Check the box for Data Labels.
3. Excel will place the variance values at the end of each bar. You can click on the labels themselves to format them (change the font, color, or number format) in the Format Data Labels pane.

Remove Unnecessary Clutter

Minimalist charts are often the easiest to read. Consider removing elements you don't need.

• If you have data labels, you might not need the horizontal axis anymore. Click on the horizontal axis labels and press the Delete key.
• You can also remove the gridlines for a cleaner look. Click on any gridline and press Delete.

After these steps, you’ll have a polished, easy-to-understand chart that clearly displays performance variance for each of your categories.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Pro Tip: Show Actual vs. Target Alongside Your Variance Chart

A variance chart is fantastic for seeing who is above or below target, but it doesn't show the full picture of performance. For example, two reps might both be $15,000 over target, but one might have achieved $115,000 in sales while the other achieved $65,000. For this reason, it’s often powerful to show the actual sales figures alongside your variance chart in a dashboard view.

You can create a second, simple bar chart showing the Actual vs. Target data for each rep. To do this, select the first three columns of your data (Sales Rep, Actual, Target) and insert another clustered bar chart. Place it next to your color-coded variance chart. Together, they provide both the raw performance numbers and the critical "what it means" context in one simple dashboard.

Final Thoughts

Creating a variance bar chart in Excel is a straightforward process that transforms a plain data table into a powerful visual tool for performance analysis. By calculating the variance, building a bar chart, and using the "Invert if negative" feature, you can quickly see who’s exceeding goals and who needs support.

Building these reports manually in spreadsheets is a classic skill, but it admittedly takes time, especially when you have to connect data from different sources and update the report every week. At Graphed , we automate this entire workflow. Instead of building charts click by click, you can connect your data sources (like your CRM or sales software) and simply ask in plain English: "Show me a bar chart of sales variance versus target for each rep this quarter." We instantly build a live, interactive dashboard for you, saving you hours of spreadsheet work and letting you and your team focus on hitting your targets.