How to add dotted forecast line in an Excel line chart?
Let’s say you have created a line chart to display the sales amount in the first half year as below screenshot shown. But now, you need to forecast the sales amount in the second half year, and add the forecasted values as a dotted line in the chart, how to deal with it? This article will introduce an easy solution to add a dotted forecast line in an existing line chart in Excel.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
To add a dotted forecast line in an existing line chart in Excel, please do as follows:
1. Beside the source data, add a Forecast column, and list the forecasted sales amount as below screenshot shown.
Note: Please remember to add the sales amount of Jun in the Forecast column too.
2. Right-click the line chart, and click Select Data in the context menu. See screenshot:
3. In the Select Data Source dialog box, please click the Add button in the Legend Entries (Series) section. See screenshot:
4. Now the Edit Series dialog box comes out. Please (1) type Forecast in the Series name box, (2) specify the Forecast column excluding the column header as Series values, and then (3) click the OK buttons successively to close the two dialog boxes. See screenshot:
5. Now the forecast line is added to the line chart. Right-click the forecast line, and click Format Data Series from the context menu. See screenshot:
6. In the Format Data Series pane, please (1) click the Fill & Line icon, (2) expand the Line section; and (3) Select Round Dot from the Dash type drop-down list. See screenshot:
Note: If you are using Excel 2010 or earlier versions, it will open the Format Data Series dialog box. In the dialog box, please click Line Style in the left bar, select Round Dot from the Dash type drop-down list, and then close the dialog box.
So far, we have added the dotted forecast line in the existing line chart. See screenshot: