How to Identify Absent Participants in Microsoft Forms Attendance Tracker
Question details
Users need a way to automatically compare Microsoft Forms attendance responses against a predefined list of required participants to immediately identify who did not attend.
- Product
- Microsoft Forms
- Device & OS
- not provided
- Scenario
- Tracking event or class attendance using Microsoft Forms and verifying submissions against a master roster.
- Observed behavior
- Microsoft Forms collects attendance data successfully but lacks a native feature to cross-reference responses with a required participant list to generate an absentee report.
Ensure you have your master list of required participants, including their names and unique identification numbers, ready in a spreadsheet before exporting your form responses.
Export Responses and Compare in Excel Using COUNTIF
Since Microsoft Forms cannot automatically calculate missing participants, you must export the data to a spreadsheet and use lookup formulas to find absences.
Microsoft Forms is an excellent data collection tool, but it does not have a built-in relational database to compare submitted responses against an external roster. The most efficient workaround is to export your collected data to a spreadsheet program and use a formula like COUNTIF or XLOOKUP to identify missing IDs.
Open your attendance form in Microsoft Forms, navigate to the 'Responses' tab, and click 'Open in Excel' to download the collected attendance data.
Open the downloaded Excel file. Add a new worksheet and paste your master list of required participants (names and ID numbers) into it.
In a blank column next to your master list IDs, type the formula =COUNTIF(FormsData!B:B, A2) (assuming column A holds your master IDs and the FormsData sheet column B holds the submitted IDs). Drag the formula down to apply it to all participants.
Apply a Data Filter to your new formula column. Filter the results to only show participants with a count of '0'. These are the individuals who did not submit the attendance form.
Easily Identify Absent Participants with WPS Spreadsheet
You can seamlessly process your Microsoft Forms attendance exports using WPS Spreadsheet. By leveraging its powerful built-in formulas, you can cross-reference rosters and identify absent participants in seconds.
- 1. Open Exported Data: Launch WPS Spreadsheet and open the .xlsx file downloaded directly from Microsoft Forms.
- 2. Insert Master List: Create a new worksheet tab and paste your required participant list, including names and student/employee IDs.
- 3. Apply Lookup Formula: In an adjacent column, use the =COUNTIF() formula to compare your master list IDs against the submitted form responses.
- 4. Filter the Results: Use the Data Filter tool in WPS Spreadsheet to show only '0' values, instantly revealing the missing participants.
Frequently Asked Questions
Can Microsoft Forms automatically notify me of absent participants?
No, Microsoft Forms only collects submitted responses and cannot independently reference an external list. You must export the data to a spreadsheet program like Microsoft Excel or WPS Spreadsheet to find who is absent.
Which spreadsheet formula is best for checking attendance?
COUNTIF is generally the easiest method. By counting how many times a participant's ID appears in the form responses, any result of '0' immediately identifies an absent individual. XLOOKUP can also be used if you need to return specific submission details.
Can I restrict my Microsoft Form to only the required participants?
Yes. If you are using a school or work Microsoft account, go to the form settings, choose 'Only people in my organization can respond', and select 'Specific people in my organization can respond' to limit access strictly to your required list.
