How to Plot Points on a Graph in Excel

Trying to show the relationship between two sets of numbers, like ad spend and sales, can feel abstract. Plotting these numeric pairs as points on a graph makes that relationship instantly visible. This tutorial will walk you through exactly how to plot points on a graph in Excel using a scatter chart, whether you have a single set of data or multiple series to compare.

When to Plot Points on a Graph?

Plotting points on a graph is most useful when you want to visualize the relationship, or correlation, between two different variables. This type of chart is known as an XY scatter plot because it uses X coordinates and Y coordinates to represent each point.

The goal is to see if a pattern emerges as one variable changes. Does the second variable also change in a predictable way? You might use a scatter plot to answer questions like:

• Does our daily advertising budget (Variable X) impact the number of daily sales we make (Variable Y)?
• Is there a connection between the average temperature (Variable X) and a local shop’s ice cream sales (Variable Y)?
• How does student study time (Variable X) affect their final exam scores (Variable Y)?

Each point on the graph represents a single instance where you have a value for both variables - like a specific day's ad spend and that same day's sales total. By plotting all these points, you can quickly spot trends, patterns, and outliers that you’d never see in a spreadsheet of raw numbers.

Preparing Your Data for Plotting

Before you can create a graph, your data needs to be organized properly. Excel needs to understand which numbers belong on the horizontal axis (X-axis) and which belong on the vertical axis (Y-axis).

Follow this simple structure:

1. Create Two Columns: Set up your data in two adjacent columns.
2. Column A (X-Axis): Put your independent variable in the first column. This is the variable you control or the one that influences the other. For example, 'Ad Spend.'
3. Column B (Y-Axis): Put your dependent variable in the second column. This is the variable that you measure or that responds to the independent variable. For example, 'Units Sold.'

It's also essential to add clear headers to each column. This isn't just for your benefit - Excel will use these headers to automatically label parts of your chart later on.

Here’s what your data should look like. In this example, we’ll track 'Study Hours' (X-axis) and the resulting 'Exam Score' (Y-axis).

Pro Tip: Ensure that all the data in your two columns is formatted as numbers. If any values are accidentally formatted as text, Excel may ignore them or misinterpret the data, leading to a broken or incomplete chart.

Step-by-Step Guide: Plotting a Single Series of Points

With your data correctly formatted, you're ready to create the graph. It only takes a few clicks.

Step 1: Select Your Data

Click and drag your cursor to highlight the cells containing your data, including the headers. In our example, you would select cells A1 through B10.

Step 2: Insert a Scatter Chart

Navigate to the Insert tab on the Excel ribbon. In the Charts group, locate the icon that looks like a graph with dots scattered on it. This is the Insert Scatter (X, Y) or Bubble Chart option. Click on this icon to reveal a dropdown menu of different chart types.

Step 3: Choose Your Chart Type

For plotting individual points, the best option is the first one, simply labeled Scatter. This will place markers on the graph for each of your XY coordinate pairs without connecting them with lines. Excel will instantly generate the scatter plot and place it on your worksheet. It will automatically use 'Study Hours' for the horizontal X-axis and 'Exam Score' for the vertical Y-axis because of how we arranged our data columns.

Customizing and Formatting Your Graph

The default chart is functional, but a few formatting tweaks can make it much clearer and more professional.

Add a Descriptive Chart Title

The default title is usually just the Y-axis header. Click on the title text to edit it to something more descriptive, like "Correlation Between Study Hours and Exam Scores."

Label Your Axes

A scatter plot is meaningless without clearly labeled axes. If Excel didn't add them automatically, click anywhere on the chart. A plus sign (+) will appear in the top-right corner. Click it, then check the box next to Axis Titles. This will add placeholder text boxes that you can click to edit your X and Y axis titles.

Refine the Axis Scale

Sometimes Excel’s default axes don't frame the data well. For example, our Y-axis (Exam Score) starts at zero, but our lowest score is 55. To make the points fill the chart area more effectively, you can change the scale. Right-click on the Y-axis labels (the numbers running up the side) and choose Format Axis. A pane will open on the right. Under Bounds, you can change the Minimum value from 0.0 to 50 to get a closer look at your data points.

Add a Trendline

A trendline is a straight or curved line that shows the general direction or pattern of the data. It's one of the most powerful features of a scatter plot. Right-click on any of the data points, and from the context menu, select Add Trendline. Excel will automatically add a linear trendline. In our example, the line goes up, indicating a positive correlation: as study hours increase, exam scores tend to increase as well.

Plotting Multiple Sets of Points on One Graph

What if you want to compare two or more data series on the same graph? For example, comparing the exam scores of two different classes. With proper data setup, this is just as easy.

Step 1: Arrange Your Data

First, arrange your data with a shared X-axis column and separate Y-axis columns for each series. Here, we'll keep 'Study Hours' as our X-axis and have two Y-series: 'Class A Scores' and 'Class B Scores'.

Step 2: Select Data and Insert Chart

Select all three columns of data, including the headers. Go to the Insert tab and insert a Scatter chart, just as you did for a single series. Excel is clever enough to recognize that you have one X column and two Y columns. It will plot them as two distinct series of points, each with a different color.

Step 3: Add a Legend

With multiple data series, a legend is necessary to tell them apart. If Excel doesn't add one by default, click the plus icon (+) next to the chart and check the Legend box. The legend will use your column headers to identify each series.

Common Problems and Troubleshooting Tips

• My graph looks like a line chart, not a scatter plot: This is a common mistake. You may have accidentally selected a Line chart instead of a Scatter chart. A line chart plots values against categorical labels on the X-axis (like Jan, Feb, Mar), whereas a scatter plot plots numeric X values against numeric Y values. Always choose "Scatter" from the Charts menu for plotting points.
• The X and Y axes are flipped: If your horizontal and vertical axes are backward, you can easily swap them. Right-click the chart and choose Select Data. In the dialog box, click on a series and press Edit. Here, you can manually re-select the cell ranges for the Series X values and Series Y values to correct the mistake.
• My chart is missing some points: This usually happens if there is text or a non-numeric value in your data range. Go back to your data table and ensure all values are formatted as proper numbers.

Final Thoughts

Mastering the scatter plot in Excel is a fundamental skill for anyone working with data. It’s a clean and effective way to instantly see the relationship between two sets of numbers, helping you move from raw data to actionable insights. Once you know how to prepare your data and use the chart tools, you can create compelling visualizations in just a few minutes.

While Excel is great for these manual analyses, we know that building and maintaining marketing and sales reports across different platforms can take up hours of your week. We created Graphed to automate that entire process. Just connect your data sources like Google Analytics, Shopify, or your CRM once, then ask for the charts and dashboards you need using plain English. Your dashboards stay up-to-date in real-time without you having to re-create them every week, freeing you up to focus on strategy instead of report-building.