Take the 2-minute tour ×
User Experience Stack Exchange is a question and answer site for user experience researchers and experts. It's 100% free, no registration required.

We have an application that allows users to design their own forms and export the data to excel. One problem is the representation of null or empty data. There are two scenarios that we need to support:

  1. The data was not present at all (question was skipped)
  2. The data was empty (question was left blank)

What is the best default representation of this in Excel/csv? Currently we are leaving the cell empty for scenario 1 are struggling to find a good representation for scenario 2. The requirements for this are:

  • Should not be tied to a particular language (so "no data" is not good)
  • Must play nice with stats programs (not sure what they don't like, but "---" is bad)
  • Should be differentiable from each other
  • Must not rely on special formatting (e.g. they may get a csv and should still be able to differentiate)


share|improve this question
Can you clarify the difference between a question being skipped and the question being left blank? If a user skips a question, have they not left it blank? –  Matt Obee Feb 5 '13 at 17:40
Yeah, so in our forms, questions can be made (ir)relevant by other questions, and it's important to be able to differentiate in the export from "this question wasn't asked" versus "this question wasn't answered". –  Cory Feb 5 '13 at 18:33
add comment

5 Answers

up vote 4 down vote accepted

As a data engineer, I would suggest you can use "NA" or "NAN" for empty data cell because of the following 2 reasons which I could think of:

  1. It is a standard terminology for null data in data industry
  2. and, of course it will make life easy for data engineers
share|improve this answer
+1, when cell is left blank some user could perceive that something didn't work and the value is ambiguous. Populating it with value clarifies that data wasn't provided. –  Anna Rouben Feb 5 '13 at 18:41
NA could be understood as "Not Applicable" (question was skipped -- scenario 1), or "No Answer" (question was not answered -- scenario 2). Ambiguous again. –  Pasha S Apr 2 '13 at 7:49
@PashaS "NA" in data science field means "No Answer" and "NAN" means "Not a Number" and we use these two strings to represent missing or no data. –  Ankit Apr 3 '13 at 1:17
@AnkitSharma: and it could also be spelled as "Not Available". But linguistics aside -- in this case you have 2 different reasons for missing data. And I don't see any logical way to assign "N/A" to either the first or the second scenario. –  Pasha S Apr 3 '13 at 6:30
However this makes the cells non numeric so difficult to sum/average, import into another app etc –  Mark Apr 3 '13 at 13:36
show 1 more comment

You approach for data not present seems alright. For a NULL entry, there also is ∅ (Null sign), which expresses precisely that something is empty.

If you prefer a less mathematical symbol, — (em-dash, an "extra-wide" dash) is a good typographical choice that is often used for things left out (e.g. € 345,— instead of € 345,00)

Both are unicode characters and should be no problem in csv data. Either one stand out from regular input, but personally, I feel the null sign to be the more appropriate, as it also works for, let's say, an unselected radio choice.

share|improve this answer
null sign is neat. do you know offhand if there are any popular stats programs that don't like unicode? –  Cory Feb 5 '13 at 19:13
Not sure what you refer to by "stat programs", but anything that takes on more than ascii should be able to handle it. MS Office and OpenOffice for the very least do. –  kontur Feb 5 '13 at 19:44
add comment

If you want it to be easily noticeable, you have to use something clearly visual that doesn't require someone to process some text first. The options there include: changing the border colour and weight; background colour; and content.

Here are three examples to illustrate. The fourth shows a common solution that is difficult to scan, and therefore much slower to use. I would not recommend using this.

enter image description here

share|improve this answer
thanks, added a note that since we also depend on .csv we need a solution that doesn't only rely on formatting. –  Cory Feb 5 '13 at 18:34
If they get a csv, and open it in excel, you should still be able to apply conditional formatting on that file. If you want to restrict that, then the only option is text that would never otherwise be used. It is no longer really a UX question as much as a "what text do they want" question. –  JohnGB Feb 5 '13 at 18:37
isn't the value chosen for the default text an important part of the user experience? the csv file is system generated so we couldn't put formatting in it manually. –  Cory Feb 5 '13 at 18:43
@Cory The default text is usually just blank for a cell that is empty. If you just want to know what the best text should be for missing data, then you need to ask a different question. –  JohnGB Feb 5 '13 at 19:39
add comment

You are in a situation where the data can be missing due to 2 different reasons. As others suggested, there are many ways to represent missing data:  (blank), N/A, NaN, , ---, , etc. So you could pick any two (say, first two) and be done. Here's a problem however: how the end user is supposed to remember which one is which? Without a logic reason, or at least a mnemonic, your users will be eternally confused.

This is because humans are notoriously bad at memorizing exactly 1 bit of information (see an example). The reason being that if you were once confused about something, and then you found an answer, then the next time you will remember your confusion much more vividly – just because the answer was so short and unremarkable.

Anyways, I don't think there is a standard way out of this situation in Excel/CSV. But I can tell you about Stata (it's a statistical analysis software). In Stata you can have up to 27 different missing value types. The "standard" missing value ., and also "extended" missing values .a, .b, ..., .z. So for example in your case you would've denoted the first-case missing answer as .a, and second-case as .b. How can this be memorized? Well, you have 2 chances of getting a missing answer: first, the question can be missing; second, the question may have been present but the user didn't answer. The situations have clear logical order, and their encodings have clear alphabetical order. Unambiguous.

share|improve this answer
add comment

I would export the data for an answer in two columns

  1. The value (null if not set)
  2. A value giving the status of the value Correct, User did not answer, Question was skipped etc.

The reasons for this include

There is no information lost the user can see exactly how the data was missed.

The columns of values are of one type and can be averaged, summed etc.

If you need to filter out for calculations you have the flags to tellyouwhat to discount

share|improve this answer
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.