How to Do Variance Analysis in Excel

Ever look at the end-of-month numbers and wonder why your results don't match your plan? Variance analysis is the process of digging into those differences to understand exactly what happened. This guide will walk you through how to perform a clear and insightful variance analysis right inside Microsoft Excel.

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

What Exactly Is Variance Analysis?

At its core, variance analysis is simply the act of comparing your planned, budgeted, or expected results against your actual results. It’s a fundamental tool for checking the health and performance of any part of your business, from a marketing campaign budget to your overall sales revenue.

But it's not just about noting that "revenue was $5,000 under budget." The real power comes from answering the next question: why?

A favorable variance is a good thing. This happens when actual revenue is higher than budget or when actual expenses are lower than budget.

An unfavorable variance is the opposite. It happens when actual revenue is lower than expected or when actual costs are higher than you planned for.

Understanding which variances are favorable and which are unfavorable is the first step toward making smarter decisions. Did a specific ad campaign overperform? Was there an unexpected cost that blew up your budget? Excel is the perfect tool to begin uncovering these stories.

Setting Up Your Data in Excel

Before you can analyze anything, your data needs to be clean and organized. A poorly structured spreadsheet will make the entire process more difficult than it needs to be. For a basic variance analysis, a simple table is all you need.

Organize your data with three primary columns:

• Categories: What you are measuring (e.g., Sales Reps, Marketing Channels, Product Lines, Expense Types).
• Budget / Plan: Your forecasted or planned numbers for each category. This is your baseline.
• Actual: The final, actual numbers you recorded for each category.

Let’s use a simple marketing budget as an example. Your setup should look something like this:

A few quick tips for a better setup:

• Keep it simple: Don't merge cells in your data table. Keep each piece of information in its own cell.
• Clear headings: Use descriptive headers for each column.
• Use 'Table' formatting: Select your data range and click Insert > Table (or press Ctrl+T). This makes formulas easier to manage and automatically adds nice formatting.

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

Calculating Variance in Excel (Step-by-Step)

With your data neatly organized, you can start calculating the variance. There are two key numbers you'll want to find: the absolute variance (the raw dollar amount) and the percentage variance (the difference relative to the budget).

Step 1: Calculate the Absolute Variance

The absolute variance tells you the exact numerical difference between your actual and budgeted figures. The formula couldn't be simpler:

Actual - Budget

Add a new column to your table called "Variance ($)". In the first data row (cell D2 in our example), type the following formula and press Enter:

=C2-B2

If you're using an Excel Table, it will automatically fill this formula down for the rest of your column. If not, click the small square in the bottom-right corner of the cell and drag it down.

You’ll now have a column showing the raw difference. A positive number means you spent more than budgeted, and a negative number means you spent less.

Step 2: Calculate the Percentage Variance

The absolute variance is useful, but it lacks context. A $1,000 overspend on a $2,000 budget is a massive problem (50% over!). But a $1,000 overspend on a $100,000 budget is less alarming (1% over).

This is where percentage variance comes in. The formula is:

(Actual - Budget) / Budget

Add another new column called "Variance (%)". In the first data row (cell E2), type this formula:

=(C2-B2)/B2

Excel will show this as a decimal by default. To make it readable, select the entire column and navigate to the Home tab. In the Number group, click the Percent Style (%) icon. You’ll now see a clear percentage that shows how much a category deviated from its plan.

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

Bringing Your Analysis to Life with Conditional Formatting

Now you have the numbers, but a table full of figures can be hard to interpret at a glance. Excel's Conditional Formatting feature transforms your data table into a visual dashboard, allowing you to spot important variances instantly.

Select your variance column(s) and go to the Home tab > Conditional Formatting.

Option 1: Use Data Bars

Data Bars create a small bar chart inside each cell, making it incredibly easy to see the magnitude of each variance relative to the others. Under Conditional Formatting, choose Data Bars and select a color. Instantly, you can spot the largest variances without reading a single number.

Option 2: Use Color Scales

Color scales apply a color gradient to your cells based on their values. A common choice is the Red-Yellow-Green scale. However, you need to be careful with the context.

For revenue, high numbers are green (good). But for expenses, high numbers are red (bad!). You may need to create separate rules for revenue and cost tables or use a simpler two-color scale to highlight deviations from zero.

Option 3: Use Up/Down Arrow Icons

This is a classic for financial and performance reporting. Icon sets can add a green up-arrow for favorable variances and a red down-arrow for unfavorable ones.

1. Select your Percentage Variance column.
2. Go to Conditional Formatting > Icon Sets and choose the arrow style you like.
3. By default, Excel's rules might not make sense. To fix this, go back to Conditional Formatting > Manage Rules.
4. Select your rule and click Edit Rule.
5. Here, you can set your own logic. For costs, you might set it to show a green arrow for any value less than 0 (under budget) and a red arrow for any value greater than 0 (over budget).

Applying these simple visual tweaks will make your report a hundred times more effective and easier for others to understand.

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

Creating a Variance Analysis Chart

For presentations and reports, a chart is often more powerful than a table. A clustered column chart is a great way to show budget vs. actuals.

1. Select your categories and your Budget and Actual columns. (Hold Ctrl to select non-adjacent columns).
2. Go to the Insert > Charts section and select a Clustered Column chart.
3. Excel will instantly generate a chart comparing the planned and actual results side-by-side for each category.
4. Customize your chart by adding a title like "Marketing Spend: Budget vs. Actual" and adjusting the colors to fit your brand.

Pro-Tip: To take it a step further, create a combo chart. You can show the Budget vs. Actual as columns and overlay the Percentage Variance as a line chart on a secondary axis. This gives you a complete performance picture in one view.

Common Pitfalls and Best Practices

Excel makes the calculations easy, but the true skill is in interpreting the results. Here are a few things to keep in mind:

• Context is Everything: A 100% variance on a $50 line item is less important than a 5% variance on a $50,000 line item. Don't just focus on the biggest percentages, consider the absolute dollar amount as well.
• Watch Your Positives and Negatives: A positive variance for revenue is fantastic news - you earned more than you expected! A positive variance for expenses is bad news - you spent more than you planned. Always be clear about what you're measuring.
• Investigate the "Why": The analysis doesn't end with the spreadsheet. A variance report tells you what happened. Your job is to find out why. Talk to your team. Did ad costs go up? Did a sales rep close a massive deal? Turn your data into a conversation.

Final Thoughts

Learning how to perform variance analysis in Excel is a must-have skill for anyone responsible for a budget or performance targets. By structuring your data correctly, using simple formulas, and applying visual aids like conditional formatting and charts, you can transform a static spreadsheet into a powerful tool for discovering what’s really driving your business results.

While Excel is great for this, the process of manually downloading CSVs from different platforms, cleaning all the data, and rebuilding these reports every week or month is time-consuming. We built Graphed to automate this entire workflow. It connects directly to your marketing and sales tools like Google Analytics, Shopify, and Facebook Ads, so your data is always live and in one place. You can ask for a variance analysis report in plain English and get a real-time, interactive dashboard in seconds, saving you from all the manual spreadsheet work.