WPS Spreadsheet: Create a Dynamic Four-Week Excel Dashboard with Charts
Question details
The user needs to create a dashboard that filters test results by supervisor and selected date, calculates averages for the previous four weeks, and displays the results in four bar charts.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Source data includes dates, people, supervisors, and four test score columns. The dashboard should use drop-down selections and update its calculations and charts automatically.
- Observed behavior
- The requested dashboard and dynamic chart setup has not yet been created; the goal is to show four-week average scores that respond to supervisor and date selections.
Confirm that the Date column contains valid spreadsheet dates and that the source range has consistent headers such as Date, Person, Supervisor, Test1, Test2, Test3, and Test4. Convert the source range to a table so formulas and chart ranges expand when new records are added.
Build a dynamic four-week dashboard with drop-down filters and bar charts
Use this approach when the dashboard must recalculate four-week test averages whenever the supervisor or selected date changes.
Store the records in a table named Table1. Create a dashboard sheet with a supervisor selector in B1 and a selected date in B2. The formulas can use AVERAGEIFS to limit results to the 28-day period ending on the selected date.
If the dashboard needs one bar per person, list the people in the chart source range and add a person criterion to each AVERAGEIFS formula. If the charts should show one overall value per test, use the simpler formulas below and place the four results in a chart source range.
In WPS Spreadsheet, select the source data, choose Insert > Table, confirm that the table has headers, and name it Table1. Use columns such as Date, Person, Supervisor, Test1, Test2, Test3, and Test4.
On a Dashboard sheet, place the supervisor selector in B1 and the date selector in B2. Select B1, open Data > Data Validation, choose a list of supervisors, and include an All option. Apply Data Validation to B2 with a list of valid dates or enter a valid date directly.
Create four labeled cells for Test1 through Test4 and enter formulas such as =IF($B$1="All",AVERAGEIFS(Table1[Test1],Table1[Date],">="&$B$2-28,Table1[Date],"<="&$B$2),AVERAGEIFS(Table1[Test1],Table1[Supervisor],$B$1,Table1[Date],">="&$B$2-28,Table1[Date],"<="&$B$2)). Replace Test1 with Test2, Test3, and Test4 in the other cells.
Place the four calculated averages beside their test labels. For person-level charts, list each person in a separate row and add the Person criterion to the AVERAGEIFS formulas, then use each test column and its calculated values as a chart source range.
Select each test label and its calculated values, choose Insert > Chart, select a Bar chart, and add a clear title. Change B1 and B2 to different supervisors and dates to verify that all four charts refresh automatically.
Create an overall four-test summary dashboard
Use this simpler layout when each chart should display one average per test rather than separate bars for individual people.
Create an Interactive Four-Week Test Score Dashboard in WPS Spreadsheet
WPS Spreadsheet provides the table, Data Validation, AVERAGEIFS formulas, and bar chart tools needed to build a dashboard that responds to supervisor and date selections. Use familiar spreadsheet controls to calculate rolling four-week results and present them in clear visual charts.
- 1. Open WPS Spreadsheet: Launch WPS Office and select Spreadsheet to create or open the workbook.
- 2. Organize the data: Use Insert > Table to turn the source records into a structured table with consistent headers.
- 3. Add dashboard controls and formulas: Use Data > Data Validation for the selectors and enter AVERAGEIFS formulas for the selected four-week period.
- 4. Insert the charts: Select the calculated chart source ranges and choose Insert > Chart > Bar, then format the dashboard.
Frequently Asked Questions
How do I make the WPS Spreadsheet dashboard filter by supervisor?
Place the supervisor selection in a dashboard cell such as B1, create its list with Data > Data Validation, and reference B1 in the Supervisor criterion of each AVERAGEIFS formula. Use an IF condition to skip that criterion when B1 contains All.
How should I calculate the previous four weeks from a selected date?
If the selected date is in B2, use a start-date condition of ">="&$B$2-28 and an end-date condition of "<="&$B$2 in AVERAGEIFS. This includes the selected date and the previous 28 days.
Can each bar chart show scores for individual people?
Yes. List each person in the chart source range and add Table1[Person] as an additional AVERAGEIFS criterion. Create one calculated value per person for each test, then use those values as the chart series.
Why do my four charts not update after changing a drop-down?
Check that the formulas reference the selector cells, the selected date is a valid spreadsheet date, and the charts use the calculated cells rather than copied values. Also confirm that the source records fall within the selected 28-day range.
