Fix an Excel XY Scatter Chart with Smooth Lines and Dates
Question details
The user needs an Excel XY scatter chart with smooth lines to correctly plot dates horizontally and numeric values vertically.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating or correcting a smooth-line XY scatter chart using dates as X values and measurements as Y values.
- Observed behavior
- The chart does not show the intended result. It appears to use dates on the horizontal axis and numbers on the vertical axis, but the source ranges and axis configuration need verification.
Identify the date column and its corresponding numeric-value column, excluding headers. Check that each date has a matching value and that Excel stores the dates as numeric date values rather than text.
Assign the correct X and Y ranges to the scatter chart
Use this solution when the chart plots the wrong data or Excel has selected the wrong series ranges.
Select the chart, then open Chart Design > Change Chart Type > XY (Scatter). Choose Scatter with Smooth Lines or Scatter with Smooth Lines and Markers.
Right-click the chart and choose Select Data. Under Legend Entries (Series), select the series and click Edit.
Set Series X values to the date cells and Series Y values to the matching numeric cells. For example, use =$A$2:$A$20 for dates and =$B$2:$B$20 for values. Exclude headers and make sure both ranges contain the same number of cells.
Click OK to apply the changes. In Select Data, check any additional series and remove unintended ones, then click OK again.
Check date values, point order, and axis settings
Use this solution if the ranges are correct but dates look wrong, points are misplaced, or the smooth line doubles back.
Plot Dates and Values in a WPS Spreadsheet Scatter Chart
Use WPS Spreadsheet to create an XY scatter chart from paired date and numeric columns. Assign the series ranges explicitly, select a smooth-line subtype, and format the horizontal-axis labels as dates.
- 1. Open the data in WPS Spreadsheet: Open your workbook in WPS Spreadsheet. Arrange dates in one column and their corresponding numeric values in the next column, with one paired observation per row.
- 2. Insert an XY scatter chart: Select the date and value cells, then open Insert > Chart. Choose an XY Scatter chart and select a smooth-line subtype from the available chart options.
- 3. Verify the series ranges: Right-click the chart and open Select Data. Edit the series so its X values reference the date cells and its Y values reference the matching numeric cells, excluding headers.
- 4. Format the date axis: Right-click the horizontal axis and open Format Axis. Set the number format to a date format, then check the axis bounds and confirm that the plotted dates cover your intended range.
Frequently Asked Questions
Why does my scatter chart show numbers instead of dates on the horizontal axis?
Excel stores dates as serial numbers, and a scatter chart uses a numeric X axis. Right-click the horizontal axis, choose Format Axis > Number, and select a date format. Also verify through Select Data > Edit that the X range references actual date cells.
Should I use a line chart or an XY scatter chart for dates?
Use an XY scatter chart when the horizontal spacing should reflect the actual time gaps between observations. A line chart can also use a date axis, but an XY scatter chart gives explicit control over each series' numeric X and Y ranges.
Why does the smooth line loop backward between dates?
Scatter-chart lines connect points in source order. If chronological order is intended, select the full paired table and use Data > Sort to sort dates from oldest to newest. If the line still curves unexpectedly, switch to a straight-line scatter subtype.
What should I share if the chart still looks wrong?
Provide a small sample workbook containing the date and value columns and the chart, along with an image or description of the desired result. Include the series ranges shown in Select Data > Edit and any fixed axis bounds, and remove sensitive data before sharing.
