16

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)

Thoughts?

3
  • 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, 2013 at 17:40
  • 1
    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, 2013 at 18:33
  • Historically empty values in tables were represented as a dash, so it might be some single dash character. Typography prefers En or Em dash, but for usability it would be easiest to input a good old hyphen, which I personally use to denote empty values for parsing tasks via Excel.
    – Mikhail V
    Dec 2, 2017 at 16:10

5 Answers 5

6

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
7
  • 3
    +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. Feb 5, 2013 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
    Apr 2, 2013 at 7:49
  • 1
    @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, 2013 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
    Apr 3, 2013 at 6:30
  • However this makes the cells non numeric so difficult to sum/average, import into another app etc
    – mmmmmm
    Apr 3, 2013 at 13:36
7

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.

2
  • null sign is neat. do you know offhand if there are any popular stats programs that don't like unicode?
    – Cory
    Feb 5, 2013 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, 2013 at 19:44
3

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

5
  • 2
    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, 2013 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, 2013 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, 2013 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, 2013 at 19:39
  • I vote for the hyphen. It is easy to type, parse and respects the typography tradition to denote 'nothing' with a dash in tables. but afaik there is no official standards about this.
    – Mikhail V
    Dec 2, 2017 at 16:27
1

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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