How to Create a Scatter Plot from Repeated Data Blocks in Excel
Question details
The user needs to create a scatter plot from multiple data blocks containing x and y coordinates separated by blank rows, without manually adding every single data series.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Visualizing fragmented worksheet data where hundreds of coordinate pairs are grouped into blocks and split by empty rows.
- Observed behavior
- Because the data sets are separated by blank rows, the spreadsheet software treats them as disconnected, requiring the user to add each series one by one manually.
Before attempting to consolidate your columns or create the chart, remove any confidential information from your data and ensure your x and y columns are clearly labeled.
Consolidate Data Blocks to Create a Single Scatter Plot
The most straightforward method to generate a scatter plot without manually adding series is to combine your fragmented data blocks into a single contiguous table.
Spreadsheet software interprets blank rows as boundaries between different data sets. By removing these gaps and restructuring your coordinates into continuous columns, the charting engine can plot the entire dataset automatically.
Highlight the entire range of your worksheet that contains the separated data blocks, including the blank rows.
Press the F5 key on your keyboard to open the 'Go To' dialog box. Click on 'Special' and select the 'Blanks' radio button, then click OK. This will select all the empty rows within your range.
Right-click on any of the highlighted blank cells, select 'Delete', choose 'Shift cells up' or 'Entire row', and click OK to consolidate your x and y pairs into contiguous columns.
Select your newly consolidated, continuous data range. Navigate to the 'Insert' tab on the ribbon, locate the Charts group, and click on the 'Scatter' (X Y) chart icon to generate the plot instantly.

Add Series Programmatically using VBA Macro
If you cannot modify the layout of your worksheet and must keep the blank rows, you can use a VBA macro to loop through the blocks and plot them automatically.
Easily Create Complex Charts with WPS Spreadsheet
WPS Spreadsheet provides powerful data handling and intuitive chart creation tools. You can effortlessly manage large datasets, quickly remove blank rows, and visualize repeated data blocks using its advanced charting features.
- 1. Open Your Data: Launch WPS Spreadsheet and open the document containing your repeated x and y coordinate blocks.
- 2. Clean Up the Layout: Navigate to the Home tab, click on 'Find and Replace', choose 'Go To', and select 'Blanks' to easily delete separating rows.
- 3. Insert the Scatter Plot: Highlight your combined data, go to the Insert tab, and click the Scatter Chart icon to plot your entire dataset perfectly without manual series addition.

Frequently Asked Questions
Why does my spreadsheet create multiple series when my data has blank rows?
Spreadsheet applications interpret blank rows or columns as boundaries between distinct data tables. Consequently, the chart engine stops reading the current series at the blank row, requiring subsequent data blocks to be treated as entirely new series.
Can I plot a scatter chart from non-adjacent columns?
Yes. You can hold down the Ctrl key while selecting different columns of data that are not next to each other. Once your x and y columns are selected, navigate to Insert > Scatter Chart, and the plot will be generated using those specific ranges.
Is there a limit to how many data points a scatter plot can handle?
In modern spreadsheet applications, a single 2D data series can handle up to 32,000 data points, while a single chart can contain up to 255 distinct series. For standard repeated blocks of about 120 rows, you will remain comfortably within these limits.




