A form can collect useful feedback quickly, but the exported responses still need careful review. Blank answers, invalid ratings, changing question labels, and personal information can all distort the analysis.
In this project, you will build a Microsoft Excel workbook for 30 made-up workshop-feedback responses. You will preserve the original responses, define what each question means, summarize ratings and topic choices, separate comment coding from the source data, and confirm that every response is counted correctly.
By the end, you will be able to explain how many people were eligible to answer each question, how many supplied a valid response, and which records need attention.
What the finished analysis workbook includes
The downloadable workbook contains four main working areas:
- Responses: the original 30 practice responses.
- Question Dictionary: the response type, allowed values, missing-answer meaning, and analysis rule for each question.
- Analysis: the response counts and checks for the rating question.
- Comment Coding: a separate table for grouping optional comments into a small set of themes.
The practice example includes 27 valid ratings, 2 missing ratings, and 1 invalid rating. Those values add back to the 30 eligible responses.
Use the accompanying Form-Response Analysis workbook to follow the project and inspect the finished setup. It includes made-up practice responses, working formulas, entry controls, a response-audit chart, comment coding, instructions, documented checks, a failure-and-repair example, and version history.
Workbook note: The file uses made-up practice data that contains no real personal information. Make a separate copy before adding your own responses. Remove names, email addresses, private comments, and other identifying details before sharing an analysis workbook.
Begin with a few answerable questions
Before building summaries, decide what you need to learn from the responses. For this project, the workbook answers five practical questions:
- How many responses were eligible for the rating question?
- How many ratings were valid?
- How many ratings were missing?
- How many ratings were outside the allowed range?
- What broad themes appear in the optional comments?
This focused scope prevents the workbook from becoming a large survey-analysis system. It also makes every result easier to check.
Preserve the response table
Open the Responses sheet. Keep one row per response and do not rewrite the original values during analysis.
| Field | What it contains |
|---|---|
| Response ID | A stable identifier such as R001, R002, or R003. |
| Timestamp | The date associated with the practice response. |
| Rating | A whole number from 1 to 5, a blank, or the deliberately invalid value 7. |
| Topic | Formulas, Charts, or Cleaning. |
| Comment | An optional short response such as Useful practice, More examples, or Clear steps. |
The workbook uses data validation for new rating and topic entries. Data validation is an Excel rule that limits what someone can enter in a cell. Existing imported data can still contain invalid values, so the analysis must check the source rather than assuming every record is valid.
Define each question before analyzing it
The Question Dictionary explains how each field should be treated.
| Field | Response type | Allowed values | Missing answer means | Analysis rule |
|---|---|---|---|---|
| Rating | Whole number | 1 to 5 | No rating was supplied | Count only values from 1 through 5 as valid |
| Topic | Choice | Formulas, Charts, or Cleaning | No topic was selected | Count each approved choice |
| Comment | Text | Free text | No comment was supplied | Code themes separately while preserving the original response |
This step matters because a blank rating, an invalid rating, and a valid low rating are not the same thing.
Count the valid ratings
The workbook uses COUNTIFS to count ratings that are at least 1 and no more than 5. COUNTIFS is an Excel function that counts records meeting more than one condition.
The formula follows this pattern:
=COUNTIFS(Responses[Rating],">=1",Responses[Rating],"<=5")
In the practice data, this returns 27 valid ratings.
The workbook also counts blank ratings and calculates invalid ratings:
- Eligible responses: 30
- Valid ratings: 27
- Missing ratings: 2
- Invalid ratings: 1
Check that the response counts match
The most important count check is:
Valid + missing + invalid = eligible responses
For the practice workbook:
27 valid + 2 missing + 1 invalid = 30 eligible responses
The Analysis sheet includes a difference check. The expected difference is zero. If it is not zero, a response may have been counted twice, excluded, or assigned to the wrong group.
Do this check separately for each question. A person can answer one question and skip another, so the valid-response count may differ from question to question.
Show distributions, not only averages
An average can hide important differences. Two rating sets can have the same average even when one group is consistently satisfied and another is split between very low and very high ratings.
When you adapt the workbook, report:
- The valid-response count
- The number or percentage for each rating from 1 to 5
- The number of missing and invalid responses
- The average only when it adds useful context
Always display the valid-response count near the result. “Average rating: 4.2 based on 27 valid responses” is clearer than “Average rating: 4.2.”
Keep comment coding separate
The Comment Coding sheet copies the Response ID and original comment into a separate working table. The source Responses sheet remains unchanged.
The practice workbook uses four theme choices:
- Useful practice
- Requests more examples
- Clear instructions
- Unclear
Use Unclear when a comment does not fit the approved themes. Do not force every response into a category simply to make the counts look complete.
A small hand-coded theme list can describe the practice responses. It should not be presented as a universal conclusion, automated sentiment result, or statement about what respondents intended.
Protect privacy before sharing
A public analysis should contain only what readers need to understand the method and result.
Remove or replace:
- Names
- Email addresses
- Phone numbers
- Employee or customer identifiers
- Private free-text comments
- Links that reveal restricted files or systems
If the source comments contain sensitive details, create a safe summary rather than publishing the original text.
Try five practical checks
| Check | What to do | Expected result |
|---|---|---|
| Complete response set | Load all 30 practice responses. | The eligible-response count is 30. |
| Valid rating count | Count ratings from 1 through 5. | The valid-rating count is 27. |
| Invalid rating | Keep the supplied rating of 7 in the imported source. | It is counted as invalid rather than valid. |
| Response-count check | Add valid, missing, and invalid ratings. | The result equals 30 and the difference is zero. |
| Privacy review | Inspect the version prepared for sharing. | No names, email addresses, or private comments remain. |
Customize the workbook for your form
After you understand the practice example, make a copy and adapt it carefully.
- Replace the practice responses with a safe copy of your own export.
- Do not edit the original response values during analysis.
- Update the Question Dictionary for every field you plan to summarize.
- Define valid, missing, and invalid values before counting responses.
- Create a separate coding table for comments.
- Review formulas and chart ranges after adding rows.
- Recheck every question using its own valid-response count.
- Remove personal information before sharing the file.
The workbook is a completed learning example rather than a full survey platform. It does not collect responses, identify people, infer emotions, perform validated sentiment analysis, or establish statistical significance.
Common mistakes to avoid
- Using one response count for every question: Missing answers can differ by question.
- Counting an invalid option as valid: Check values against the documented allowed range.
- Editing the source during analysis: Keep correction and coding work in separate tables.
- Reporting an average without a distribution: The average can hide disagreement.
- Forcing every comment into a theme: Keep an Unclear choice.
- Treating themes as proven intent: Comment coding is an organized interpretation, not mind reading.
- Sharing identifiable responses: Remove private information before distributing the analysis.
What this project helps you practice
This project gives you experience with Excel Tables, response rules, data validation, COUNTIFS, blank and invalid-value handling, response-count checks, distributions, comment coding, and privacy review. It also shows why careful counting matters as much as the final chart or average.
Continue the project workflow
- Clean form-response data before analysis when dates, categories, or text values are inconsistent.
- Turn survey measures into a monthly report when the same form is reviewed every month.
- Present the response analysis as a spreadsheet project if you want to explain the counting rules, privacy controls, and comment-coding decisions.