How to Create Scatter Plots in Excel Like a Pro

The simplest way to create a scatter plot in Excel is to highlight the cells in the two columns that contain your two numeric variables—in this case, the “MONTHS OF EMPLOYMENT” and “MONTHS WITH ABOVE-AVERAGE PERFORMANCE” columns.

Then, go to the “Insert” tab of your Excel menu bar and click on the scatter plot icon in the “Recommended Charts” area of your ribbon.

Excel will automatically create a scatter plot for you in the same sheet as your data, using the first column of your dataset as the horizontal (X) axis, and the second column as your vertical (Y) axis.

A quick note here: in creating scatter plots, a common practice is to make the horizontal axis your “independent variable” and the vertical axis your “dependent variable” (that is, the number that is likely to change based on the value of our independent variable).

For our scenario, the number of months a person has been employed is more likely to affect the number of “above average” ratings they receive, rather than vice versa. That’s why our independent variable—months of employment—is in our data table’s left-hand column, and our dependent variable is in the right hand column.

This automatically-generated graph could use some formatting and cleanup. Taking time to strengthen the skeleton of your graph—everything that isn’t the actual data points—will help make your insights and information stand out.

After removing unnecessary lines, and cleaning up our axes and titles, our graph looks like this:

Now we have a nicely-formatted, single-series scatter plot that uses an identical black circle as a marker for each of our unique data points. From here, we can continue to make modifications and refinements to our graph.

In the scatter plot we’ve just created, there is only one data series, consisting of our entire cadre of pilot program participants. Each participant’s length of employment is plotted on the horizontal axis, and their total of above-average monthly ratings is on the vertical axis.

Let’s assume we wanted to subdivide this data series into multiple series. For instance, our participants are assigned to three different offices worldwide (United States, Canada, and Japan); what if we wanted to color our data markers to represent that person’s location?

In Excel, creating a scatter plot with multiple data series can be done several ways. The easiest is to have a single column in your data containing the X values for all of your data series, and then have a separate column for the Y values of each individual data series.

Let’s take a look at how we could modify our existing data table to do this.

In this table, the “OFFICE” is its own column, and it contains three unique values: United States; Japan; and Canada. Instead, add three new columns to the right of the existing table, and make each OFFICE value the name of one of the columns:

In the cells of those columns, we’ll write a formula that says “If the value of [OFFICE] in this specific row matches the header of this column, then give this cell the same value as the [MONTHS WITH ABOVE AVERAGE PERFORMANCE] column; otherwise, give it a value of #N/A.”

In cell G5, add a formula to decide if the cell should be empty, or should contain the value from cell F5.

When we propagate this formula across our new columns and down all of our rows, the table will look like this:

Copy the Y values from column F into the appropriate column G, H, or I, based on if the OFFICE value in column A matches the header.

As you can see, our “United States” column only has numeric values if the “OFFICE” column value in that row is “United States.”

When you click on the existing scatter plot, you’ll see purple and blue highlighting around the X and Y columns that Excel is currently displaying in that graph.

Click on the scatter plot to highlight the columns Excel is using for the X (purple) and Y (blue) values.

We’d like this graph to show the Y values in the three new columns we’ve just created. To do that, hold your cursor over the edge of the blue rectangle until it becomes a hand, and then drag that rectangle right by a single column, so that it’s highlighting the data underneath “United States.”

Click and hold the blue column, and drag it to the right by a single column.

You might notice that a lot of your data points are now missing! That’s because now, Excel is only using the “United States” column for our Y axis, and Excel won’t draw a data point if there’s an “#N/A” as a Y value.

Not to worry, though: we’ll get all our data points back now, by clicking on the bottom right corner of that blue rectangle and dragging it to the right, so that the rectangle covers all three new columns we’ve created.

Click on the bottom right corner of the blue rectangle, and drag that corner to the right so that all three new columns are highlighted.

All of our data has returned, hooray! And, as you can see, Excel is now using a different color for each of our data series.

Let’s add a legend so that our viewers know what these different colors represent. First, we’ll name each of our data series: right-click on the chart, choose “Select Data,” and add the data series names manually in the pop-up window.

