Chart & Visualization Issues

How to Create a Trend Chart by Date, Project, and Selected Metric in WPS Office Spreadsheet

Kushani NimanthikaKushani Nimanthika Nov 13, 2026 869 views

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.

How to Create a Trend Chart by Date, Project, and Selected Metric in WPS Office Spreadsheet
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.
Before you start

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.

Solution 1Recommended

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.

1
Add a metric selector dropdown

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.

2
Create a Selected Metric helper column

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.

3
Insert a PivotChart

Select the table including Project, Date, and Selected Metric columns, click Insert > PivotChart, choose New Worksheet, and click OK.

4
Build the trend view

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.

Create a dynamic trend chart with a metric selector cell
Interactive trend view: The chart now updates to show Data Representation, Slide Aesthetics, Risk and Issues Articulation, Timeliness, or Consistency based on the selected metric.

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. 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. 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. 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. 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.
Switch metrics with one dropdown instead of rebuilding chartsCompare multiple projects in one line chart with Date as the shared axisOpen, edit, and save Microsoft Excel .xlsx workbooks with full compatibilityFree and lightweight alternative to Microsoft Excel for project tracking dashboards
QA img-9

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.