Create a Dynamic Daily Positive-Case Excel Chart in WPS Office
Question details
The user needs to build a chart in WPS Spreadsheets that automatically shows daily positive-case counts for residents and staff and updates as new records are added.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user maintains daily case records for residents and staff and wants to visualize positive counts by date in a stacked chart that updates as new data is entered.
- Observed behavior
- The desired chart should automatically include new dates and adjust series values for residents and staff without manually editing the chart data range.
Before creating the chart, make sure the daily case records include a Date column and a Category column such as Resident or Staff, with each row representing one case or one daily count. Confirm the data is in a continuous range so WPS Spreadsheets can convert it to an Excel Table.
Create an Excel Table and Stacked Column Chart from Daily Case Records
Use this method when you want a simple dynamic chart that automatically expands as you add new rows to a structured table.
A WPS Spreadsheets table range is dynamic, so any chart based on it updates when new records are added. Stacked columns separate positive residents and staff by date.
Select any cell in the daily case records, go to the Insert tab, click Table, confirm the range, and enable My table has headers.
With the table selected, go to the Insert tab, click the Column Chart drop-down, and choose Stacked Column.
Use Select Data or Chart Tools to set the horizontal axis to the Episode Date column and the series values to the positive count columns for residents and staff.
Type a new row immediately below the table; WPS Spreadsheets extends the table range and the chart updates automatically.
Use a PivotChart from a PivotTable for Automatic Daily Summaries
Use this method when your raw data contains individual case records and you need WPS Spreadsheets to count and group positives by date and category automatically.
Create an Auto-Updating Daily Positive-Case Chart with WPS Spreadsheets
Turn daily positive resident and staff records into a dynamic stacked column chart or PivotChart in WPS Spreadsheets. The chart expands with new rows and refreshes with your data, while remaining compatible with Excel .xlsx workbooks.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the daily positive-case workbook, and select the data range that contains the date and category columns.
- 2. Convert the range to a table: Go to the Insert tab, click Table, and check My table has headers so new rows are included automatically.
- 3. Insert the stacked chart: Go to the Insert tab, click Column Chart, and choose Stacked Column or Stacked Bar to compare residents and staff by date.
- 4. Refresh or add data: Type new records directly below the table and use Refresh All on the Data tab if you are using a PivotChart summary.
Frequently Asked Questions
Why does my chart not update when I add new daily rows?
In WPS Spreadsheets, a normal chart uses a fixed range. Select your data, click Insert > Table to convert it to a structured table, then base the chart on that table. New rows typed below the table are included automatically.
Can the chart show residents and staff as separate colors in the same bar?
Yes. Use a stacked column chart and put resident and staff counts in separate series, or add a Category field to a PivotChart so each group is plotted as a stacked series.
How do I make new dates appear on the horizontal axis automatically?
Base the chart on an Excel Table or PivotChart. In a PivotChart, right-click the chart or PivotTable and choose Refresh, or use Refresh All on the Data tab after adding new records.
Does this work with a workbook created in Microsoft Excel?
Yes. WPS Spreadsheets opens and saves .xlsx files, so you can open existing Excel case tracking workbooks, create the dynamic chart, and share it without converting the file format.
Do I need one row per resident and staff case?
For automatic counting, keep a continuous table with one row per case and a category column. If you already have daily counts, use two count columns for residents and staff instead.
