How to Make an Excel Chart with Two Sets of Data

Trying to visualize how two different sets of numbers relate to each other - like your ad spend versus your sales revenue, or website traffic from two different countries - can feel tricky in Excel. Putting everything on a single chart often results in a confusing mess where one data set squishes the other into a flat line. Fortunately, Excel has powerful, easy-to-use charting features designed specifically for this. This guide will walk you through exactly how to create clear, insightful charts using two sets of data.

First, Get Your Data Ready for Charting

Before you can build your chart, your data needs to be organized logically. A clean setup is the foundation of a good chart. For most comparisons, you’ll want a three-column layout:

• Column 1: The Shared Label. This is your common X-axis, typically a time period like day, week, or month.

• Column 2: Data Set 1. This is your first metric, like "Sales Revenue."

• Column 3: Data Set 2. This is your second metric, like "Ad Spend."

Your spreadsheet should look something like this:

Having a clean, simple layout like this prevents headaches and makes the charting process smooth.

Method 1: The Combo Chart with a Secondary Axis

The "combo" chart is the most common and effective way to display two data sets that are measured on different scales. For instance, comparing revenue (in tens of thousands of dollars) to website visits (in the hundreds) on a single scale would make the website visits line look flat and meaningless. The solution is creating a secondary vertical axis on the right side of the chart.

Let's use an example where we compare monthly Revenue (large dollar amounts) and the Number of MQLs (Marketing Qualified Leads) generated (smaller whole numbers).

Step-by-Step Guide to Creating a Combo Chart:

1. Select Your Data: Click and drag to highlight all the cells containing your data, including the headers (e.g., from cell A1 to C7). This tells Excel what information to include in the chart.

2. Insert the Chart: Go to the Insert tab on Excel's ribbon. In the Charts section, find and click the "Insert Combo Chart" icon. It looks like a small bar chart with a line chart overlaid on top.

3. Choose a Combo Chart Type: A dropdown menu will appear. You can choose a pre-made "Clustered Column - Line" chart, but for more control, select "Create Custom Combo Chart..." at the bottom.

4. Configure Your Axes: This is the most important step. A dialog box will pop up, showing your two data series ("Revenue" and "MQLs"). For each series, you can choose a chart type. Let's make:

   • Revenue an elegant Clustered Column chart.

   • MQLs a connected Line chart.

   Now, to solve the scale issue, look for the "Secondary Axis" column. Check the box next to the data set with the different scale - in our case, "MQLs." You will immediately see a preview of how the chart will look with two separate Y-axes.

5. Click OK: Excel will generate a chart that cleanly displays both your columns and your line, each corresponding to its own appropriately-scaled axis.

Formatting Your Combo Chart for Readability

A default chart is good, but a well-formatted chart tells a story. Click on your new chart and start customizing:

• Add Axis Titles: Click the "+" icon next to the top-right corner of the chart and check "Axis Titles." Clearly label your left axis ("Revenue ($)") and your right axis ("Number of MQLs") so nobody has to guess what the numbers mean.

• Use a Clear Chart Title: Replace "Chart Title" with something descriptive like "Monthly Revenue vs. MQLs Generated."

• Adjust Colors: If the default colors clash or don't match your brand, right-click on a column or the line and select "Format Data Series." You can change the fill and line colors to improve visual clarity.

Method 2: Side-by-Side Chart with a Single Axis

Sometimes your two data sets are measured in the same units and on a similar scale. In this case, you don't need a secondary axis. You just want to compare them directly next to each other. For example, comparing the monthly sales of "Product A" vs. "Product B."

How to Create a Side-by-Side Comparison Chart:

1. Organize Your Data: Your data should be set up with a shared label and two data columns, just like before (e.g., Month, Product A Sales, Product B Sales).

2. Select Everything: Highlight all your data, including the headers.

3. Insert a Column or Line Chart: Go to the Insert tab. This time, choose either the "Insert Column or Bar Chart" or "Insert Line or Area Chart" icon. Excel is smart enough to recognize you have two distinct data series and will plot them separately.

4. Choose Your Chart Subtype: For columns, a "2-D Clustered Column" chart works perfectly. For lines, a "Line with Markers" chart is a great choice because it clearly shows the data points for each month.

Excel will automatically assign a different color to each data series and generate a legend so a viewer can easily tell which bar or line represents Product A versus Product B.

Method 3: Scatter Plot Chart to Show Correlation

What if you want to see the relationship between two sets of data, not just their trends over time? A scatter plot is perfect for this. It helps you answer questions like, "Does spending more on advertising lead to more sales?"

For a scatter plot, your data structure is slightly different. You usually won't have a time-based column. You will have two numerical columns that you want to plot against each other - one for the X-axis and one for the Y-axis.

How to Create a Scatter Plot:

1. Set Up Your Data: Place your independent variable (the one you control, like "Ad Spend") in the left column and your dependent variable (the one you're measuring, like "Sales") in the right column.

2. Select Data: Highlight just the two columns of numerical data (do not include the headers this time).

3. Insert Scatter Chart: Go to Insert > Charts and click the icon that looks like a plot of scattered dots, named "Insert Scatter (X, Y) or Bubble Chart." Choose the first option, a simple scatter plot.

4. Add a Trendline (Optional but Recommended): Once the chart is created, click the "+" icon and check "Trendline." Excel will draw a line of best fit through your data points, giving you an immediate visual cue about the correlation. A line trending up and to the right indicates a positive correlation.

Common Mistakes and Quick Fixes

• Problem: One data set looks flat.Fix: Your data sets likely have very different scales. Use a Combo chart and enable the Secondary Axis for one of the series.

• Problem: The chart's horizontal axis shows "1, 2, 3..." instead of months or names.Fix: You likely forgot to select the first column (your labels) when you selected the data. Reselect your data, making sure to include that first column of labels.

• Problem: The chart is too cluttered and hard to read.Fix: Your labels might be too long, or you have too many data points. Simplify your data if possible, give the chart a clear title, and label your axes. Less is often more.

Final Thoughts

Visualizing two different data sets on a single chart is one of the most powerful features in Excel. Whether you need a combo chart to compare differently scaled metrics, a column chart for a direct comparison, or a scatter plot to analyze relationships, the process is straightforward once your data is properly organized. These charts turn a simple table of numbers into a clear story about your business performance.

While mastering charts in Excel is a great skill, the process often begins with the time-consuming work of gathering that data in the first place - logging into different platforms, exporting CSVs, and pasting them together. At Graphed, we automate that entire first step. We connect directly to your marketing and sales tools (like Google Analytics, Shopify, Facebook Ads, etc.) to bring all your data into one place. From there, you can build real-time, interactive dashboards just by describing what you want to see in simple English, giving you back hours to focus on strategy instead of spreadsheet work.