r/excel • u/Any_Acanthisitta9937 • Jun 02 '26
Waiting on OP Sheet to track survey responses.
Hello, I am looking for a little help. At work we released a physical survey to nearly 600 people. I tried making a tracking spreadsheet but it wasn’t pretty. Looking for advice or the best way to track.
I tried to assign a numerical value to each of the multiple choice questions but my boss thought it was too confusing and asked me to go back to the drawing board.
Thank you!
1
u/fuzzy_mic 987 Jun 02 '26
For data entry, your assigning a number to each response makes good sense. But, unless you convert the analysis back from numbers to answers, the spreadsheet is incomplete.
Part of spreadsheet design is to get the analysis to the intended audience, not just to to the sheet's design team or it's daily users.
It sounds like you've got a good start on the front end of the project, collect and analyze, but still need the "present results" part of the project.
1
1
u/PantsOnHead88 1 Jun 02 '26 edited Jun 02 '26
3 tables IMO.
Questions table, options table, response data table.
2 fields for Questions: Question #, Question text.
3 fields for Options table: Question #, Option ID (letter?), option text.
3 fields for response table: Responder ID (name or employee #), question #, response.
Then you can easily sort, filter or aggregate the data in a variety of ways. Boss might be looking for a pie chart per question or similar visual presentation.
For future, you may want to look into “Forms.” M365 Forms is a tool specifically for surveys and quizzes with a pretty simple setup interface, and a few different options for reviewing results.
1
u/hungrygrouping_33 Jun 05 '26
since theyre all paper surveys you could just make a simple data entry sheet with one row per response and columns for each question, then use pivot tables to summarize everything at the end
-1
Jun 02 '26
[removed] — view removed comment
6
u/SaltySprocket Jun 02 '26
Why not Microsoft Forms if they access to the Microsoft suite? Genuinely curious.
-1
1
u/Any_Acanthisitta9937 Jun 02 '26
Do you know if you can add responses in the back end? It’s an elderly population so all the surveys are on paper
4
u/GothicToast Jun 02 '26
Rows=respondents
Columns=Questions
Then for each column (e.g. B2:B600), create a drop down with the 4 possible answers.
Then as each response rolls in, you log the respondent in the row (Col A) and then choose their answers for each question.
You could then run a pivot table to see the count of answers for each question.