Chart & Visualization Issues

How to Add a Target Line to a Data Model PivotTable Bar Chart in WPS Office Spreadsheets

Aamir Naveed Akram Nov 13, 2026 869 views

Question details

The user needs to add a target reference line to a bar chart built from a Data Model PivotTable in WPS Office Spreadsheets, where direct target-line options may not work the same way as with a regular PivotChart.

How to Add a Target Line to a Data Model PivotTable Bar Chart in WPS Office Spreadsheets

Product: WPS Office Spreadsheets
Device & OS: not provided
Scenario: Creating or editing a bar chart based on a Data Model PivotTable and wanting a target line to compare category values against a goal.
Observed behavior: Direct target-line options may not be supported in the same way as for a regular PivotChart; a separate target data series is needed to create the visual reference line.

Before you start

Before you begin, check the PivotChart to see whether right-clicking a series provides Change Series Chart Type. If it is not available, prepare a linked summary range from the PivotTable results and target values.

Solution 1Recommended

Add a Repeating Target Series and Change It to a Line in a WPS PivotChart

Use this method when the Data Model PivotChart allows you to change an individual series chart type.

A Data Model PivotChart may not expose the same combination-chart options as a regular PivotChart. Adding a target field or measure as a separate series lets you keep the PivotTable connected and change only that series to a line.

Add a target column or measure to the Data Model

Open the Data Model source table and create a target column that repeats the target for each row, or add a calculated measure that returns the target value.

Add the target field to the PivotTable Values area

Select the PivotTable, open the PivotTable Field List, and drag the target field into the Values area so it appears as a new series.

Change the target series to a line in the chart

Select the PivotChart, right-click the target series, click Change Series Chart Type, and choose Line for the target series while keeping the original series as Bar.

Format the line as a target reference

Right-click the line, choose Format Data Series, and adjust the color, width, dash type, or marker to make the target line clear.

Add a Repeating Target Series and Change It to a Line in a WPS PivotChart

Target line updates with the PivotTable: Because the target is part of the PivotTable, the line updates when you refresh the PivotTable data.

Solution 2

Build a Standard Combination Chart from a Linked PivotTable Summary Range

Use this method when the Data Model PivotChart does not allow the required combination chart or restricts series chart type changes.

WPS Office Spreadsheets Guide

Add a Target Line to a Data Model PivotTable Bar Chart in WPS Office Spreadsheets

Use a helper target series and the combo chart option in WPS Office Spreadsheets to turn one series into a target line. If the Data Model PivotChart restricts the combo chart, link the PivotTable results to a summary range and create the target-line chart there.

1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office Spreadsheets and open the workbook that contains the Data Model PivotTable and bar chart.
2. Add a target series to the PivotTable: Create or include a target column or measure in the PivotTable so each category has a target value.
3. Create a combo chart: Select the PivotChart, click Change Chart Type, and set the target series to Line while keeping the bar series unchanged.
4. Format the line: Right-click the target line, choose Format Data Series, and set the color and style for the goal line.
5. Save and share: Save the workbook as .xlsx to keep it compatible with Microsoft Excel and WPS Office.

Add target lines to PivotCharts without rebuilding the entire workbookFull compatibility with .xlsx, .xls, and common Microsoft Office chart workbooksFree, lightweight WPS Office interface with familiar chart and PivotTable toolsKeep Data Model results connected while creating clear visual benchmarks

microsoft office alternative - wps office

Frequently Asked Questions

Why can’t I add a target line directly to a Data Model PivotChart in WPS Spreadsheets?

Data Model PivotCharts may restrict individual series chart type changes. Add a target column or measure to the PivotTable and use Change Chart Type to set that series to a line, or build a standard combo chart from a linked summary range.

Can I use a calculated measure as the target line?

Yes. Create a measure that returns the target value for each category, add it to the PivotTable Values area, then change its series chart type to Line and format it separately.

What should I do if Change Series Chart Type is disabled for my PivotChart?

Create a normal chart from a linked summary range that references the PivotTable rows and target values, then use the Combo chart option to show bars and a line.

Will the target line update when my PivotTable data changes?

If the target series is in the PivotTable and the PivotTable is refreshed, the line updates. If you use a linked summary range, refresh the PivotTable and verify the linked formulas return current values.

Does this method work when I open the file in Microsoft Excel?

Yes. WPS Office saves the workbook in .xlsx format, and the combination chart with a target line remains compatible with Microsoft Excel.

Excel & Data Problems