Chart & Visualization Issues

How to Show Actual and Forecast Data with Different Chart Line Styles in WPS Office Excel

Khadija KhanKhadija Khan Nov 13, 2026 869 views

Question details

The user wants to create a WPS Office Excel chart that displays historical actual values as a solid line and projected forecast values as a dashed line, without the two lines connecting across the transition.

How to Show Actual and Forecast Data with Different Chart Line Styles in WPS Office Excel
Product
WPS Office Spreadsheet
Device & OS
not provided
Scenario
Creating an Excel trend chart with historical and projected data series.
Observed behavior
The desired chart should contain two separate formatted series: a solid line for actual values and a dashed line for forecast values.
Before you start

Prepare two data ranges: one for actual historical values and one for projected forecast values. Use NA() in the cells where a series should not be plotted so the chart lines do not connect across the transition.

Solution 1Recommended

Create Separate Actual and Forecast Series with NA() and Format Each Line

Use this method when you want a clean break between historical actuals and future projections in a WPS Office Excel line chart.

This approach uses two chart series and the NA() function. WPS Office Spreadsheet treats NA() as an empty plot point, which prevents the solid actual line from connecting to the dashed forecast line.

1
Enter actual and forecast data

In WPS Office Spreadsheet, enter your actual values in one column and forecast values in another column. In cells where a series should not appear, enter =NA().

2
Insert a line chart

Select both data columns including headers, go to the Insert tab, click Chart, and choose a Line chart type.

3
Confirm the two series

Right-click the chart and choose Select Data. Ensure one series is the actual data and the other is the forecast data; use Add if the second series is missing.

4
Format the actual series as solid

Right-click the actual data line, click Format Data Series, go to Line, and set the line style to Solid.

5
Format the forecast series as dashed

Right-click the forecast data line, click Format Data Series, go to Line, and choose a dash type such as Dash or Square Dot.

Create Separate Actual and Forecast Series with NA() and Format Each Line
Chart is ready: The actual series now uses a solid line and the forecast series uses a dashed line, with no connecting line across the transition.
WPS Office Excel

Create a Solid Actual and Dashed Forecast Chart in WPS Office Excel

Use WPS Office Spreadsheet to build a two-series trend chart with a solid historical line and a dashed projected line. The chart formatting works inside WPS Office and remains compatible with Microsoft Excel files.

  1. 1. Open WPS Office Spreadsheet: Launch WPS Office and open your existing Microsoft Excel workbook or start a new spreadsheet.
  2. 2. Prepare the actual and forecast data: Enter your actual values and forecast values in separate columns, using =NA() for cells that should not be plotted in each series.
  3. 3. Create and format the chart: Select the data, click Insert > Chart > Line, then right-click each series and use Format Data Series to set the actual line to solid and the forecast line to dashed.
  4. 4. Save as an Excel-compatible file: Click Menu > Save As and choose Excel Workbook (.xlsx) to keep the chart formatting compatible with Microsoft Excel.
Format actual and forecast series separately with solid and dashed linesFull compatibility with Microsoft Excel .xlsx workbooks and chartsFree spreadsheet tools with familiar Insert, Chart, and Format Data Series menusSeamless migration between WPS Office and Microsoft Excel
microsoft office alternative - wps office

Frequently Asked Questions

How do I stop the actual and forecast lines from connecting in WPS Office Excel?

Use =NA() in the cells where the series should not appear. This creates a break in the line instead of connecting the last actual point to the first forecast point.

How do I change a chart line from solid to dashed in WPS Office Excel?

Right-click the forecast line in the chart, click Format Data Series, open the Line settings, and select a dash type under Dash type.

Can I open and edit this chart in Microsoft Excel after creating it in WPS Office?

Yes. Save the workbook as an Excel .xlsx file in WPS Office, and Microsoft Excel can open the chart with the solid and dashed series formatting.

Why use NA() instead of leaving cells blank?

WPS Office Spreadsheet may interpret blank cells differently depending on chart settings. =NA() forces a no-value plot point and reliably breaks the line between actual and forecast data.

How do I add a forecast series to an existing chart?

Right-click the chart, choose Select Data, click Add, select the forecast data range, and then format that added series with a dashed line.