office 2016 mail merge shows 0 istead of hyphenated number

Remember, in Power Query Editor, we see the first 1000 rows data as sample. It is not until we encounter errors after Refresh. 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.

Indeed, it’s common to deal with 10 thousands rows of data and even more if you are in position of data analytic. In real world, we deal with data of much much much more than 10 rows. Why does it matter? The question is, do we expect the “hyphen” during the stage of data cleansing? You may be thinking… it is super easy to replace “-” with zero in Power Query (by using Replace Values). In Power Query, when we define a column as Whole Number (or other numbers), non-numeric values yield to “Error”. Well, in case you really want to exclude zeros in your calculations, you may consider AVERAGEIFS: =AVERAGEIF(C2:C5, '0')Īnother hassle with hyphen as zero is especially for those who use Power Query. 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). 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. It doesn’t matter only if you do not expect further processing / calculations of your data. Note: The first, second, and third part indicates how we'd to format positive number, negative number and zero respectively.

Or apply the following custom format string if you want to display number with no decimal and the ordinary +/- sign: #,#0 -#,#0. Using Accounting format with no currency symbol: The proper way of displaying “-” for zero is to apply relevant cell format to it.Į.g. Do you input a hyphen to represent zero? If you do, please stop doing so in the future.

Office 2016 mail merge shows 0 istead of hyphenated number