How to Create an Excel Scatter Plot with Dates on Both Axes
Question details
The user wants to create a scatter plot that displays date values on both the horizontal (X) and vertical (Y) axes, but the chart appears empty due to incorrect data structure or ranges.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Visualizing date-based data pairs by plotting them on a custom scatter chart.
- Observed behavior
- The generated scatter plot may appear completely empty or plot incorrectly if the data structure is not arranged in paired columns or if the chart's series ranges are mismatched.
Ensure that the cells containing your dates are formatted as actual Excel date values rather than text strings, as scatter plots cannot plot text on numerical axes.
Adjust Chart Series Data Ranges and Parameters
Fix an empty scatter plot by manually assigning the correct paired date columns to the X and Y axes in the Select Data Source settings.
When inserting a scatter plot with complex data like dual date columns, Excel might misinterpret the data ranges. Manually editing the series values ensures the plot reads your dates correctly.
Ensure your source data is arranged in two adjacent columns, with the X-axis dates in the first column and the Y-axis dates in the second.
Click on your existing empty scatter plot to activate the Chart Tools on the top ribbon.
Navigate to the 'Chart Design' tab and click on the 'Select Data' button to open the configuration dialog box.
In the Legend Entries (Series) box on the left, select your data series and click the 'Edit' button.
Clear the 'Series X values' box and select your first date column. Then, clear the 'Series Y values' box and select your second date column. Click OK to apply the changes.
Create Scatter Plots with Dates Seamlessly in WPS Office
WPS Spreadsheet provides an intuitive interface for managing complex data structures and creating custom charts, making it easy to plot scatter charts with dates on both axes without hassle.
- 1. Open your data: Launch WPS Spreadsheet and open your workbook containing the paired date columns.
- 2. Insert Scatter Chart: Highlight your data, navigate to the Insert tab, and select the Scatter chart icon.
- 3. Select Data: Click the chart, go to the Chart Design tab, and use the Select Data tool to accurately assign your X and Y axis ranges.
- 4. Format Axes: Double-click the axes to open the settings pane and apply specific Date formats to display your values correctly.
Frequently Asked Questions
Why does my scatter plot show 5-digit numbers instead of dates on the axis?
Spreadsheet programs store dates as sequential serial numbers for calculation purposes. If your axis shows numbers like 44000 instead of a date, right-click the axis, click 'Format Axis', navigate to the Number section, and change the category format to 'Date'.
Why is my scatter plot completely blank after selecting date columns?
A blank scatter plot usually occurs if your dates are stored as text instead of valid date formats, or if the X and Y series ranges are pointing to empty cells. Highlight your dates, ensure their format is set to 'Short Date', and re-check the Select Data Source parameters.
Can I change the date interval or scaling on the scatter plot axes?
Yes. Right-click the axis and select Format Axis. Under Axis Options, you can adjust the Minimum/Maximum bounds (by inputting date serial numbers) and change the Major and Minor Units to specify the number of days, months, or years between each tick mark.
