How to Create a Multi-Series Scatterplot in Google Sheets

Data visualization is a cornerstone of effective analysis, and the ability to generate a multi-series scatterplot in Google Sheets allows analysts to compare several groups simultaneously on the same axes. This tutorial provides a comprehensive, step-by-step guide to achieving a result similar to the professional visualization shown below:

While the process may seem complex due to the need for specific data reformatting, it is easily managed using simple conditional formulas available natively in Google Sheets. The following sections will guide you through the necessary data manipulation steps required to successfully plot separate series for distinct categories.

Step 1: Setting Up the Initial Dataset

The first critical step involves accurately inputting the raw data into the spreadsheet. For this detailed example, we will utilize a sample dataset that tracks key performance indicators—specifically assists and points—for individual basketball players, categorized by their respective teams. This categorical variable (Team) is what necessitates the creation of multiple distinct series within the final chart.

Ensure that your data is organized into clear columns, identifying the categorical variable (Team) and the two numerical variables (Assists and Points) that will form the X and Y axes of the scatterplot. Consistency in data entry and labeling is crucial for the subsequent reformatting steps. Below is the structure of the sample data we will use:

This raw data structure is standard for recording observations, but it is not optimized for creating separate series plots in Google Sheets without manual intervention. The challenge lies in converting this stacked format into a structure where each team’s data can be treated as an independent pair of X and Y values.

Step 2: Preparing Data for Multi-Series Plotting

Creating separate data series based on a categorical variable (Team) requires a specific restructuring of the worksheet. Before generating the chart, we must transform the data such that the points variable (Points) is conditionally displayed only when its corresponding team matches a designated header. This technique leverages conditional formulas to isolate the data for each group, ensuring that each group becomes a distinct plot series.

Begin by creating new columns dedicated to each unique team name found in your dataset. These unique team names should populate the top row of your new data range (e.g., cells E1, F1, G1, H1). These headers will serve as the criteria for our filtering formula. Next, in the cell immediately below the first new header (cell E2 in this example), input the following conditional formula:

=IF($A2=E$1, $C2, NA())

This Google Sheets formula checks if the value in column A (the Team name, using an absolute reference for the column: $A2) matches the header value in the current column (the specific Team name, using an absolute reference for the row: E$1). If the condition is met (the player belongs to that team), the formula returns the player’s corresponding Points value ($C2). Crucially, if the condition is false, it returns the NA() function, which represents “Not Available.”

The use of NA() is paramount because when Google Sheets plots data, it automatically ignores cells containing this function, preventing the insertion of unwanted zero values or erroneous data points. After entering the formula in E2, you must drag it horizontally across the row until it covers all team headers (up to cell H2 in this case). Subsequently, drag the formula down vertically to apply it to all rows corresponding to your original dataset (down to cell H17).

Step 3: Aligning X-Axis Data

Following the application of the conditional formula, your spreadsheet should now clearly separate the Points data based on the Team category. To finalize the data structure required for plotting, you must now pair the newly calculated points data with the corresponding X-axis variable, which in this example is Assists. Since the conditional filtering only applied to the Points data, the Assists column (Column B) remains unchanged and must be replicated to serve as the constant X-axis reference for all the new series.

Copy all values from the original Assists column (Column B) and paste them directly into a new column, Column D. This column (D) will function as the shared X-axis variable for all subsequent series (E, F, G, H). This crucial step ensures that every team’s points are plotted against the correct assist count, resulting in the final, optimized plotting range (Columns D through H):

The resulting structure ensures that each column (E, F, G, H) exclusively contains the Points data for its respective team, interspersed with NA() values where the condition was not met. Your sheet is now ready for visualization.

Step 4: Creating the Scatterplot

With the data correctly formatted in columns D through H, we are now ready to generate the visualization. Select the entire range encompassing the shared X-axis (Assists in Column D) and all newly created series columns (E through H). Navigate to the “Insert” menu and select “Chart.” Google Sheets will automatically open the Chart editor panel on the right side of your screen.

Within the initial setup tab of the Chart editor, verify that the selected Chart type is set to Scatter chart. Google Sheets recognizes the structure of the data—where the first column (D) serves as the primary X-axis and subsequent columns (E, F, G, H) provide distinct Y-axis values paired with the X-axis—and automatically configures multiple data series based on your column headers.

If the Chart editor does not automatically select the Scatter chart type, use the drop-down menu under “Chart type” to manually select it. This action ensures that the relationship between the two numerical variables (Assists and Points) is correctly visualized for each categorical group:

Step 5: Reviewing the Initial Visualization

Once the chart type is confirmed, the resulting scatterplot will instantly populate your sheet. Because of the preparatory steps involving the conditional filtering and the use of the NA() function, each distinct team will be represented by a unique color, effectively creating a multi-series chart where each series corresponds to a single team.

The resulting visualization provides an immediate comparison of the assists-to-points relationship across all teams simultaneously. The visual differentiation makes it simple to analyze trends and outliers specific to each group. The plot should look similar to this:

Each individual data point on the chart represents a unique basketball player. The plotting location is determined by their respective assists (X-axis) and points (Y-axis), and the colors of the points correspond directly to the teams, confirming that the data formatting process correctly segregated the data by category for plotting purposes.

Step 6: Finalizing and Customizing the Chart

To maximize the clarity and impact of the visualization, it is highly recommended to enter the “Customize” tab within the Chart editor. Here, you can adjust various aesthetic elements, ensuring the final chart is easy for any audience to interpret. Essential customizations include adding a descriptive chart title and accurately labeling both the horizontal (X) and vertical (Y) axes.

Furthermore, within the “Series” section of the customization options, you have granular control over the appearance of each team’s data, including marker shape, size, and color. You can also add trendlines or error bars if further statistical context is required. These refinements transform a raw plot into a professional analytical tool, enabling clearer communication of the underlying data relationships.

The final, polished Google Sheets scatterplot, complete with informative axis labels and a relevant title, clearly illustrates the comparison between the four teams:

Further Google Sheets Tutorials

Mastering multi-series plotting is just one aspect of powerful data analysis in Google Sheets. Utilize the following related resources to expand your analytical toolkit and explore additional common data manipulation and visualization techniques.

• Understanding advanced conditional formatting.

• Generating histograms and box plots.

• Using QUERY function for complex data extraction.