Type in the name of each series, or select a cell from the Excel sheet that contains the name.

Then, you can fine-tune the look of your graph—perhaps you add a legend as a subheader to your title, and pick specific colors for your series—and your multi-series scatter plot is ready to go.

This scatter plot contains the same data as our first one, but the data is now subdivided into three series—one for each office—and is color-coded to reflect that. We’ve also added a legend beneath our chart title to identify which color goes with which office.

Whenever we present data to an audience who might be unfamiliar with it, it’s a good idea to include contextual information to help make it easier to understand. In a scatter plot, we can add context like:

Depending on the insights or the data itself, you might use one (or several) of these techniques, so let’s go through them one at a time.

It’s great that we’ve put our data on the graph…but what does that data actually represent? If we care about more than just the overall distribution of data points, we should add data labels to some, if not all, of our points.

To add data labels to a scatter plot, just right-click on any point in the data series you want to add labels to, and then select “Add Data Labels…” Excel will open up the “Format Data Labels” pane and apply its default settings, which are to show the current Y value as the label. (It will turn on “Show Leader Lines,” which I usually turn off.)

By default, Excel will add the Y values as the data label, and will align it to the right of each data point.

Using only the checkboxes in the “Format Data Labels” pane, you can get your labels to include other values or combinations of values, including the Series name, the X value, and/or the Y value. In many cases, though, we’ll want to customize our data labels to show other information than just those fields. For that, we’ll use the “Value From Cells” option.

You can tell Excel to use any cell or series of cells as its source for Data Label information. For instance, let’s label each of the markers in this chart with the first initial and last name of the pilot program participants. We can add a “Data Label” column to the right of the data table and use the CONCATENATE formula to create a cell in each row with this information.

You can use formulas to fill in your new “Date Label” column, or you can just manually add the data. In this version, we used the CONCATENATE formula to combine the first initial from the FIRST NAME column, a period, and the full LAST NAME column value for every row.

Then, click on a data label in the existing scatter plot. That brings up the “Format Data Labels” pane, where you can change the settings in the “Label Contains” section to use “Value From Cells”. Clicking on the “Select Range…” button brings up a popup window asking what cells to use for the data label information, and you can highlight all the cells in that brand-new column.

To reduce the amount of text in the graph, let’s only keep labels for those participants who were far above or far below the trendline—in other words, overachievers and underachievers.

Double-click on one of the labels you want to remove, and then either delete it or set its Text Fill to “No Fill.” Do the same to several other unwanted labels, until you’re left with just high- and low-performing participants. (You can also click-and-drag labels slightly out of their default positions, to make labels with close-by neighbors easier to read.)

With the techniques described above, you should be well prepared to create a scatter plot in Excel that can be designed and formatted to support the specific story you intend to tell. Use the power pairing of color and words to help your audience see what you want them to notice and learn from your scatter plot. Feel free to include annotations inside or near the chart area itself, as well as large headline or takeaway text above your visualization to emphasize the key messages.

The final version of our visualization includes a takeaway title, annotations, a reference line, and custom labels on selected elements in our mult-series scatter plot.

Now, you’re ready to create your own scatter plots in Excel! The data and the graphs you see in this post (along with some bonus content) can be downloaded here.

Check out our chart guide for more about scatter plots and other graph types; subscribe to our YouTube channel for Excel tutorials and lots of other videos; and follow our blog to get the latest in how-to instructions, graph makeovers, and other tips and tricks for communicating more effectively with data.

Scatter plots are one of the most useful chart types in Excel They allow you to visualize the relationship between two sets of values in a graphical format.

For example, you could plot revenue over time to view trends or plot age vs. height to see correlations. The scatter plot will make it far easier to see patterns in your data

In this comprehensive guide. you’ll learn

  • Exactly what a scatter plot is and when to use it
  • Step-by-step instructions on building scatter plots in Excel
  • How to customize colors, add trendlines, and format your plots
  • Advanced tips for data analysis with scatter plots
  • Common errors and how to avoid mistakes

