WPS Spreadsheet: Create a Booking Status Chart by Work Programme
Question details
The user needs a dashboard chart showing the percentage of booked items and items awaiting information for each work programme.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A source table contains work programmes and booking dates, and the booking date should determine whether each item is booked or awaiting information.
- Observed behavior
- A booking-status dashboard and comparative chart are needed, but the source data does not yet include a status field or percentage breakdown by work programme.
Check that the source data has a Work Programme column, a Booking Date column, and one row per item. Confirm that blank booking dates represent items awaiting information before creating the helper status field.
Create a 100% stacked booking-status chart with a PivotTable
Use this method to classify each item from its booking date, calculate row percentages, and compare status proportions across work programmes.
A helper Status column converts each booking date into a consistent category. The PivotTable then counts each category by work programme and displays the counts as percentages of each row total, making a 100% stacked chart suitable for comparing proportions.
In the source table, add a new column named Status. In the first Status cell, enter =IF([@[Booking Date]]="","Awaiting information","Booked") and fill the formula down. If the data is not formatted as a table, replace the structured reference with the booking-date cell, such as =IF(C2="","Awaiting information","Booked").
Select any cell in the source table, open the Insert tab, choose PivotTable, select the source range or table, and place the PivotTable on a new worksheet.
In the PivotTable Fields pane, drag Work Programme to Rows, Status to Columns, and an item identifier or Status field to Values. Set the Values calculation to Count if WPS does not select Count automatically.
Open the value field settings from the Values area, choose Show Values As, and select Percentage of Row Total. Each work programme row will then show the proportions for Booked and Awaiting information.
Select the PivotTable results, open Insert, choose Column or Bar Chart, and select 100% Stacked Column or 100% Stacked Bar. Add a title such as Booking Status by Work Programme and keep the Booked and Awaiting information legend visible.
Build the percentage chart from a summarized worksheet table
Use this alternative when PivotTable controls are unavailable or when you need a manually arranged dashboard data range.
Create a Booking Status Dashboard in WPS Spreadsheet
WPS Spreadsheet can classify records from booking dates, summarize booked and awaiting-information items by work programme, and turn the percentages into a clear PivotTable-based 100% stacked chart. Its familiar spreadsheet tools make it easy to maintain the dashboard as source data changes.
- 1. Open the source workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the Work Programme and Booking Date columns.
- 2. Add the status formula: Insert a Status column and enter =IF([@[Booking Date]]="","Awaiting information","Booked") in the first data row, then fill it down.
- 3. Summarize the records: Choose Insert > PivotTable, place Work Programme in Rows, Status in Columns, and an item field in Values, then set the value display to Percentage of Row Total.
- 4. Create the dashboard chart: Select the PivotTable, choose Insert > Chart, and select a 100% Stacked Column or 100% Stacked Bar chart.
Frequently Asked Questions
How does the formula decide whether an item is booked?
The formula checks the Booking Date cell. If it is blank, the Status result is Awaiting information; if it contains a date or another value, the result is Booked.
Which PivotTable fields should I use for this dashboard?
Place Work Programme in Rows, Status in Columns, and an item identifier or Status field in Values. Set the Values field to Count and display it as Percentage of Row Total.
Why should I use a 100% stacked chart?
A 100% stacked chart makes every work programme equal to 100%, so the segment sizes directly compare the proportion of Booked and Awaiting information items.
What happens when I add new records to the source table?
Refresh the PivotTable after adding records. If the source is an Excel-style table, the expanded table range is generally included when the PivotTable is refreshed; otherwise, update the PivotTable source range first.
