How to Do Gap Analysis in Excel

Knowing your business isn't meeting its goals is one thing, but pinpointing exactly where and why the shortfall is happening is another challenge entirely. A gap analysis is the perfect tool for this, comparing where you are to where you want to be. This tutorial will walk you through exactly how to perform a gap analysis in Excel using simple formulas and clear visualizations, so you can turn raw data into an actionable strategy.

What Exactly Is a Gap Analysis?

At its core, a gap analysis is a simple method for comparing your actual performance against your desired performance or goals. Think of it as answering the question: "Where are we now versus where did we plan to be?" The "gap" is simply the difference between those two states.

This process isn't just about finding a negative number. It's about:

• Identifying Shortfalls: Clearly see which areas are underperforming.

• Quantifying the Difference: Understand the exact size of the performance gap.

• Informing Strategy: Use the insights to figure out why the gap exists and what you need to do to close it.

You can apply gap analysis to almost any part of your business. For example:

• Comparing monthly sales achieved by a rep vs. their sales quota.

• Analyzing website traffic numbers against your quarterly traffic goals.

• Tracking project completion milestones versus the original timeline.

Getting Your Data Ready in Excel

Before you can do any analysis, you need to structure your data correctly. The good news is that you don't need a complicated setup. For a gap analysis, a simple, clean table is all it takes.

The Basic Data Structure

Your Excel sheet should have at least three columns:

1. An identifier or category (e.g., Sales Rep, Marketing Channel, Product Name).

2. The target or goal performance (e.g., 'Sales Quota', 'Traffic Goal'). We'll call this the "Future State".

3. The actual performance achieved (e.g., 'Sales Achieved', 'Actual Traffic'). We'll call this the "Current State".

For this tutorial, let's use a common scenario: tracking sales team performance against their quarterly quotas. Our table in Excel looks like this:

(In your spreadsheet, you'd have your data in cells A1 through C6)

Once your data is laid out cleanly like this, you're ready to start crunching the numbers.

Method 1: Calculating the Gap with Simple Excel Formulas

The simplest way to start your analysis is by adding a new column to calculate the exact size of the gap for each item. This gives you a clear, numerical value for performance against the goal.

Step 1: Calculate the Raw Gap

First, add a new column header in cell D1 called "Performance Gap".

In the first cell of this column (D2 in our example), we'll write a basic subtraction formula. There are two ways to do this, but for clarity, we'll calculate Actual - Target. This way, a positive number means the target was exceeded, and a negative number means there was a shortfall.

In cell D2, type the following formula and press Enter:

Excel will calculate the result: 15,000 for Anna Smith. To apply this formula to the rest of your reps, simply click on cell D2, grab the small square "fill handle" in the bottom-right corner, and drag it down to the last row of your data (D6).

Your table now looks like this, cleanly showing who hit their goal and who missed it:

Step 2: Calculate the Gap Percentage (Optional but Powerful)

Sometimes, the raw number isn't enough context. A $30,000 shortfall is much more significant for a rep with a $90,000 quota than one with a $250,000 quota. Calculating a percentage makes the comparison fairer.

Add a new column header in E1 called "Gap %".

In cell E2, type the formula to calculate the percentage difference:

This formula divides the performance gap by the original target. After typing the formula, highlight column E, right-click, select Format Cells, and choose Percentage to display the result correctly.

Drag the fill handle down again, and you'll have a completed table rich with insights.

Method 2: Visualizing the Gap with Excel Charts

Numbers are great, but visuals are often better for spotting trends and communicating results to others. Excel's charting tools are perfect for bringing your gap analysis to life.

Creating a Column Chart to Compare Actual vs. Target

The most direct way to visualize the data is with a classic column chart. This lets you quickly see the target and the actual performance side-by-side.

1. Select your data: Highlight the range that includes your categories, target values, and actual values. In our example, that would be from cell A1 to C6.

2. Insert the chart: Go to the Insert tab in the Excel ribbon. In the Charts section, click the icon for Insert Column or Bar Chart.

3. Choose your chart type: Select the first option under 2-D Column, called Clustered Column.

Excel will instantly generate a chart. You can immediately see how the blue bar (Quota) compares to the orange bar (Sales Achieved) for each rep. Reps where the orange bar is taller than the blue one are your over-performers, and reps where it's shorter have a clear gap.

Creating a Chart that Highlights the Gap Value

To focus specifically on the size and direction of the gap, a chart that plots only the "Performance Gap" column is even more powerful. Negative values will appear as bars below the zero-line, making shortfalls impossible to miss.

1. Select your data: Highlight just the Sales Rep column and the Performance Gap column. To select non-adjacent columns, first highlight A1:A6, then hold the Ctrl key (or Cmd on Mac) and highlight D1:D6.

2. Insert the chart: Follow the same steps as before (Insert > Column Chart > Clustered Column).

This chart gives you a crystal-clear view of performance. Anna and Chloe have positive bars, showing they beat their quotas. Ben, David, and Eve have negative bars, immediately drawing your attention to their performance gaps. This is a very effective chart for management and strategic meetings.

Pro Tip: For extra visual impact, you can right-click the bars in this chart and choose "Format Data Series." In the options pane, under "Fill & Line," check the box for "Invert if negative." This will automatically color your negative bars a different color (like red) to make the performance gaps stand out even more.

Interpreting Your Analysis and Taking Action

Performing the analysis is just the first step. The real value comes from what you do with the results. Now that you've calculated and visualized the gaps, it's time to ask "why?"

Look at your table and charts and ask questions like:

• Are there any major outliers? David and Ben have the same size gap ($30,000), which is more severe than Eve's ($20,000).

• Are there patterns? Are all the underperformers new to the team? Do they all sell in the same territory? Were the targets they were given unrealistic to begin with?

• What is working well? What did Anna do to exceed her target by 15%? Can her strategies be taught to the rest of the team?

The goal isn't just to point out failures, but to understand the reasons behind them. Your gap analysis becomes the starting point for strategic conversations about training, resource allocation, goal setting, and overall business strategy.

Final Thoughts

Performing a gap analysis in Excel is a straightforward yet powerful way to transform a simple list of numbers into a clear understanding of your business performance. Whether you use basic formulas or visual charts, this method helps you move beyond raw data to diagnose problems and find opportunities for improvement.

While Excel is fantastic, the most time-consuming part of this process can still be gathering all the data in one place - especially when your sales data is in Salesforce, your marketing data is in Google Analytics, and your revenue data is in Shopify. With Graphed, we automate all that data connection and preparation for you. You can connect your platforms once and use simple, natural language to ask questions, build dashboards, and get all the insights from this tutorial without exporting a single CSV or writing a single formula.