5 Ways To Create Excel Scatter Plot Multiple Series

Excel scatter plots are a great way to visualize the relationship between two datasets. But what if you want to compare multiple series of data? Fortunately, creating an Excel scatter plot with multiple series is a straightforward process. In this article, we'll explore five ways to create an Excel scatter plot with multiple series.

What is an Excel Scatter Plot?

An example of a simple Excel scatter plot

Before we dive into creating multiple series, let's quickly cover what an Excel scatter plot is. A scatter plot, also known as an XY chart, is a type of chart that displays the relationship between two datasets. Each data point is represented by a marker on the chart, and the x-axis and y-axis represent the two datasets.

Method 1: Using the Built-in Scatter Plot Feature

Using the built-in scatter plot feature in Excel

One of the easiest ways to create an Excel scatter plot with multiple series is to use the built-in scatter plot feature. Here's how:

Select the data you want to plot, including the headers.
Go to the "Insert" tab in the ribbon.
Click on the "Scatter" button in the "Charts" group.
Select the "Scatter with only markers" option.
Right-click on the chart and select "Select Data".
In the "Select Data Source" dialog box, click on the "Add" button to add a new series.
Select the data for the new series and click "OK".

Adding Multiple Series to the Scatter Plot

To add multiple series to the scatter plot, simply repeat the steps above for each series. Each time you add a new series, it will be displayed on the chart with a different color and marker style.

Method 2: Using a PivotTable

Using a PivotTable to create a scatter plot with multiple series

Another way to create an Excel scatter plot with multiple series is to use a PivotTable. Here's how:

Select the data you want to plot, including the headers.
Go to the "Insert" tab in the ribbon.
Click on the "PivotTable" button in the "Tables" group.
In the "Create PivotTable" dialog box, select a cell to place the PivotTable and click "OK".
Drag the fields you want to plot to the "Rows" and "Columns" areas of the PivotTable.
Right-click on the PivotTable and select "PivotChart".
In the "PivotChart" dialog box, select the "Scatter" option and click "OK".

Customizing the PivotTable Scatter Plot

To customize the PivotTable scatter plot, you can use the various options available in the "PivotTable Tools" tab. For example, you can change the marker style, add a trendline, or modify the axis labels.

Method 3: Using a Formula

Using a formula to create a scatter plot with multiple series

If you want to create a scatter plot with multiple series using a formula, you can use the SERIES function in Excel. Here's an example:

Select the cell where you want to display the scatter plot.
Enter the following formula: =SERIES("Series 1",A1:A10,B1:B10)
Replace "Series 1" with the name of the series, and A1:A10 and B1:B10 with the data ranges for the x and y values.
Press Enter to create the scatter plot.
To add multiple series, simply repeat the formula for each series.

Customizing the Formula Scatter Plot

To customize the formula scatter plot, you can modify the formula to change the marker style, add a trendline, or modify the axis labels.

Method 4: Using a Third-Party Add-in

Using a third-party add-in to create a scatter plot with multiple series

There are several third-party add-ins available that can help you create a scatter plot with multiple series in Excel. Some popular options include:

Power BI
Tableau
QlikView

These add-ins provide a range of features and tools to help you create interactive and dynamic scatter plots.

Advantages of Using a Third-Party Add-in

Using a third-party add-in can provide several advantages, including:

Advanced features and tools
Improved data visualization capabilities
Enhanced interactivity and dynamic features

Method 5: Using VBA Macros

Using VBA macros to create a scatter plot with multiple series

If you're comfortable with VBA programming, you can create a scatter plot with multiple series using VBA macros. Here's an example:

Open the Visual Basic Editor in Excel.
Create a new module and insert the following code: Sub CreateScatterPlot()
Use the Chart object to create a new scatter plot.
Use the Series object to add multiple series to the scatter plot.
Use the Axis object to customize the axis labels and titles.

Customizing the VBA Macro Scatter Plot

To customize the VBA macro scatter plot, you can modify the code to change the marker style, add a trendline, or modify the axis labels.

Gallery of Excel Scatter Plots

Excel Scatter Plot Examples

Example of a scatter plot with multiple series

Example of a scatter plot with a trendline

Example of a scatter plot with customized axis labels

Example of a scatter plot with multiple markers

Example of a scatter plot with a secondary axis

Example of a scatter plot with error bars

In conclusion, creating an Excel scatter plot with multiple series is a straightforward process that can be achieved using a variety of methods. Whether you use the built-in scatter plot feature, a PivotTable, a formula, a third-party add-in, or VBA macros, the key is to choose the method that best suits your needs and skill level.