Published November 2026 · 10 min read

Excel Survey Response Categorization: A Practical Tutorial (With Sample Files)

This is a hands-on tutorial. Download the two workbooks below, open them next to this page, and by the end you will have a category summary, a breakdown by segment, a chart and a list of quotes for each category. All of it is built with ordinary Excel formulas. Part 1 shows how far you can get categorizing responses with formulas alone, and where that stops. Part 2 works from a categorized file and covers everything you need to report on it.

The Structure That Makes Everything Easy: One 0/1 Column per Category

Whatever method you use to categorize, aim for this layout: the response text in one column, then one column per category holding 1 (the response belongs) or 0 (it doesn't). A response that mentions two things gets two 1s.

This layout is what makes the formulas in Part 2 one-liners. The average of a 0/1 column is the share of respondents in that category.

Part 1: Categorizing With Formulas (and Where It Stops)

Open library_survey_2026.xlsx. In G1 type hours, then in G2:

=--OR(ISNUMBER(SEARCH({"hour","open","close","sunday"}, $E2)))

SEARCH is case-insensitive and returns a position when a keyword is found. ISNUMBER turns that into TRUE/FALSE, OR checks all four keywords, and -- converts the result to 1 or 0. Fill it down to row 241, then build similar columns for the other themes.

Now filter column G to 1 and read what you caught. Then filter to 0 and read what you missed. In the sample file you will find:

• "The car park closes before the library does" is flagged as hours because it contains "close". It is a parking complaint.
• "The selection feels dated" is a books comment that no sensible keyword list for books catches.
• "It gets very noisy after school" is missed unless you think of "noisy" as well as "quiet". Next survey, someone will write "too loud".

Formulas are worth knowing for quick checks and very short, predictable answers, like a "which product?" question where people type brand names. For genuine open-ended questions they fail in both directions at once, and the only way to find out how badly is to read every response, which is the work you were trying to avoid.

The practical alternatives are hand-coding (type the 1s yourself, around 50 to 100 responses an hour) or AI coding, where a model reads each response against categories you approve and fills in the columns. The second workbook is what that output looks like. If you would rather produce it from your own data, upload the workbook to SurveyCat, review the suggested categories, and download it back with columns G onward added. Then carry on with Part 2.

Part 2: Reporting on Categorized Responses

Open library_survey_2026_coded.xlsx. Columns G to L hold the six categories and M is q_improve_cat_none: 1 when a response fits no category or was left blank.

Step 1: The summary table

Add a new sheet called Summary. List the categories in A2:A7 and use these formulas in row 2, filled down (adjust the column letter per row, or use the INDEX version below):

Count:              =SUM(Data!G$2:G$241)
% of everyone:      =AVERAGE(Data!G$2:G$241)
% of those who answered:
                    =AVERAGEIFS(Data!G$2:G$241, Data!$E$2:$E$241, "<>")

One formula for all rows (put the category number 1-6 in column B):
                    =AVERAGEIFS(INDEX(Data!$G$2:$L$241, 0, B2), Data!$E$2:$E$241, "<>")

The "<>" condition means "not blank", so the last version excludes the 15 people who skipped the question. That is usually the right base for an open-ended question. Format as percentages and sort descending:

The percentages add up to more than 100%. That is correct: 52 respondents mentioned two things. Label the column "% of respondents" so nobody tries to make it sum to 100.

Step 2: Break it down by segment

Put the branch names across the top (B10:E10 = Central, Northside, Riverside, Westgate), the categories down the side, and add a second condition to AVERAGEIFS:

=AVERAGEIFS(Data!G$2:G$241, Data!$B$2:$B$241, B$10, Data!$E$2:$E$241, "<>")

Add conditional formatting (Home → Conditional Formatting → Color Scales) and the story jumps out: Central wants longer hours, and the other three branches have a Wi-Fi and computers problem. Branch groups here are only 49 to 66 people, so treat differences of a few points as noise.

Step 3: Or let a pivot table do it

If you prefer pivots, first add a helper column so you can exclude blanks: in N1 type answered, and in N2 =--(E2<>""), filled down. Then:

1. Select the data and choose Insert → PivotTable.
2. Drag answered to Filters and set it to 1.
3. Drag branch to Columns.
4. Drag each category column to Values, and change each from Sum to Average (Value Field Settings).
5. Format the values as percentages. Excel puts the categories in rows automatically once there are several value fields.

Step 4: Pull the quotes for each category

Numbers say how many. Quotes say what people meant, and reports need both. In Excel 365 or 2021, one formula lists every response in a category:

All Wi-Fi and computers responses:
=FILTER(Data!E2:E241, Data!J2:J241=1)

Wi-Fi responses from Northside only:
=FILTER(Data!E2:E241, (Data!J2:J241=1) * (Data!B2:B241="Northside"))

This doubles as your quality check. Read down the list, and if anything does not belong, you will spot it in seconds. Do the same for column M (none) with (Data!E2:E241<>"") added. If the non-blank "none" answers share a theme, you are missing a category.

Step 5: Chart it

Select the category names and the "% who answered" column from your sorted summary table and choose Insert → Bar Chart → Clustered Bar. Horizontal bars suit category labels better than columns. In Format Axis, tick Categories in reverse order so the biggest bar is on top. Add data labels, delete the gridlines, and title the chart with the finding ("Wi-Fi and opening hours lead patrons' wish lists"), not the question number.

Reusing This for Your Next Survey

Once the Summary sheet exists, it becomes a template. Paste the next survey's categorized data into the Data sheet and every formula updates. Two things keep that working:

• Same categories, same order. SurveyCat names Excel columns from the question and category, e.g. q_improve_cat_quieter_study_space, so using the same category list gives the same columns in the same place. See reusing a codebook across survey waves.
• Turn the data into a Table (Ctrl+T) and use structured references, so formulas grow with the row count instead of stopping at row 241.

Frequently Asked Questions

Can Excel categorize open-ended responses automatically?

Only with keyword formulas, which miss synonyms and mis-flag shared words, as Part 1 shows. For real open-ended answers, use AI coding to fill the category columns and Excel to analyze them.

Should my base be everyone or only people who answered?

Usually only those who answered, using AVERAGEIFS with "<>". State the base under every table either way.

How much does it cost to get my own file categorized?

1 credit = one classified response to one question. Blank answers are free. The sample's 225 answers would cost 225 credits. The 80 free credits on a new account cover a test run of up to 80 answers, and a Starter pack ($25 for 850 credits) covers a typical full project.