How to Add a Horizontal Target Line to an Excel Column Chart
Question details
The user needs to display a horizontal target or reference value across an Excel column chart.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A clustered column chart needs a constant target line for comparing each category against the same benchmark.
- Observed behavior
- The chart's Lines option may be unavailable, so a target value must be added as a separate series and converted to a line.
Add a target-value column beside the chart's source data and enter the same target value for every category. Make sure the target column has a clear header and covers all categories shown in the chart.
Add a target series and convert it to a line in Excel
Use this method when the clustered column chart does not offer a direct horizontal line option.
A constant target line is created by plotting the target value as a separate series for every category, then changing only that series from columns to a line.
In the worksheet, add a new column next to the chart source data. Enter a header such as Target and type the same target value in every category row.
Select the chart, open Chart Design on the ribbon, choose Select Data, click Add, and select the Target header as the series name and the target values as the series values.
Click the new target series in the chart, open Chart Design, select Change Chart Type, and set the Target series type to Line. Confirm the change to display a horizontal line across the columns.
Right-click the target line and choose Format Data Series. Adjust its color, width, dash style, and markers so it is easy to distinguish from the column data.
Create a horizontal target line in WPS Spreadsheet
WPS Spreadsheet can create the same comparison chart by adding a repeated target-value series, inserting it into the chart, and changing that series to a line. Its familiar chart tools make the workflow easy to migrate from Excel.
- 1. Prepare the target values: In WPS Spreadsheet, add a Target column beside the chart data and enter the same target number for each category.
- 2. Add the target series: Select the chart, open the Chart Tools or Design tab, choose Select Data, and add the Target column as a new series.
- 3. Switch the series to a line: Select the Target series, choose Change Chart Type from the chart tools, and set that series to Line while leaving the other series as columns.
- 4. Format and save the chart: Use the Format Data Series options to style the line, then save the workbook in .xlsx format if it will be opened in Microsoft Excel.
Frequently Asked Questions
Why is the Lines option unavailable for my clustered column chart?
Some clustered column chart configurations do not provide a direct Lines option. Add the target as a separate series, then use Chart Design > Change Chart Type to convert that series to a line.
Do I need to enter the target value for every category?
Yes. Enter the same target value in each category row so the chart plots a continuous horizontal line across the full chart.
Can I keep the actual data as columns and the target as a line?
Yes. When changing the chart type, assign the actual data series to Column and the target series to Line. This creates a combination chart.
How can I make the target line easier to see?
Right-click the line, select Format Data Series, and adjust its color, width, dash style, or markers. A contrasting color and thicker line usually work best.
