How to Plot Two Data Sets in Excel

Plotting two related data sets on a single Excel chart is one of the fastest ways to see how metrics influence each other. Instead of flipping between two separate graphs, a combined view helps you instantly compare trends, spot correlations, and tell a more complete story with your data. This article guides you through the best methods for visualizing two data sets in Excel, from direct comparisons to charts that handle different scales and units.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

First, Get Your Data Ready for Charting

Before you create any charts, organizing your data correctly is the most important step for a smooth process. A little bit of prep saves you a lot of headaches later on. Follow these simple rules for your spreadsheet layout.

• Organize in Columns: Structure your data vertically in columns. Your first column should almost always be your shared x-axis label, like a date, a month, or a category name (e.g., Product A, Product B). The subsequent columns will be the different numeric data sets (your y-axes) that you want to plot.
• Align Your X-Axis: Ensure that the values for both data sets line up with the correct label in the first column. For example, if you're plotting website sessions and sales by month, make sure the session count for January and the sales figure for January are in the same row.
• Keep it Clean: Make sure your data is formatted as numbers (or dates, where appropriate) and remove any empty rows within your dataset, as these can confuse Excel's chart generator.

Here’s what a well-structured dataset for plotting "Ad Spend" and "Clicks" over time looks like:

Month | Ad Spend ($) | Clicks

Jan | $500 | 1,200

Feb | $750 | 1,850

Mar | $600 | 1,500

With your data properly organized, you're ready to start visualizing.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 1: Create a Combo Chart to Visualize Different Units

What if you want to compare two metrics that are measured differently? For instance, comparing the number of units sold (a simple count) with the total revenue (in dollars). The raw numbers are on completely different scales - revenue might be in the thousands, while units sold are in the hundreds. Plotting them on the same axis would make the "units sold" line look frustratingly flat and meaningless. The solution is a combination chart with a secondary axis.

When to use a Combo Chart:

• When you are comparing data sets with different units (e.g., dollars vs. percentages, quantities vs. average ratings).
• When the scale of one data set is drastically different from the other, even if they share the same unit.

Step-by-Step Guide to Creating a Combo Chart

Let's use an example where we want to plot monthly Sales Revenue ($) and Units Sold.

1. Select Your Data: Highlight all the data you want to include in the chart, including the column headers and the x-axis labels (in this case, the months).
2. Insert the Chart: Navigate to the Insert tab on Excel's ribbon. In the Charts section, click on Recommended Charts.
3. Choose the Combo Chart: Excel is pretty smart and will often suggest a combo chart if it detects two different data scales. If not, click on the "All Charts" tab at the top of the window and select "Combo" from the list on the left.
4. Customize Your Chart Type and Axes: In the Combo chart menu, you'll see your two data series ("Sales Revenue" and "Units Sold"). You can choose a chart type for each. A common approach is a "Clustered Column" for one and a "Line" for the other.
5. Add a Secondary Axis: This is the key step. Since "Sales Revenue" is on a much larger scale, find that data series in the list and check the "Secondary Axis" box next to it. You will see a preview of the chart update immediately. The left vertical axis will represent Units Sold, while a new axis on the right will represent Sales Revenue. This allows both data sets to be displayed clearly.
6. Click OK: Once you're happy with the preview, click "OK" to create the chart.

Now you have a single, easy-to-read chart that visually tells the story of how your unit sales and total revenue relate to each other over time.

Method 2: Use an XY Scatter Plot for Finding Correlations

Sometimes your goal isn't to see a trend over time, but to understand the relationship between two different variables. Do they move together? Does one increase as the other decreases? This is a question of correlation, and the best tool for the job is an XY Scatter Plot.

When to use an XY Scatter Plot:

• When you want to see if a relationship exists between two different numeric variables.
• Both the horizontal (x) and vertical (y) axes represent numeric values (unlike a line chart where the x-axis is often a time series or category).
• Examples: Tying advertising spend to sales, rainfall to crop yield, or hours studied to test scores.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step Guide to Creating a Scatter Plot

Let's find out if there's a relationship between daily Ad Spend and Website Clicks.

1. Arrange Your Data: Your data should be in two adjacent columns. The independent variable (the one you control or change, like Ad Spend) should be on the left, and the dependent variable (the one you measure, like Website Clicks) should be on the right.
2. Select the Data: Highlight just the two numeric data columns. Unlike other charts, you typically skip the labels or headers for the initial selection to make sure Excel interprets the data correctly.
3. Insert the Chart: Go to the Insert tab. In the Charts group, find the icon that looks like a plot of dots and click it. This is the "Insert Scatter (X, Y) or Bubble Chart" menu. Select the first option, the basic Scatter plot.
4. Interpret the Plot: Excel will generate a chart showing your data as a collection of points. Look at the pattern of these points.
5. (Optional) Add a Trendline: To make the correlation clearer, you can add a trendline. Right-click on any of the data points and select "Add Trendline..." from the context menu. A line will appear that shows the general direction of your data, making the relationship visually obvious.

Method 3: Plot Multiple Series on a Single Axis for Direct Comparison

The simplest scenario is when you want to compare two data sets that are measured with the same unit and on a similar scale. This could be comparing the sales of two different products, website traffic from two different sources, or monthly expenses for two different departments.

When to use this method:

• When both data sets share the same unit of measurement (e.g., all in dollars, all are visitor counts).
• When the values of both data sets fall within a reasonably similar range.

Step-by-Step Guide to Plotting Multiple Lines

Let's visualize the monthly sales for "Product A" and "Product B".

1. Set up your data: Have your months in Column A, Product A sales in Column B, and Product B sales in Column C.
2. Select everything: Click and drag to highlight all three columns, including the headers.
3. Insert Line Chart: Go to the Insert tab, and click the "Insert Line or Area Chart" icon. Choose the "Line with Markers" option for a clear visual.
4. Excel Does the Work: In most cases, Excel automatically recognizes the setup. It will use the "Month" column for the horizontal axis and plot "Product A" and "Product B" as two separate lines on the vertical axis. It will also create a legend to help you distinguish between the two.

Troubleshooting Tip: What if the chart looks wrong?

Sometimes, Excel gets confused and might plot a data series incorrectly. If this happens, right-click on your chart and choose "Select Data." This opens a dialog box where you can manually add, edit, or remove data series, and correctly assign your horizontal axis labels. It gives you full control to fix any misinterpretations.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Best Practices for Customizing Your Chart

Creating the chart is only half the battle. Making it clear, understandable, and professional is just as important.

• Write Clear Titles: Be descriptive. Instead of "Chart," name it "Monthly Sales Revenue vs. Units Sold, Q1." Anyone should be able to understand the chart’s purpose just from the title.
• Label Your Axes: Never leave axes unlabeled. For a combo chart, it's especially critical to label both the primary and secondary vertical axes so viewers know which line corresponds to which scale.
• Use a Legend: Excel adds one by default, but make sure it’s clear and positioned where it doesn’t obstruct the view of the data.
• Consider Colors and Formatting: Use contrasting colors for your data series to make them easy to differentiate. For line charts, consider using different line styles (e.g., solid vs. dashed) for better accessibility for color-blind viewers.

Final Thoughts

Visualizing two data sets on a single chart in Excel is an essential skill for turning raw numbers into compelling insights. By choosing the right chart type - whether it's a combo chart for different scales, an XY scatter for correlations, or a simple multi-line chart for direct comparison - you can uncover relationships that might otherwise stay hidden in your spreadsheet.

Getting your data into an Excel spreadsheet is often the most time-consuming part, especially when you have to manually export CSVs from platforms like Google Analytics, Shopify, Facebook Ads, and Salesforce every week. At Graphed, we eliminate that entire process. We connect directly to your marketing and sales data sources, so you can build real-time charts and dashboards just by describing what you want to see. Instead of wrestling with data sets, you can just ask, "Create a combo chart showing website traffic from Google Analytics and revenue from Shopify," and get an answer in seconds - no manual downloads required. Discover a faster way to get the insights you need with Graphed.