How to Create a Color Coded Scatter Plot in Excel?

Sharing is caring!

Scatter plots in Excel are powerful tools for visualizing relationships between data points. By adding color coding, you can separate categories, highlight trends, or emphasize specific values, making your charts much easier to interpret.

In this guide, you’ll learn step by step how to create a color coded scatter plot in Excel, along with useful formatting tips to make your charts clear and professional.

Why Use Color Coding in Scatter Plots?

Color coding adds meaning and clarity. Instead of reading raw data values, your audience can instantly see groups or categories.

Here are common reasons to use color coded scatter plots:

• Categorical distinction – separate departments, age groups, or regions.
• Highlight ranges – show high, medium, and low performance.
• Cluster visualization – identify natural groupings in your dataset.
• Trend emphasis – show which values belong to a specific trend line.

Preparing Your Data for a Color Coded Scatter Plot

Before you create the chart, ensure your data is structured correctly. Excel requires two numeric columns for X and Y values and one column for category or condition that will define the colors.

Example dataset:

In this dataset:

• Sales is on the X-axis
• Profit is on the Y-axis
• Region defines the color

Creating a Basic Scatter Plot in Excel

Follow these steps to create a simple scatter plot:

1. Select your data (without the category column first).
2. Go to the Insert tab in Excel.
3. Under the Charts group, click Insert Scatter (X, Y) Chart.
4. Choose the basic scatter plot option.

You will now see data points plotted on the chart, but all in the same color.

Methods to Color Code Scatter Plots in Excel

There are multiple ways to color code scatter plots in Excel. Let’s go step by step through the most common approaches.

Method 1: Using Separate Data Series for Categories

This is the most straightforward way to create a color coded scatter plot when you have categorical data.

Steps:

1. Copy your dataset and separate categories into different series.
   Example: Create one series for East, one for West, etc.
2. Insert a scatter plot.
3. Add each category as a separate data series.
   • Go to Chart → Select Data → Add.
   • Select the X values and Y values for that category.
4. Assign different colors to each series.

This method works well for small datasets with a limited number of categories.

Method 2: Using Conditional Formatting with Colors

If you want to color code based on value ranges, such as profits above or below a certain threshold, you can do this with formulas.

Steps:

1. Add a helper column in your data to define conditions.
   Example: If Profit > 450, mark as “High”; else “Low”.
2. Insert a scatter plot as usual.
3. Split your dataset into two series: High and Low.
4. Assign one color for “High” and another for “Low”.

This is especially useful in business analysis when you want to emphasize high performers or underperformers.

Method 3: Using VBA for Dynamic Color Coding

For large datasets, manually separating series can be time-consuming. A faster way is to use a VBA macro to automatically assign colors based on categories.

Example VBA snippet:

Sub ColorCodeScatter()
    Dim cht As ChartObject
    Set cht = ActiveSheet.ChartObjects(1)
    Dim s As Series
    Dim i As Long
    
    For Each s In cht.Chart.SeriesCollection
        For i = 1 To s.Points.Count
            Select Case Cells(i + 1, 3).Value
                Case "East": s.Points(i).Format.Fill.ForeColor.RGB = RGB(0, 112, 192)
                Case "West": s.Points(i).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
                Case "North": s.Points(i).Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
                Case "South": s.Points(i).Format.Fill.ForeColor.RGB = RGB(255, 192, 0)
            End Select
        Next i
    Next s
End Sub

This code assigns different colors automatically based on the “Region” column.

Formatting Tips for Color Coded Scatter Plots

Once you’ve added color, improve the readability of your chart with these tips:

• Add axis titles (Sales, Profit, etc.) for clarity.
• Include a legend to explain what each color represents.
• Use contrasting colors so categories are easy to distinguish.
• Increase marker size for better visibility.
• Add data labels if you want to show exact values.
• Apply trendlines to emphasize overall patterns.

Example: Sales vs. Profit Scatter Plot with Regions

Imagine you want to analyze sales performance by region. By creating a scatter plot with color coding by region, you can quickly see:

• Which region has higher profits
• Which region struggles with lower sales
• Whether there are clusters of performance

Such a chart makes presentations and reports far more effective compared to plain numbers in a table.

Using Excel Chart Design Tools for Better Visualization

Excel has multiple built-in chart design features to make your color coded scatter plots look more professional:

• Quick Layouts – instantly add titles, legends, and gridlines.
• Chart Styles – apply pre-made styles with coordinated colors.
• Format Pane – customize marker shapes, transparency, and outlines.
• Custom Colors – use brand-specific or theme colors for consistency.

By applying these tools, you can create a scatter plot that looks polished and business-ready.

Comparing Different Methods to Create a Color Coded Scatter Plot

Here’s a quick summary comparing the three approaches to color coding scatter plots in Excel:

Common Mistakes to Avoid

While creating color coded scatter plots in Excel, be mindful of these mistakes:

• Too many categories – makes the chart cluttered.
• Poor color choices – similar colors confuse viewers.
• No legend – users won’t know what the colors mean.
• Overlapping points – adjust transparency or marker size to fix.
• Ignoring data preparation – ensure your dataset is clean and consistent.

Final Thoughts

Creating a color coded scatter plot in Excel is a straightforward process once you understand the steps. With proper formatting, clear legends, and the right choice of colors, you can make your scatter plots easy to read and insightful.

Whether for business reports, academic analysis, or personal projects, color coded scatter plots help transform raw numbers into meaningful stories.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.