How can I plan a survey-results sheet so unanswered questions remain visible in the summary?

Plan for response status explicitly rather than treating every empty-looking cell alike. For each question, distinguish three cases: an actual answer, no response, and not applicable. An answer such as “0” or “None” is still an answer if the respondent deliberately entered it; it should not be counted as missing just because it looks sparse.

A useful structure has one row per respondent and one column per question, plus a separate status field for each question. For example, columns might be Q1_answer and Q1_status. Use consistent status labels such as Answered, No response, and Not applicable. If your survey has a meaningful “Prefer not to answer” choice, include it as its own status or answer choice rather than silently combining it with a blank.

Then make the summary show, for every question, the number of records in each status. Keep answer breakdowns separate. That way a reader can see both what people answered and how many did not provide an answer.

Don’t guess from the blank alone. First check whether the survey or export includes a rule or field that identifies why a response is blank. If it does, use that information to assign the appropriate status. If it doesn’t, label the case Unknown or Unclassified in your working sheet and explain that the reason cannot be determined from the available data. Don’t relabel it “Not applicable” without evidence.

For a copyable status set, use:

Answered No response Not applicable Unknown

You may omit Unknown if your source reliably distinguishes the other cases. If you do use it, give it a visible count in the summary; otherwise, unresolved blanks can vanish just as easily as skipped questions. Preserve the original imported value in a separate column or source tab, so your classification can be checked or revised without losing what the export contained.

Give each question its own summary block or row, with status counts placed before or alongside the answer results. For example, using entirely hypothetical numbers:

Question: How often do you use the service? Answered: 18 | No response: 4 | Not applicable: 2 | Unknown: 0 Among 18 answers: Daily 7 | Weekly 8 | Rarely 3

The important part is that the answer breakdown says it covers 18 answers, rather than implying it covers everyone represented in the sheet. If a percentage is useful, state its denominator: for example, “8 of 18 answered weekly,” not simply “44%” with no explanation. Keep the status totals visible even when they are zero, or choose a consistent display rule and note it clearly.

A compact reusable outline is:

Question label Total records | Answered | No response | Not applicable | Unknown Answer breakdown (answered responses only) Notes on any classification or denominator

Use the same order and labels for every question so readers can compare sections easily.

Yes. Write down the status definitions once, use the same labels consistently, and keep the respondent-level data separate from the summary. Give each question a stable, clear label, and document how you decided whether a blank was a no response, not applicable, or unknown. Before sharing the summary, compare the status counts with the number of records included for each question; explain any difference rather than forcing counts to match by recoding uncertain cases.

For example, a short note at the top of the sheet could say: “Answered means a response value was recorded. No response means the question was left unanswered according to the available survey information. Not applicable means the survey identifies the question as not applicable. Unknown means the available information does not establish why no answer appears.” Adjust those definitions to match what your survey actually records. A spreadsheet can make the distinctions visible, but it cannot determine a respondent’s reason when the source data does not say.