How to Build an Excel Performance Management Form and Dashboard in WPS Spreadsheets
Question details
The user needs to plan and build an Excel-compatible performance management system in WPS Spreadsheets, using employee, department, manager, review period, criteria, and score tables, with historical averages and dashboard reporting.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is setting up a performance review workbook that records employees, departments and manager assignments; captures manager scores; calculates averages; keeps historical results; and reports them in an interactive dashboard.
- Observed behavior
- The goal is a reliable, formula-driven workbook where data entry is controlled by tables and validation, scores roll up into averages, and dashboard visuals update without needing macros first.
Before building the form, define the review scale and the columns for employee, department, manager, review period, criteria, and score. Create one Excel Table per record type so lookup formulas, PivotTables, and slicers remain stable as records grow.
Create a structured data model with Excel Tables
Use this first because normalized tables make formulas, PivotTables and dashboards easier to maintain than a single flat sheet or a macro-driven workbook.
Store each record type—employees, departments, managers, review periods, criteria, and scores—in its own table. Keep ID values unique in each table so relationships can be built with lookups and PivotTables.
Launch WPS Office, open WPS Spreadsheets, and create a new blank workbook. Save it as an .xlsx file so it stays compatible with Excel.
Enter the column headers for Employees, Departments, Managers, Review Periods, Criteria, and Scores on separate sheets or side-by-side ranges. Select each range, go to the Insert tab, click Table, and enable the header option.
Give each table a unique ID field such as EmployeeID or ManagerID. On the Data tab, click Data Validation to create dropdowns for department and manager selection so entries stay consistent.
Use the same ID values in the Scores table that you use in employee, manager, period, and criteria tables so formulas and PivotTables can link records correctly.
Calculate score averages and employee details with formulas
Use formula-based lookups and averaging after tables are created, so rows can be added without rewriting calculations.
Build the dashboard with PivotTables, PivotCharts, and slicers
Add dashboard visuals after the data model and calculations are in place, because PivotTables and slicers provide interactive historical comparisons without macros.
Build a performance management dashboard with WPS Spreadsheets
WPS Spreadsheets provides Excel Tables, lookup formulas, PivotTables, PivotCharts, and slicers, so you can build a complete performance management form and interactive dashboard without needing macros first.
- 1. Open WPS Spreadsheets: Download and install WPS Office, then open WPS Spreadsheets and create a new .xlsx workbook.
- 2. Create source tables: On separate sheets, use Insert > Table to create Employees, Managers, Review Periods, Criteria, and Scores tables with ID columns.
- 3. Add lookups and averages: Use =XLOOKUP or =VLOOKUP to return employee and manager names, and use =AVERAGE on the score range to calculate review results.
- 4. Build dashboard visuals: Select the Scores table, click Insert > PivotTable, add a PivotChart, and use Insert Slicer for Department, Manager, and Review Period.
- 5. Refresh and maintain: After new reviews are added, right-click the PivotTable and select Refresh so the dashboard updates from the source tables.
Frequently Asked Questions
Do I need macros or VBA to build a performance dashboard in WPS Spreadsheets?
No. For most performance dashboards, create Excel Tables, use data validation and lookup formulas, then add PivotTables, PivotCharts, and slicers. Add VBA only for automation that formulas cannot handle reliably.
Can I open and edit an existing Excel performance management workbook in WPS Office?
Yes. WPS Spreadsheets opens and saves .xlsx and .xls files, so you can keep using existing employee, score, and dashboard sheets while maintaining Excel compatibility.
How do I make manager selection a dropdown instead of typed text?
Select the manager column, go to the Data tab, click Data Validation, choose List, and set the source to the Manager table or manager name range.
Which formula should I use to look up employee details in a score table?
Use =XLOOKUP when your WPS version supports it, or =VLOOKUP for older compatibility. Point the lookup to the EmployeeID and return the Employee Name from the Employees table.
Why does my PivotTable not update after I add new score rows?
PivotTables do not always update automatically. Right-click the PivotTable and choose Refresh, or convert the source range to an Excel Table so new rows are included when you refresh.
