How to Create an Excel XY Scatter Chart from Separate Data Ranges
Question details
The user needs to create an XY Scatter chart using data split across separate X and Y columns and wants to understand why PivotCharts do not support this chart type.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Plotting variable data points visually when the target X and Y coordinates are stored in non-adjacent columns or complex datasets.
- Observed behavior
- PivotCharts do not support XY Scatter plots natively, requiring users to rely on alternative methods like manual series addition or data filtering to plot separated X and Y ranges.
Ensure your separate X and Y data columns contain an equal number of data points. Additionally, confirm that your data is not stored in a PivotTable format, as PivotCharts fundamentally do not support scatter plots.
Manually Select Data for the Scatter Chart
The most reliable method to create a scatter plot from separated columns is to insert a blank chart and manually define the non-adjacent X and Y value ranges.
By bypassing the automatic chart generation, you gain full control over which columns act as the X-axis and Y-axis, effectively avoiding formatting errors.
Click on any empty cell outside your dataset. Go to the 'Insert' tab on the ribbon, find the Charts group, and select the 'Scatter' chart icon to insert a blank canvas.
Right-click anywhere inside the blank chart area and choose 'Select Data' from the context menu.
In the Select Data Source dialog box, click the 'Add' button located under the Legend Entries (Series) section.
Click inside the 'Series X values' box and highlight your separated X column data. Then, clear the 'Series Y values' box, select your Y column data, and click 'OK'.
Use Paste Special to Add Data as a New Series
If you already have a chart started, you can quickly append non-adjacent data ranges to the existing scatter plot using the Paste Special feature.
Use the FILTER Function to Reorganize Ranges
Extract specific X and Y values into clean, adjacent ranges using Excel's FILTER function before plotting, which is especially useful for dynamic datasets.
Create Professional Scatter Charts Effortlessly with WPS Spreadsheet
WPS Spreadsheet provides robust, intuitive charting tools to handle separated data ranges with ease. Fully compatible with Microsoft Excel files, it allows you to build complex XY Scatter charts, filter data, and customize visualizations without encountering strict PivotChart limitations.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the separated X and Y columns.
- 2. Insert a blank Scatter chart: Navigate to the Insert tab, find the Chart group, and select the XY (Scatter) icon to place an empty chart canvas on your sheet.
- 3. Select non-adjacent data: Right-click the chart, choose 'Select Data', and use the 'Add Series' dialog to individually select your separate X-axis and Y-axis cell ranges.
- 4. Customize your chart: Click the chart elements icon to quickly add trendlines, axis titles, and data labels to enhance your scatter plot's readability.
Frequently Asked Questions
Why can't I create an XY Scatter Chart from a PivotTable?
Spreadsheet software natively restricts PivotCharts from supporting XY Scatter, Bubble, and Stock chart types due to the way PivotTables aggregate data. To create a scatter plot from aggregated data, you must copy the PivotTable data to standard cells or use lookup formulas to extract it first.
How do I ignore blank cells or missing data points in my scatter chart?
If you have blank cells, the chart may plot them as zeros or leave gaps. To prevent this, replace the empty or incorrect values with the formula `=NA()`. Scatter charts are designed to ignore `#N/A` errors, ensuring a clean and continuous data plot.
Can I plot multiple sets of separate X and Y columns in one chart?
Yes. After setting up your initial scatter chart, right-click it and select 'Select Data'. Click the 'Add' button to create a new series, and manually select the subsequent pair of separate X and Y ranges you wish to overlay on the same chart.
Is there a shortcut to select non-adjacent columns for a chart?
Yes. You can hold down the 'Ctrl' key (or 'Cmd' on Mac) while clicking and dragging over your X column, and then repeat the action over your Y column. Once both separated columns are highlighted, go to the Insert tab and select the Scatter chart.
