Excel 2019: Format as a Façade
November 14, 2019 - by Bill Jelen
Excel is amazing at storing one number and presenting another number. Choose any cell and select Currency format. Excel adds a dollar sign and a comma and presents the number, rounded to two decimal places. In the figure below, cell D2 actually contains 6.42452514. Thankfully, the built-in custom number format presents the results in an easy-to-read format.
The custom number format code in D2 is $#,##0.00. In this code, 0s are required digits. Any #s are optional digits.
However, formatting codes can be far more complex. The code above has one format. That format is applied to every value in the cell. If you provide a code with two formats, the first format is for non-negative numbers, and the second format is for negative numbers. You separate the formats with semicolons. If you provide a code with three formats, the first is for positive, then negative, then zero. If you provide a code with four formats, they are used for positive, negative, zero, and text.
