Why Isn't My Scatter Plot Working in Excel?

Are you pulling your hair out trying to create a scatter plot in Excel, but it just won't cooperate? You're not alone. Scatter plots, while powerful for data visualization, can be a bit finicky. Let's troubleshoot this issue and get your scatter plot working in no time.

How to Make Scatter Plot in Excel
How to Make Scatter Plot in Excel

First, let's ensure we're on the same page. A scatter plot is a type of plot using Cartesian coordinates to display values for two variables. The data is displayed as a collection of points, each having the position of both variables' values along the horizontal and vertical axes.

Excel Scatter Plot Tutorial | Step-by-Step Data Visualization Guide for Beginners
Excel Scatter Plot Tutorial | Step-by-Step Data Visualization Guide for Beginners

Understanding Your Data

Before we dive into the technical aspects, let's ensure your data is ready for a scatter plot. Scatter plots are best used when you have two sets of numerical data that you want to compare or explore the relationship between.

Scatterplot - Learn about this chart and tools to create it
Scatterplot - Learn about this chart and tools to create it

For example, you might want to plot the relationship between a company's advertising spend and its sales revenue. In this case, advertising spend and sales revenue would be your two variables. Ensure your data is clean, with no missing or inconsistent values, as this can cause issues when creating the plot.

Check Your Data Types

Learn to create a scatter plot with two series in Excel - INDZARA
Learn to create a scatter plot with two series in Excel - INDZARA

Excel is smart, but it's not psychic. It needs to know what kind of data you're working with. For a scatter plot, both sets of data should be numeric. If your data is text or dates, Excel won't know how to plot it.

To check your data types, select your data and look at the bottom-left corner of the Excel window. You'll see a small box that tells you the format of the selected cells. If it's not 'General' or 'Number', you might need to convert your data.

Convert Data Types if Necessary

a woman is smiling with the text how to make a scatter plot in excel
a woman is smiling with the text how to make a scatter plot in excel

To convert text or date data to numbers, select your data, right-click, and choose 'Format Cells'. In the 'Number' tab, choose 'Number' or 'General' and click 'OK'. For dates, choose 'Custom' and enter a format like 'yyyy-mm-dd' to convert them to serial dates, which Excel can plot.

Remember, converting data types can change the way your data looks, so always double-check your data after converting.

Creating the Scatter Plot

Learn How to Create a Scatter Plot in Google Sheets
Learn How to Create a Scatter Plot in Google Sheets

Now that your data is ready, let's create the scatter plot. Here's a step-by-step guide:

1. Select your data, including the headers. Go to the 'Insert' tab, click on 'Scatter' in the 'Charts' group, and choose the scatter plot type you want.

Scatter Plot Maker Excel | Scatter Diagram | Correlation Analysis
Scatter Plot Maker Excel | Scatter Diagram | Correlation Analysis
The Making of the Weighted Pivot Scatter Plot
The Making of the Weighted Pivot Scatter Plot
Scatterplots in Excel, Regression part 1 - Statistics Help
Scatterplots in Excel, Regression part 1 - Statistics Help
Use Scatter Chart in Excel to Find Relationships between Two Data Series
Use Scatter Chart in Excel to Find Relationships between Two Data Series
Statistics Project: scatter plot, line of best fit, association of data
Statistics Project: scatter plot, line of best fit, association of data
How to Make a Scatter Plot in Excel and Present Your Data
How to Make a Scatter Plot in Excel and Present Your Data
Got a Scatter Plot? Learn How to Add Marginal Histograms
Got a Scatter Plot? Learn How to Add Marginal Histograms
How to Make a Polygon Scatter Plot in Tableau
How to Make a Polygon Scatter Plot in Tableau
Making Horizontal Dot Plot or Dumbbell Charts in Excel – How To
Making Horizontal Dot Plot or Dumbbell Charts in Excel – How To
Dual Axis Colouring of a Scatter Plot
Dual Axis Colouring of a Scatter Plot
Scatter (XY) Plots
Scatter (XY) Plots
Work in Excel Faster Than Ever – Speed Up Your Workflow
Work in Excel Faster Than Ever – Speed Up Your Workflow
This is how to create a scatter plot in Excel using the latest version
This is how to create a scatter plot in Excel using the latest version
a poster with instructions on how to use excel
a poster with instructions on how to use excel
Perfect Scatter Plots with Correlation and Marginal Histograms - Articles
Perfect Scatter Plots with Correlation and Marginal Histograms - Articles
Interactive Line and Scatter Plot Worksheets for Middle School Math
Interactive Line and Scatter Plot Worksheets for Middle School Math
Simply creating various scatter plots with ggplot #rstats | R-bloggers
Simply creating various scatter plots with ggplot #rstats | R-bloggers
a screen shot of the scatter plot in excel
a screen shot of the scatter plot in excel
Top 20 excel problems solved for beginners
Top 20 excel problems solved for beginners
How to Make a Scatter Plot in R with Ggplot2 -
How to Make a Scatter Plot in R with Ggplot2 -

2. Excel will insert a scatter plot based on the data you selected. If your data is not plotted correctly, you might need to switch the rows and columns of your data or tell Excel which columns to use for the horizontal and vertical axes.

Switching Rows and Columns

If your data is not plotted correctly, try switching the rows and columns of your data. In Excel, you can do this by selecting any cell in the row or column you want to move, right-clicking, and choosing 'Move or Copy'. Then, select the destination cell and click 'OK'.

After switching, try creating the scatter plot again.

Specifying Axes

Sometimes, Excel might not guess correctly which columns to use for the horizontal and vertical axes. If this happens, right-click on the plot area and choose 'Select Data'. In the 'Select Data Source' dialog box, click on the 'Horizontal (Category) axis labels' or 'Vertical (Series) axis labels' list and choose the series you want to use.

Click 'OK' to close the dialog box and see if your scatter plot is now correct.

Formatting Your Scatter Plot

Once your scatter plot is working, you can format it to make it more readable and visually appealing. Right-click on the plot area and choose 'Format Selection' to access the formatting options.

Here, you can change the color and style of the data points, add a trendline, or add labels to the axes. Remember, the goal of a scatter plot is to communicate your data effectively, so keep your formatting choices simple and clear.

There you have it! Your scatter plot should now be working perfectly in Excel. If you're still having trouble, double-check your data and make sure you've followed each step carefully. Happy plotting!