Follow along to become an Excel scatter plot pro!

Scatter Plot Basics

A scatter plot displays data points on a graph using X and Y coordinate values. Each data point is represented by a marker.

The position of the marker depends on its X and Y values. For example, a data point with X=5 and Y=10 would be positioned at (5, 10) on the graph.

Ideally, you should use scatter plots when trying to determine if two variables are correlated. The pattern of the data points will reveal if there is a positive, negative, or non-existent relationship between the variables.

Some examples of using scatter plots:

  • Relating height and weight
  • Looking at sales over time
  • Comparing temperature and plant growth
  • Analyzing test scores vs study time

Basically any time you want to visualize correlations between quantitative values, a scatter plot is the way to go.

Creating Your First Scatter Plot

Let’s walk through building a simple scatter plot in Excel using two sets of sample data:

  1. Open a blank Excel workbook.
  2. Enter values in two columns labelled X and Y.
  3. Highlight both columns with your data.
  4. On the Insert tab, click the Scatter button.
  5. Select the scatter plot style you want.

A few notes:

  • Your X values will appear along the horizontal axis.
  • Your Y values will appear along the vertical axis.
  • Each (X,Y) pair will be plotted as a point on the chart.

And that’s all there is to getting started with scatter plots! Now let’s explore how to customize your chart.

Formatting Your Scatter Plot

The default scatter plot may need some tweaking to match your presentation needs. Excel provides numerous formatting options on the Design and Format tabs.

Some formatting ideas to try:

  • Change chart layout – Emphasize certain elements.
  • Modify data points – Colors, shapes, borders.
  • Add data labels – Display values beside points.
  • Include trendline – Show linear relationship.
  • Adjust axis scales – Zoom in on key data ranges.

Don’t be afraid to experiment with styles until you find one that looks professional and conveys the relationships clearly.

When finalizing the format, ensure the data takes center stage rather than decorative elements.

Adding Projected Trendlines

One useful customization for scatter plots is adding a trendline. This shows the general direction and rate of change in the data rather than just the individual data points.

To add a trendline:

  1. Click any data point to select the scatter plot.
  2. On the Design tab, click Add Chart Element.
  3. Choose Trendline and select the type you want.

Linear trends are most common, but you can also do exponential, logarithmic, polynomial, and moving average trendlines.

The value of R-squared will indicate how closely the trendline fits your actual data. Strive for an R-squared value as near to 1 as possible for a good fit.

Linking to Source Data

If you use scatter plots for ongoing data analysis, it’s crucial to link them to the source data. This allows the chart to update automatically when the data changes.

Here’s how to link a scatter plot:

  1. Select the plot and go to the Design tab.
  2. Click Select Data in the Data group.
  3. Click the Edit link next to the data ranges.
  4. Adjust the range references to your source data cells.

Now whenever the data is changed, the scatter plot will reload with the new values.

Adding Axis Labels and Titles

Don’t forget to clearly label your scatter plots! Follow these steps:

  1. Click the scatter plot to select it.
  2. Click the Layout tab under Chart Tools.
  3. Add text for the Horizontal Axis Title, Vertical Axis Title and Chart Title.
  4. Use bold title case text and include units.

Good labels add essential context for readers trying to interpret the data relationships shown in your scatter plot.

Troubleshooting Errors

Creating your first few scatter plots will involve a bit of trial and error. Here are some common mistakes to avoid:

  • Empty plot – Make sure data is fully highlighted before inserting.
  • #DIV/0! errors – Check for blank cells or invalid data types in your data range.
  • Overlapping data – May need to adjust axis minimum and maximum values.
  • Sparse distribution – Outliers can skew trendline, consider trimming dataset.

Don’t get discouraged if your early scatter plots look wonky! Just take your time and double check each step. Soon it will be second nature.

Digging Deeper Into Your Data

Once you have the basics down, explore more advanced ways to analyze data with scatter plots:

  • Add regression formulas – Display the mathematical relationship.
  • Color code subgroups – Visually highlight clusters.
  • Overlay plots – Compare trends by graph type.
  • Include error bars – Indicate variability around means.
  • Animate time series data – View shifts over time.

