How can I find the most common answer in a survey without counting blanks?
Quick answer
First decide which differently formatted answers should count as the same response, and standardize them consistently. Then count the cleaned response field. In Excel, a PivotTable can place the response field in Rows and Values, with Values summarized by Count; Count counts nonempty values, so blank response cells are excluded. Check the resulting totals and report every answer tied for the highest count.
The question, the details, the next step.
I have a survey column with answers like “Email,” “email,” and some empty cells. How do I find the most common answer without blanks distorting the result?
Use two steps: make the responses comparable, then count them. Decide whether “Email” and “email” mean the same thing for your survey. If they do, apply one consistent rule and use a cleaned response column or an agreed mapping list. Keep the original answers unchanged so you can check your work. Then summarize the cleaned column. In Excel, Microsoft documents using a PivotTable with the response field in Rows and Values, setting Values to Count. Count counts nonempty values, so blank cells are excluded. [S1] A targeted COUNTIF is another documented option when you already know which exact answer you want to count. [S1]
What should I do about spaces, capitalization, or answers that seem similar but may not mean the same thing?
Write a small normalization rule before counting; don’t merge answers just because they look alike. For example, you might decide that capitalization and accidental spaces do not change the meaning, while “Email” and “Text message” remain separate choices. If respondents entered free text, review likely variants and map them only when your survey context supports treating them as equivalent. Keep ambiguous answers separate or mark them for review rather than guessing. Record the rule beside your summary so another person can understand how categories were formed. The cited Excel guidance explains how to count values, but it does not say which differently formatted responses are equivalent; that decision is yours to document. [S1]
Can you show the PivotTable setup, and how do I make sure the blank answers aren’t included?
For the cleaned response field, set up the PivotTable like this:
- Put the response field in Rows.
- Put that same field in Values.
- Set the Values summary to Count.
Microsoft describes this arrangement for counting response occurrences and defines Count as counting nonempty values. That is why blank cells in the counted field are excluded. [S1] Check that the Values heading says Count, not a different summary, and compare the displayed total with the number of nonblank responses you expect. If your data has other columns, make sure you are counting the response field itself—not a field that is filled in even when the answer is blank.
How should I report the result if two answers have the same highest count?
Report a tie rather than choosing one answer as the single most common. Here is a copyable layout; replace the bracketed fields with your results:
Question: [survey question] Responses counted: [number of nonblank responses] Standardization rule: [brief rule used] Most common response(s): [answer A] — [count]; [answer B] — [count] Blank responses excluded: [yes/no]
If there is only one highest count, list that answer alone. Keep the total and the rule with the result: a count is easier to interpret when readers can see what was counted and whether similar-looking entries were grouped.
A practical checklist
Keep an unchanged copy of the original response column.
Decide and write down which formatting differences or variants should be grouped.
Apply the same normalization rule to every response; leave uncertain answers separate or flag them for review.
Use a PivotTable with the response field in Rows and Values, and set Values to Count.
Confirm the count excludes blanks and report all answers tied for the highest count.
A few more useful answers
Can I use COUNTIF instead of a PivotTable?
Yes. Microsoft documents COUNTIF for counting a particular value. Use it when you want the count for a specific cleaned answer; a PivotTable is useful for summarizing occurrences across the response field. [S1]
Does the counting method decide whether two differently formatted answers mean the same thing?
No. The cited instructions explain counting, not how to interpret response wording. Decide and document a normalization rule before counting. [S1]
What if every response is blank?
There is no nonblank answer to identify as most common. Report that no responses were available to count rather than naming a most common answer.
Sources
References used for this guide.
- [S1] Count how often a value occurs in Excel support.microsoft.com · Checked