How to Create a Trend Chart by Date, Project, and Selected Metric in WPS Office Spreadsheet
Question details
The user needs to visualize project performance over time by selecting one metric at a time and comparing values across projects and dates.

- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user tracks project performance metrics such as Data Representation, Slide Aesthetics, Risk and Issues Articulation, Timeliness, and Consistency, and wants a chart that shows a selected metric by date and project.
- Observed behavior
- The goal is to display a trend line chart with Date on the horizontal axis, one series per project, and a switchable or filterable selected metric.
Organize the source data in a table with separate columns for Project, Date, and each metric. Make sure dates use a consistent date format and each project-date combination is unique to avoid duplicate or misleading series points.
Create a dynamic trend chart with a metric selector cell
Use this method when your metrics are stored in separate columns and you want one dropdown to switch the chart between metrics without rebuilding the chart.
Adding a helper column that reads the selected metric keeps the chart linked to a single measure. The PivotChart then uses this helper column to create one series per project over the Date axis.
In a blank cell such as J1, type "Metric". Select J2, click Data > Validation > List, and set the source to your metric header cells, for example =$C$1:$G$1. This creates the dropdown for switching metrics.
Enter "Selected Metric" in H1. In H2, enter the formula =INDEX(C2:G2,MATCH($J$2,$C$1:$G$1,0)) and fill it down to return the selected metric value for each row.
Select the table including Project, Date, and Selected Metric columns, click Insert > PivotChart, choose New Worksheet, and click OK.
In the PivotChart Field List, drag Date to Axis (Categories), Project to Legend (Series), and Selected Metric to Values. Use the J2 dropdown to switch the plotted metric.

Use a PivotChart on unpivoted data with Metric and Value columns
Use this alternative when you can restructure the data so that all metrics are in one Metric column and their values are in one Value column.
Create a switchable project trend chart in WPS Spreadsheet
WPS Office Spreadsheet can directly build the trend chart you need: add a metric selector, create a helper column, and insert a PivotChart to compare project performance by date and metric.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office, open the workbook containing Project, Date, and metric columns, and format the data as a table.
- 2. Create the metric selector and helper column: Use Data Validation to make a metric dropdown and add a helper column with INDEX/MATCH to return the selected metric value.
- 3. Insert and configure the trend chart: Select the table, click Insert > PivotChart, and drag Date to Axis, Project to Legend, and Selected Metric to Values.
- 4. Switch and format the chart: Use the metric dropdown and PivotChart filters to change the displayed metric, then adjust the chart style from the Chart Tools tab.

Frequently Asked Questions
How do I switch between metrics without creating a separate chart for each one?
Add a metric selector dropdown using Data Validation, create a helper column that returns the selected metric value with INDEX/MATCH, and then insert a PivotChart that uses that helper column as the Values field.
Can I use the same trend chart to show only one project or a specific date range?
Yes. In the PivotChart, use the report filters or add Slicers for Project and Date, then select the project or date range you want to view.
Why does my line chart connect all projects into one line?
The chart needs Project in the Legend (Series) area. In a PivotChart, drag Project to Legend and Date to Axis so each project becomes a separate series.
Will this work if my data is already in separate metric columns?
Yes. Use a helper column with INDEX/MATCH to pull the selected metric into a single Selected Metric column. You do not need to manually unpivot the data for the recommended method.
Can I save this trend chart as a Microsoft Excel file?
Yes. WPS Office Spreadsheet supports .xlsx format, so the workbook, chart, and PivotChart connection remain compatible when opened in Microsoft Excel.




