Analyze collected market research in Excel with clear response denominators and segment comparisons. Connect the findings to matched customer sales without confusing stated interest with buying behavior.
Market research Excel analysis compares valid responses across customer segments, then puts those findings beside sales evidence. Start with the research you have already collected and preserve the distinction between what respondents say, what customers buy and what the wider market might do.
The SBA describes market research as a way to understand customers and the business environment. The workflow below analyzes an existing business research table; it does not select respondents or establish that they represent a whole market.
A US business-software company asks nominated contacts at customer accounts whether a proposed reporting feature would remove a purchasing obstacle. Its analysis rule retains one response per account for this question and research round. The question has two valid answers, Yes and No; a blank remains missing.
Use A for response ID, B for account ID, C for segment, D for answer and E for the account's prior-quarter net sales in USD. Sales have already been aggregated to one row per account from the sales export before being matched to the research table. There are no unmatched accounts in this example.
| Response | Account | Segment | Answer | Prior-quarter net sales (USD) |
|---|---|---|---|---|
| R1 | A1 | Midmarket | Yes | 1,200 |
| R2 | A2 | Midmarket | Yes | 800 |
| R3 | A3 | Midmarket | No | 600 |
| R4 | A4 | Midmarket | blank | 400 |
| R5 | B1 | Enterprise | Yes | 3,000 |
| R6 | B2 | Enterprise | No | 2,000 |
| R7 | B3 | Enterprise | No | 1,800 |
| R8 | B4 | Enterprise | No | 1,200 |
| Total | 11,000 |
The word blank represents an empty D5 cell. Preserve the unedited export separately and document any recoding, duplicate selection and exclusions. If your research allows several people from one account, first decide whether the comparison concerns people or accounts; do not switch between them halfway through the analysis.
Create a summary starting in G1 with these columns: Segment, Submitted, Yes, No, Valid, Missing and Yes share. Put Midmarket in G2 and Enterprise in G3.
In H2 through M2, enter these formulas and fill down:
=COUNTIFS($C$2:$C$9,G2)
=COUNTIFS($C$2:$C$9,G2,$D$2:$D$9,"Yes")
=COUNTIFS($C$2:$C$9,G2,$D$2:$D$9,"No")
=SUM(I2:J2)
=COUNTIFS($C$2:$C$9,G2,$D$2:$D$9,"")
=IF(K2>0,I2/K2,NA())
Microsoft documents COUNTIFS for counting rows that meet specified criteria. Defining valid as Yes plus No prevents an unexpected text value from silently entering the denominator. Covirage is not affiliated with Microsoft.
| Segment | Submitted | Yes | No | Valid | Missing | Yes share |
|---|---|---|---|---|---|---|
| Midmarket | 4 | 2 | 1 | 3 | 1 | 66.7% |
| Enterprise | 4 | 1 | 3 | 4 | 0 | 25.0% |
| Total | 8 | 3 | 4 | 7 | 1 | 42.9% |
Display percentages to one decimal place and keep full precision in the cells. Midmarket's Yes share is 2 / 3 = 66.7%; Enterprise's is 1 / 4 = 25.0%. The difference is 41.7 percentage points, rounded, calculated from the unrounded shares.
The total is 3 / 7 = 42.9%. Do not average the displayed segment percentages because their valid-answer denominators differ. For a broader questionnaire, survey analysis in Excel covers rating distributions and question-specific validity rules.
The submitted count must satisfy 4 + 4 = 8 response rows. Valid answers must satisfy 3 + 4 = 7, and 3 Yes + 4 No must also equal 7. Finally, 7 valid + 1 missing = 8 submitted. In each segment row, =H2-K2-L2 must return zero; investigate unexpected answer values if it does not.
If a new segment label appears in the export, a fixed two-row summary will omit it. Compare the summary count with the response-table count on every refresh. Investigate any difference before presenting segment results.
These eight responses give a descriptive comparison of participating accounts. They do not establish statistical significance, a market-wide preference or a projected sales increase. A useful next action is to ask why the two segments answered differently and compare those explanations with recorded purchasing obstacles.
To sum matched sales by segment, put the same segment labels in G2:G3 and use:
=SUMIFS($E$2:$E$9,$C$2:$C$9,G2)
Microsoft's SUMIFS documentation explains conditional sums. The resulting Midmarket total is 1,200 + 800 + 600 + 400 = $3,000; Enterprise is 3,000 + 2,000 + 1,800 + 1,200 = $8,000. Together they reconcile to $11,000.
The accounts answering Yes contributed $1,200 + $800 + $3,000 = $5,000, or 5,000 / 11,000 = 45.5% of matched prior-quarter sales, rounded. That is a historical sales share represented by those accounts. It is not a probability of conversion, expected revenue from the feature or a replacement for the unweighted response distribution.
If you also analyze a rating question, keep its distribution visible and use the dedicated Likert-scale analysis guide for that chart. A customer preference table also differs from market share, which needs a comparable market denominator.
Bring a sample of your own coded research responses and matched customer sales table to Covirage, which maps the columns. Its tools compute and check the supplied segment comparisons; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and set up your business with a sample of your own data.
See self-service analytics for business teams working from their own files.
Yes. A spreadsheet can summarize collected responses, calculate segment percentages and connect findings to a controlled customer table. The result depends on consistent coding, valid denominators and suitable research design; a spreadsheet calculation alone does not make a group representative.
Usually they should be reported separately as missing answers. Count them as no only if the questionnaire and analysis rules explicitly define that treatment. Otherwise, turning blanks into no changes the reported distribution without any additional customer evidence.
Not from your response file alone. Market share needs a defined market and a comparable total-market denominator. A customer survey describes the people or accounts represented in its responses, subject to how those respondents were selected.