Best way to represent null and empty data in an excel file

Question

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?

Answer 1

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

Answer 2

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.

Answer 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.

Answer 4

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.

Answer 5

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