Do you input a hyphen to represent zero? If you do, please stop doing so in the future. The proper way of displaying “-” for zero is to apply relevant cell format to it.

E.g. Using Accounting format with no currency symbol:

or apply the following custom format string if you want to display number with no decimal and the ordinary +/- sign:

#,##0; -#,##0; -

Note: The first, second, and third part indicates how we'd to format positive number, negative number and zero respectively.

Why does it matter?

It doesn’t matter only if you do not expect further processing / calculations of your data. That should rarely be the case… I guess.

Let’s illustrate with a simple example:

When we input “-” (hyphen) as zero (as in B4 shown above), it is interpreted as text by Excel and most functions ignore it by default. As a result, if we average the range of data, the “-” is ignored and Excel returns 1 instead of 0.75, which should be the expected answer when “-” means zero (not blank).

Well, in case you really want to exclude zeros in your calculations, you may consider AVERAGEIFS:

=AVERAGEIF(C2:C5, "<>0")

Another hassle with hyphen as zero is especially for those who use Power Query. In Power Query, when we define a column as Whole Number (or other numbers), non-numeric values yield to “Error”. See below:

You may be thinking… it is super easy to replace “-” with zero in Power Query (by using Replace Values). Why does it matter?

The question is, do we expect the “hyphen” during the stage of data cleansing?

In real world, we deal with data of much much much more than 10 rows. Indeed, it’s common to deal with 10 thousands rows of data and even more if you are in position of data analytic. Unless you know the data inside-out before cleansing, it is very unlikely that you will add a step to replace “-” with zero when you don’t see it in the Preview of your data. Frankly, did you?

It is not until we encounter errors after Refresh. 😣

Remember, in Power Query Editor, we see the first 1000 rows data as sample. When the “-” is sitting low enough, or even in a newly added file to a folder, how are we supposed to expect it and apply the corresponding cleansing step?

Now we know one more potential trap in data waiting for cleansing. 😎

Have you experienced other data traps? Please share with us by leaving comments below.

p.s. I am a strong believer of fixing problem at the source.  Whenever we can, have the data input/collect in a correct way at the very beginning and the (data) world will become a better place. 😉