How to Create a Spreadsheet Pivot Table Check-Off Chart from Completion Dates
Question details
The user needs to create a Pivot Table that displays a visual check-off chart, indicating whether specific training modules have been completed by students based on the existence of a completion date.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Tracking student training module completion status using logged completion dates.
- Observed behavior
- A structured matrix chart showing students as rows, modules as columns, and visual check marks (instead of dates or numbers) indicating completed tasks.
Ensure your source data is organized in a clear tabular format without blank rows, featuring distinct columns for 'Student', 'Module', and 'Completion Date'.
Build the Pivot Table and Apply a Custom Number Format
This method counts the completion dates and uses a custom format to replace the numerical count with a visual check mark symbol.
Placing a date or text field into the Values area of a Pivot Table will default to counting the number of entries. By leveraging this count, you can apply a custom number format to visually represent a '1' (completed) as a check mark, and handle blank cells for incomplete modules.
Select your entire data range. Navigate to the 'Insert' tab on the ribbon and click 'PivotTable'. Choose to place it on a new or existing worksheet.
In the PivotTable Fields pane, drag the 'Student' field to the Rows area, the 'Module' field to the Columns area, and the 'Completion Date' field to the Values area. Ensure the calculation is set to 'Count of Completion Date'.
Highlight the calculated value cells inside your new Pivot Table. Right-click the selected area and choose 'Format Cells' from the context menu.
In the Format Cells dialog, go to the 'Number' tab and select the 'Custom' category. In the 'Type' box, enter the custom format code: "✓";;"" (you can copy and paste the check mark symbol). Click OK to apply.
Create Pivot Table Check-Off Charts Easily in WPS Office
WPS Spreadsheet provides a highly intuitive interface for building and formatting Pivot Tables, allowing you to track project and student progress effortlessly with powerful custom cell formatting.
- 1. Open Data in WPS Spreadsheet: Launch WPS Spreadsheet and open your tracker document containing the students, modules, and dates.
- 2. Insert a PivotTable: Go to the 'Insert' tab on the top ribbon and select 'PivotTable' to generate a blank table from your data range.
- 3. Arrange the Tracking Fields: Drag 'Student' to Rows, 'Module' to Columns, and 'Completion Date' to Values to generate a numerical count of completed modules.
- 4. Format as Check Marks: Highlight the count values, press Ctrl+1 to open the Format Cells window, select 'Custom', and input "✓";;"" to replace the numbers with visual check marks.
Frequently Asked Questions
Why is my Pivot Table summing the dates instead of counting them?
If your date column contains numbers that the spreadsheet interprets strictly as numeric values, the Pivot Table may default to 'Sum'. Right-click any value cell in the Pivot Table, select 'Value Field Settings', and change the summarize function from 'Sum' to 'Count'.
How do I show an 'X' or unchecked box for incomplete modules in the Pivot Table?
Since Pivot Tables do not naturally generate records for blank data intersections, you can force them to display a character for empty cells. Right-click the Pivot Table, select 'PivotTable Options', check the box for 'For empty cells show:', and enter an 'X' or '☐' symbol.
Can I use the Wingdings font to create the check marks?
Yes. Instead of using a custom number format with a unicode check mark, you can format the cells with the 'Wingdings' font. In Wingdings, an uppercase 'P' or the character 'ü' often represents a check mark. However, using the unicode custom format ("✓";;"") with standard fonts is generally more reliable across different devices.
