How to Compare Two Pivot Tables in Excel

Comparing two pivot tables in Excel is a fantastic way to spot trends, measure growth, and understand changes in your data over time or across different categories. This guide will walk you through several practical methods to compare data, from a simple side-by-side visual check to more advanced techniques that calculate the exact differences for you.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Why Compare Pivot Tables?

Before jumping into the "how," let's quickly cover the "why." You might need to compare two pivot tables to answer common business questions like:

• How did our sales in Q2 compare to Q1? (Time-based comparison)
• Are our marketing campaigns in California performing better than our campaigns in New York? (Segment-based comparison)
• What was the change in website traffic before and after our new site launch? (Event-based comparison)
• Is Product A generating more revenue than Product B this year? (Category-based comparison)

Each of these questions involves comparing two sets of similar data to find meaningful differences. Pivot tables are the perfect tool for summarizing that data, and knowing how to compare them effectively is a skill that turns raw numbers into clear, actionable insights.

Preparing Your Data for a Clean Comparison

The secret to any successful pivot table is well-structured source data. Before you start, make sure both of your datasets are organized in a proper tabular format. This means:

• Each column has a single, clear header (e.g., 'Date', 'Region', 'Product', 'Sales Amount').
• Each row represents a single record or transaction.
• There are no blank rows or columns interrupting your data.

For example, if you wanted to compare sales from January and February, your source data should ideally be in one table, not two separate ones. If they are separate, the techniques below will help you bring them together. The key is consistency - the columns you want to compare (like 'Product' or 'Category') should exist and be named identically in both datasets.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Method 1: The Simple Side-by-Side Visual Check

The most straightforward way to compare two pivot tables is to place them next to each other on the same worksheet. This approach works best for smaller datasets where you can visually scan for obvious differences without getting lost in the numbers.

Let's say you have two separate datasets: one for January sales and one for February sales.

Step 1: Create Your First Pivot Table

First, create a pivot table for your January data.

1. Click anywhere inside your January data table.
2. Go to the Insert tab on the Excel ribbon and click PivotTable.
3. In the dialog box, confirm your data range is correct and choose to place the pivot table on a New Worksheet or an Existing Worksheet. Let's choose a new one for now.
4. Click OK.
5. In the PivotTable Fields pane, drag Product to the Rows area and Sales Amount to the Values area. You now have a summary of January sales by product.

Step 2: Create Your Second Pivot Table on the Same Sheet

Now, let's create the second pivot table for February right next to the first one.

1. Go to the worksheet containing your February data.
2. Click inside the data, go to the Insert tab, and click PivotTable.
3. This time, in the "Create PivotTable" dialog box, select Existing Worksheet for "Choose where you want the PivotTable to be placed."
4. Click the selector icon in the "Location" field, navigate to the sheet with your January pivot table, and select a cell a few columns to the right of it (e.g., cell E1). This leaves a buffer column to prevent the pivot tables from interfering with each other if one expands.
5. Click OK.
6. Set up the second pivot table just like the first: drag Product to the Rows area and Sales Amount to the Values area.

You now have two pivot tables sitting side-by-side, making it easy to see the sales figures for each product in January vs. February.

Pros: It's fast, easy, and intuitive for high-level comparisons. Cons: This method is purely visual. It doesn't calculate the difference for you, and it can be difficult to manage with large lists of products or categories. Manually spotting differences in long lists is tedious and prone to error.

Method 2: Combine Data Sources for a Single, Unified Pivot Table

A much more powerful and scalable method is to combine your two datasets into one master table before creating a pivot table. This allows you to analyze both data sources within a single pivot table, giving you the ability to calculate variance directly.

Step 1: Add a "Source" or "Period" Identifier Column

Before combining your data, you need a way to distinguish between the two datasets.

• Go to your first dataset (e.g., January Sales) and add a new column at the end. Name it "Period" or "DataSet."
• Fill this entire column with the value "January" for every row.
• Now, go to your second dataset (February Sales) and do the same, but fill the "Period" column with the value "February."

This column is your key differentiator.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step 2: Combine the Two Datasets

The easiest way to combine them is to copy and paste.

1. Copy all the data from your February table (including the new "Period" column and headers).
2. Go to your January table.
3. Scroll to the first empty row beneath your January data.
4. Paste the February data directly below it.

You should now have one long table containing all the data from both months, with the "Period" column clearly identifying which month each row belongs to.

Step 3: Create One Pivot Table from the Combined Data

Now, create a single pivot table from this new, combined data source.

1. Click anywhere in your master data table.
2. Go to Insert > PivotTable.
3. In the PivotTable Fields pane, set it up like this:

Instantly, you get a perfectly formatted comparison table. Each product is a row, and "January" and "February" are columns showing their respective sales totals side-by-side. This is already a massive improvement over the first method.

Method 3: Calculate the Difference and Percentage Change Automatically

This is where the real power of comparison comes in. Building on Method 2, you can use a built-in Excel feature to automatically calculate the dollar variance and percentage change right inside your pivot table. No manual formulas are needed!

Step 1: Add Fields for Calculation

Using the unified pivot table you just created in Method 2, drag the Sales Amount field into the Values area two more times. You should now have three identical "Sum of Sales Amount" columns.

Step 2: Calculate the Dollar Variance

Let's turn the second sales column into a dollar difference.

1. Right-click on any value inside the second data column (e.g., "Sum of Sales Amount2").
2. In the menu that appears, hover over Show Values As and select Difference From....
3. A dialog box will pop up. For the "Base field," select Period.
4. For the "Base item," select (previous) or the specific period you want to compare against (e.g., "January"). Selecting "(previous)" is useful if you have more than two periods.
5. Click OK.

That column instantly transforms to show the dollar variance between February and January. Don't forget to rename the column header to something clearer, like "Variance ($)."

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 3: Calculate the Percentage Variance

Now, do the same for the third sales column to get the percentage change.

1. Right-click on any value inside the third data column ("Sum of Sales Amount3").
2. Go to Show Values As and select % Difference From....
3. In the dialog box, set the "Base field" to Period and the "Base item" to (previous) or "January."
4. Click OK.

Rename this column header to "% Change," and you're done! You now have a complete comparison report showing sales figures for both periods, the exact dollar difference, and the percentage growth or decline for each product.

Bonus Tip: Use Conditional Formatting to Add Visual Cues

To make your comparison even easier to read, add conditional formatting. Select the "Variance ($)" or "% Change" column in your pivot table.

• Go to Home > Conditional Formatting > Color Scales and select the Green-Yellow-Red scale. Excel will automatically color positive changes green and negative changes red, giving you an at-a-glance view of performance.
• Alternatively, you can use Data Bars to create in-cell bar charts that visually represent the magnitude of the change.

Final Thoughts

Comparing two pivot tables in Excel moves from a simple visual check to a powerful analytical exercise. By combining your data sources and using the "Show Values As" feature, you can build dynamic comparison reports that automatically calculate variance and percentage change, saving you time and preventing manual errors.

As powerful as Excel is, creating these detailed reports still involves several manual steps - combining datasets, building pivot tables, and configuring calculated fields. For real-time, automated analysis across platforms like Google Analytics, Shopify, or Salesforce, we built Graphed. We connect directly to your data sources, allowing you to ask questions in plain English like "compare my Shopify sales month-over-month by product" and get a live, interactive dashboard in seconds, fully automating the entire data wrangling process.