Chart & Visualization Issues

How to Create a Scatter Plot with Filters in Excel

Phi Hung Vo Oct 10, 2026 870 views

Question details

The user needs to create an XY scatter plot in Excel that allows filtering by specific categories like depot and year, but is facing issues with data selection and PivotTable limitations.

How to Create a Scatter Plot with Data and Filters in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Visualizing numeric data points using a scatter chart and filtering the displayed results dynamically.
Observed behavior: Excel disables the scatter chart option when attempting to create it directly from a PivotTable, requiring a different approach to data selection and filtering.

Before you start

Ensure your dataset contains numeric values for both the X and Y axes, as scatter plots cannot plot textual or categorical data directly on the axes.

Solution 1Recommended

Create a Scatter Chart Directly from Source Data

Bypass PivotTable restrictions by generating the scatter plot directly from your raw data table and using standard table filters.

Because Excel does not support creating scatter charts directly from PivotTables, the most straightforward approach is to use your raw data. By converting your data into an Excel Table, you can apply filters that will automatically update the scatter plot.

Format as Table

Select your entire dataset and press Ctrl+T to convert it into an Excel Table. This adds filter dropdowns to your column headers.

Select Numeric Data

Highlight the numeric columns you want to plot (e.g., 'Days Between' and 'Year').

Insert Scatter Chart

Navigate to the Insert tab, click on the Scatter Chart icon in the Charts group, and select 'Scatter'.

Apply Filters

Use the filter dropdown arrows on your Table headers (like 'Depot' or 'Year') to filter your data. The scatter chart will automatically hide the filtered-out data points.

Create a Scatter Chart Directly from Source Data

Formatting Tip: If you need specific data points to appear as distinct series, you can right-click the chart, choose 'Select Data', and add each series manually.

Solution 2

Use Power Query and Data Model for Dynamic Slicers

Use advanced Excel tools like Power Query and Power Pivot to create a dynamic chart setup with slicers that resembles a filtered scatter plot.

Data Visualization

Create Filtered Scatter Plots Effortlessly in WPS Spreadsheet

WPS Office Spreadsheet provides an intuitive, lightweight environment to handle massive datasets, instantly generate scatter plots, and apply auto-filters without encountering complex PivotTable errors.

1. Open Your Data File: Launch WPS Spreadsheet and open your existing dataset.
2. Select the Target Columns: Highlight the numeric columns intended for your X and Y axes.
3. Insert the Scatter Plot: Go to the Insert tab, click the Chart icon, select 'XY (Scatter)', and choose your preferred scatter style.
4. Filter the Data: Highlight your column headers, go to the Data tab, and click 'AutoFilter'. Use the dropdowns to filter categories dynamically on your new chart.

Free to download and use for all your data analysis needsSeamlessly compatible with Microsoft Excel (.xlsx) file formatsIntuitive chart insertion and formatting interfaceRobust table filtering tools that update charts in real-time

microsoft office alternative - wps office

Frequently Asked Questions

Why is the scatter chart option grayed out when I select my data?

Excel disables scatter charts if you are attempting to create one directly from a PivotTable. To fix this, you must select raw data from a standard worksheet range or an Excel Table instead of the PivotTable.

How do I plot multiple categories as different colors in a scatter plot?

To display categories in different colors, you need to add them as separate series. Right-click the chart, choose 'Select Data', and click 'Add' to define the X and Y ranges for each specific category individually.

Can I put text categories on the X-axis of an XY scatter chart?

No, true XY scatter charts strictly require numeric data for both the X and Y axes. If you want text labels on the X-axis and numeric values on the Y-axis, use a Line chart and format the data series to show markers without lines.

Excel & Data Problems