How to Create a Multi-Line Trend Chart from an Excel Pivot Table
Question details
Create a multi-line trend chart from an Excel 2013 PivotTable that shows volunteer response counts per month, with each response type as a separate line, dates on the horizontal axis, and counts on the vertical axis.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has volunteer response data with application dates and response types and wants to compare how each response type trends over four months.
- Observed behavior
- The desired chart should display monthly grouped dates on the horizontal axis, response type series in the legend, and response counts on the vertical axis.
Make sure the source data has one row per volunteer application, with columns for application date and response type. Open the workbook in WPS Spreadsheets and click a cell inside the data before creating the PivotTable.
Create a PivotTable and PivotChart line chart grouped by month in WPS Spreadsheets
Use this method when your source data has a date column, a response type column, and one row per response; it creates one line for each response type with monthly counts.
WPS Spreadsheets uses the same PivotTable and PivotChart workflow as Excel 2013, so you can follow this method directly on an existing .xlsx workbook.
Click any cell in the source data, go to the Insert tab, choose PivotTable, confirm the data range, select New Worksheet, and click OK.
In the PivotTable Fields pane, drag Application Date to the Rows area, Response Type to the Columns area, and the application identifier field or Application Date to the Values area, then set the value field to Count.
Right-click any date label in the PivotTable row area, choose Group, select Months, and click OK. This groups dates into months instead of showing individual dates.
Click inside the PivotTable, go to the Insert tab, choose PivotChart, select Line chart or Line with Markers, and click OK.
In the PivotChart, confirm that the grouped date field is in the Axis Fields area, Response Type is in the Legend Fields area, and Count of Application is in the Values area. Drag fields between areas if needed.
Build a separate monthly helper table and insert a standard line chart
Use this alternative if you prefer a static chart without PivotChart field buttons or need a fixed summary table that will not change when the source data is refreshed.
Create a Multi-Line Trend Chart from an Excel Pivot Table in WPS Office
Use WPS Spreadsheets to build a PivotTable from volunteer application data, group the date field by month, and insert a line PivotChart with response types as separate series.
- 1. Open the Excel workbook in WPS Office: Launch WPS Spreadsheets, click Menu > Open, choose the Excel 2013 workbook, and select the volunteer data sheet.
- 2. Insert a PivotTable from the source data: Click any cell in the data, go to the Insert tab, click PivotTable, confirm the range, and choose New Worksheet.
- 3. Build the monthly trend layout: Drag Application Date to Rows, Response Type to Columns, and an application identifier or date field to Values; right-click a row date, click Group, select Months, and click OK.
- 4. Insert and arrange the line PivotChart: Click inside the PivotTable, go to the Insert tab, choose PivotChart, select Line chart, and verify the grouped date is on Axis, Response Type is in Legend, and Count is in Values.
- 5. Save or export the result: Click Menu > Save As and choose Excel Workbook or PDF to share the trend chart while keeping the file compatible with Excel.
Frequently Asked Questions
Why does the line chart show individual dates instead of one line per month?
The date field is not grouped. Right-click a date label in the PivotTable, choose Group, select Months, and click OK before inserting or after updating the PivotChart.
How do I put response types as separate lines in WPS Spreadsheets?
Drag the Response Type field to the Columns area of the PivotTable or to the Legend Fields area of the PivotChart. WPS then creates one series and one line for each response type.
Can I open an Excel 2013 PivotChart in WPS Office without losing the layout?
Yes, WPS Spreadsheets supports .xlsx PivotTables and PivotCharts. Open the workbook and use the PivotChart tools to adjust the axis, legend, and values fields if needed.
How do I count applications instead of summing dates?
Click the field in the Values area, choose Value Field Settings, select Count, and click OK. Use a unique application field such as Application ID for an accurate count.
Why does the horizontal axis show every date instead of four months?
The row or axis field must be grouped by Months. Right-click a row label and group by Months; if the source dates include text or inconsistent values, correct them first.