Think beyond 2D point plots on an X/Y axis. You can build stunning 3D and even 4D scatter plots with the power of Excel!

Scatter Plot Use Cases

Now that you’re a scatter plot expert, here are some real-world examples where they come in handy:

  • Product demand forecasting – Correlate promotions to sales spikes.
  • Quality control – Visualize defect rates over production volume.
  • Drug trials – Relate dosage to treatment effectiveness.
  • Sports analytics – Are wins correlated to payroll?
  • Demographics research – Plot income vs family size.

Any quantitative data that may have an interesting mathematical relationship can be analyzed with scatter plots.

Share Your Scatter Plot Insights

The insights gleaned from building scatter plots are only useful if shared with stakeholders who can take action.

Rather than awkwardly trying to explain the plots verbally, just send the Excel workbook containing your charts. This allows recipients to engage with the interactive data visualizations firsthand.

Some sharing best practices:

  • Export static images when sending in emails or presentations.
  • Consider dashboards apps like Power BI to reach wider audiences.
  • Remove confidential metadata if sharing externally.

With great data visualization comes great responsibility. Ensure your scatter plots enlighten audiences rather than confuse them!

Level Up Your Excel Analytics Game

You’re now fully equipped to use scatter plots to uncover correlations, trends, and patterns in all kinds of data sets.

The next time you’re faced with large sets of figures that seem meaningless on their own, don’t just stare blankly at columns of numbers.

Fire up Excel, build a scatter plot, and let the visualizations speak for themselves. You’ll be amazed what kind of hidden insights you can uncover.

So embrace scatter plots as a simple but powerful analytics tool that’s just a few clicks away. Start plotting your data the smart way and take your Excel skills to new heights!

how to make scatter plots in excel

How to add an average point to a scatter plot

We’ll start by adding another row to the bottom of our data table, where we’ll calculate what the average X and Y values were. For simplicity’s sake, we’re only going to look at the average across ALL of our participants, rather than separate averages for each office (although the same techniques would apply).

Since columns E and F contain our X and Y values, we’ll write formulas to average the values in each of those columns.

how to make scatter plots in excel

Row 24 will contain the averages of our X values (in column E) and our Y values (in column F). Use the AVERAGE formula, as shown in this screen capture, to generate the correct values.

Then, we’ll right-click on our chart, choose “Select Data…” from the menu that pops up, and add another data series just for our average values. Click the “+” button below the “Legend entries (Series):” window to add a new series, which you can then rename and set the range of X and Y values for in that pop-up.

how to make scatter plots in excel

The “average” series will consist of a single point, showing the average of all X values and the average of all Y values.

That will put a single point on our chart that marks our X and Y averages, as you can see in the updated graph below.

I’ve deleted the new “average” series from the legend (by clicking once to highlight the legend, then a second time to highlight the average, and hitting “delete”), and formatted the single point to be gray with a black outline.

how to make scatter plots in excel

The average of X and Y values across all three data series is now shown in a single gray marker, outlined in black.

How to add reference lines and create quadrants in a scatter plot

Now that we have an average point, we can visually break our scatter plot into four quadrants:

  • above average in both X and Y variables;
  • below average in both;
  • high X but low Y; and
  • low X but high Y.

We’ll use our average point as the basis for drawing the lines that define those quadrants. Specifically, we’ll add a chart element to that point, and maybe not the one that you’d immediately expect: error bars.

Error bars are typically intended to show additional statistical context around a data point. Instead, we’ll be using them to draw both a vertical and a horizontal line, each of which will connect our axes to the edges of our graph, running directly through our average point.

First, we’ll need to know how long to make these error bars.

  • We know that each of our axes has a maximum value of 50, so we’ll make sure our error bars cover that total distance.
  • Vertically, we’ll draw one bar from the baseline to our average point, and one bar from our average point to the top of our graph.
  • Horizontally, we’ll draw one bar from our Y-axis to our average point, and then one bar from the average point to the right-hand edge of our graph.

