Graphed LogoGraphed
Login

How to Insert a Combo Pivot Chart in Excel

Cody Schneider•

A standard Excel chart visualizes one set of data, but what happens when you want to compare two different measures on one chart, like sales revenue and profit margin? That's where a Combo Pivot Chart comes in, allowing you to elegantly display data with different scales in a single, insightful visual. This guide will walk you through exactly how to prepare your data and create a dynamic Combo Pivot Chart in just a few steps.

What Exactly is a Combo Pivot Chart?

A Combo Pivot Chart is a chart built from a PivotTable that combines two or more chart types. For example, you might use columns to represent sales figures and a line to show a growth percentage over time. It gets its real power from the ability to plot one data series on a "secondary axis."

Imagine your company's monthly revenue is in the hundreds of thousands of dollars, while your profit margin is between 20-30%. If you plot both on the same standard axis, the profit margin line would look flat and almost invisible at the bottom, completely overshadowed by the massive revenue bars. A combo chart solves this by giving the profit margin its own scale on the right-hand side of the chart (the secondary axis), so both metrics are displayed clearly and in proper proportion.

You should use a Combo Pivot Chart when you need to:

  • Show a relationship between two metrics that have different units or scales (e.g., dollars and percentages).

  • Compare values over time while also highlighting a trend (e.g., units sold vs. customer satisfaction score).

  • Create a more information-dense and visually engaging report without cluttering it with multiple charts.

Step 1: Get Your Data Ready

Before you can build anything in Excel, your raw data needs to be clean and structured correctly. Great charts come from great data foundations. A PivotTable requires your data to be in a simple, tabular format.

Here’s the checklist for perfect data preparation:

  • Organize in Columns and Rows: Each piece of data should have its own cell. Each record (like a single sale or entry) should be its own row, and each category of information (like Date, Product, Revenue) should be its own column.

  • Every Column Needs a Header: Give each column a distinct and descriptive name in the first row (e.g., "Transaction Date," "Units Sold," "Region").

  • No Blank Rows or Columns: Remove any completely empty rows or columns within your dataset, as these can confuse Excel when it tries to identify your data range.

  • Format as a Table (Pro Tip): This isn't strictly necessary, but it's a huge time-saver. Select any cell in your data and press Ctrl + T (or go to Insert > Table). This turns your data range into a dynamic Excel Table. The benefit? As you add new rows of data, the table automatically expands, and your PivotTable and PivotChart will include the new information with a simple refresh, saving you from manually updating your data source range.

Our Example Dataset

For this tutorial, let's use a sample sales dataset. It is formatted as a Table named "SalesData" and includes the month, sales team, revenue generated, and the closing rate percentage for a B2B SaaS company.

Our goal is to create a chart that shows total monthly revenue (as columns) and the average close rate (as a line) to see if there's a connection between them.

Step 2: Create Your PivotTable

With our data perfectly structured, creating the underpinning PivotTable is fast and straightforward. This table will summarize our raw data so the chart has something to visualize.

  1. Click anywhere inside your data table (our "SalesData" table).

  2. Navigate to the Insert tab on the Ribbon and click PivotTable.

  3. A dialog box will appear. Since we formatted our data as a Table, Excel has already correctly identified the source ("SalesData"). Just ensure "New Worksheet" is selected and click OK.

  4. You'll now have a blank PivotTable and the "PivotTable Fields" pane will appear on the right side of your screen. This is your control center. Let’s build the summary we need:

    • Drag the Month field into the Rows area.

    • Drag the Revenue field into the Values area.

    • Drag the Close Rate field into the Values area as well.

Your Fields list should look like this:

Finishing an Essential PivotTable Tweak

Excel defaults to summarizing data with a SUM calculation. This is perfect for "Revenue," but it makes no sense for our "Close Rate." We don't want the sum of percentages, we want the average.

  1. In the "Values" area of the PivotTable Fields pane, click on "Sum of Close Rate."

  2. Select Value Field Settings...

  3. In the new dialog box, choose Average from the list under "Summarize value field by."

  4. While you're here, click Number Format.... Select "Percentage" and set it to 0 decimal places. Click OK.

  5. Click OK again to close the "Value Field Settings."

Your final PivotTable will now show the total revenue and the average close rate for each month, ready to be visualized.

Step 3: Insert and Configure the Combo Pivot Chart

Now for the main event: turning our tidy PivotTable into a powerful Combo Pivot Chart.

  1. Click anywhere inside your newly created PivotTable.

  2. Go to the PivotTable Analyze tab on the top Ribbon.

  3. Click the PivotChart button.

  4. The "Insert Chart" window will open. Scroll down the list on the left and select Combo at the very bottom.

  5. This is the most important step! Excel now shows a "Custom Combination" view where you can define how each data series looks. This is where you bring your chart to life.

    • For Sum of Revenue, ensure the Chart Type is set to Clustered Column.

    • For Average of Close Rate, change the Chart Type to Line.

    • For Average of Close Rate, check the Secondary Axis box on the right.

  6. As soon as you check the "Secondary Axis" box, you’ll see the preview update. The line for Close Rate instantly becomes visible and properly scaled. Click OK.

Congratulations! You've successfully created a Combo Pivot Chart in Excel. Now let's clean it up to make it report-ready.

Step 4: Customize Your Chart for Clarity and Impact

A default chart gets the point across, but a well-formatted chart tells a story. Let’s make a few quick improvements to turn this visual into a professional-grade analysis.

Refine Chart Titles and Axes

Generic titles are roadblocks to understanding. Make yours clear and direct.

  • Chart Title: Click the default "Total" title and change it to something descriptive, like "Monthly Revenue vs. Close Rate."

  • Axis Titles: A secondary axis is confusing without a label. Click the "+" icon on the top-right of your chart, hover over Axis Titles, click the small arrow, and check both "Primary Vertical" and "Secondary Vertical." Then, edit the new titles accordingly (e.g., "Total Revenue" for the left axis and "Average Close Rate" for the right axis).

Use Slicers for Interactive Filtering

The "Pivot" in "PivotChart" means it’s dynamic. Slicers are user-friendly buttons that let you filter the chart without needing to touch the PivotTable.

  1. Click on your PivotChart to select it.

  2. Go to the Pivot Chart Analyze tab and click Insert Slicer.

  3. Check the box for Team and click OK.

Now you have an interactive slicer next to your chart. Click on "North," "South," or "West" to instantly see how each team's revenue and close rate performance differed over the months. This makes your dashboard much more powerful and useful for exploring the data.

Final Thoughts

The ability to create a Combo Pivot Chart is a fantastic skill for any Excel user. It allows you to combine different data types and scales into one single chart, revealing insights that would otherwise be hidden across multiple visuals or lost in the numbers. By adding a secondary axis, you can give each metric the space it deserves, creating a clear and accurate comparison for your reports.

Manually refreshing spreadsheets and walking through chart menus gets the job done, but it can quickly pull you away from the actual analysis. As business data gets more complex and more scattered across different apps, this manual work piles up. For that reason, we built Graphed to bypass the manual setup entirely. Instead of configuring PivotTables and axes, you connect your data sources once and just ask for what you want, like, "Show me a chart of total revenue and average deal close rate by month from HubSpot." It’s designed to deliver the answers without the administrative dashboard work.