To define the exact length of these error bar segments, we’ll add one more row of data to our table. Below the AVERAGE row, we’ll add a row called “Upper bound.” The value in those cells is calculated as “50-[average value]”.

how to make scatter plots in excel

Add an “Upper bound” row to the data set in preparation for building the quadrant lines.

Now, to add our error bars to the graph: click on the “average” data point in the chart, and then go to the Chart Design > Add Chart Element option in the ribbon, and select “Error Bars > More Error Bars Options…

how to make scatter plots in excel

Here’s where to find the “More Error Bars Options” item in the drop-down menus.

This will open up a Format Error Bars pane on the right side of the screen. (At this point, Excel defaults to having you format the vertical error bars; we’ll get to the horizontal bars in a minute.) You’ll see options for “Direction” (select Both), “End Style” (select No Cap), and “Error Amount” (select Custom).

how to make scatter plots in excel

This screen shows the correct settings under “Format Error Bars.”

By selecting a Custom option under Error Amount, you’ll have to then click on the “Specify Value” button. In that popup window, tell Excel how far above and below your “average” point you want your vertical error bar to be drawn. That’s why we created that “upper bound” row of data earlier. For the “Positive Error Value” in the popup box, use the value from the “upper bound” row, and for the “Negative Error Value,” use the “AVERAGE” row value.

how to make scatter plots in excel

The vertical error bars are sized correctly, but the horizontal ones look like low-res wagon wheels. That’s what we’ll fix next.

Then, click directly on the horizontal error bars in the plot and follow the same steps to modify those bars, using the AVERAGE and Upper bound rows for the MONTHS OF EMPLOYMENT column.

(Note: these bars might be really small by default; if you can’t click on them, then click anywhere in your graph, then go to the “Format” menu next to “Chart Design”, and in the drop down menu on the far left of the ribbon, select “Series ‘average’ X Error Bars”.)

how to make scatter plots in excel

Quadrant lines are drawn, but could still use some re-formatting.

Once that’s done, you can modify the format of the lines and the average point however you like. I prefer to push these reference lines visually toward the background, and to make the average point itself invisible by turning off its data marker. (Careful! Don’t delete the data marker entirely, because that will also make the error bars disappear.)

how to make scatter plots in excel

With some formatting changes, our error bars have become perfectly sized quadrant lines, faded nicely into the background.

By drawing quadrants, I can see right away that we have two employees from the Japan office in the top left who have been above average on performance frequently, even though they’re among the newest 50% of program participants. Conversely, the yellow mark in the bottom right shows me that one participant has been here for a long time, but is far below the 50th percentile in terms of above-average performance periods. I don’t know if this is an interesting story, or the most important insight, but simply drawing the quadrants on the graph makes it easier for me to analyze, and talk about, some of the data points within it.

How to Make a Scatter Plot in Excel

How do I create a scatter plot in Excel?

First, highlight the data you want to use for your scatter plot. Select the “Insert” tab on teh ribbon and click the Insert Scatter option in the Charts group. Choose the type of scatter plot you want to insert. A scatter plot, sometimes referred to as a scatter chart or XY chart, compares the relationship between two different data sets.

How to create a scatter chart in Excel?

Click the drop-down for Insert Scatter (X, Y or Bubble Chart icon under Charts group. Choose Scatter. This command will insert a scatter chart based on the selected data range. There are a few types of scatter charts available in Excel: When you have a few points of data in Excel, then a scatter chart with lines is the most user-friendly option.

How to create a scatter plot with straight lines in Excel?

To create a scatter plot with straight lines, execute the following steps. 1. Select the range A1:D22. 2. On the Insert tab, in the Charts group, click the Scatter symbol. 3. Click Scatter with Straight Lines. Note: also see the subtype Scatter with Smooth Lines. Result: Note: we added a horizontal and vertical axis title.

How to add data labels to a scatter plot in Excel?

By default, data labels are not visible when you create a scatter plot in Excel. But you can easily add and format these. Do add the data labels to the scatter chart, select the chart, click on the plus icon on the right, and then check the data labels option.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *