Scenario #1 – Thousands Separator with Zero-Decimal Precision
In the below dataset, we are asked by our boss to format the numbers with a thousands separator (comma), a decimal point (period), and set the decimal precision to zero-decimal places.
This number format is achieved by selecting cell the data cells and pressing CTRL-1 on your keyboard (or right-click “Format Cells…”) to access the Format Cells dialog box.
From here, we will select the Number tab at the top and then select the Category: Number on the left and enable the “Use 1000 Separator (,)” option and set the Decimal Places: option to 0 (zero).
Scenario #2 – Hide the Zeros
After seeing the result, your boss now wants to hide the zeroes.
This is where developing our own custom number formatting comes into the picture.
Start by pressing CTRL-1 on your keyboard (or right-click “Format Cells…”) to access the Format Cells dialog box.
From here, we will select the Number tab at the top and then select the Category: Custom on the left.
We can see the format codes for the original thousands separator format we first applied.
#,##0
We will update the codes in the “Type:” field to read:
#,##0;-#,##0;Our zero values are now gone.
Before we break down the logic of the custom number format, we need to ask ourselves, “Are the zero values really gone?”
If we select an “empty” cell where a zero once was, we can see in the Formula Bar that the zero is still in the cell.
The key to remember about number formatting is that the formatting never actually changes the data; it merely alters the way it is presented to the user. (There are exceptions to this, such as the Percent Style.)
The number remains as it was typed. Think of number formatting as a costume that allows the number to pretend to be something different than what it is.
In this case, the custom rule hides the zeroes.
The Custom Number Formatting Rule
Number formats are comprised of positive/negative numbers, zero values and text strings.
Excel can’t determine in advance if your cell entry is a number, a zero or a text entry, so Excel has compensated for this unknown by creating a flexible number format structure.
This structure (often called a numeric code or a number format) is comprised of 4 parts – segment 1; segment 2; segment 3; segment 4.
Each of these sections (i.e. “segments”) are separated by semicolons.
This “segmented” structure allows the user to control a cell’s “character format” appearance. The structure looks like this:
- Segment 1: formats positive numbers (values)
- Segment 2: formats negative numbers (values)
- Segment 3: formats 0 (zero) entries
- Segment 4: formats text entries (letters or letter/number combinations)
Cell values are “customized” by using a formatting symbol(s) in either 1, 2, 3, or all 4 format segments.
The Interchanging of Format Ideas
We can place almost any formatting instruction in any of the 4 segments.
Suppose we wish to display the message “zero values” in a cell containing a zero. Consider the following custom number format.
#,##0;-#,##0;”zero values”;
The result will be as follows.
Notice that the Formula Bar sees a zero in the cell, but the user sees the text “zero values”.
We can even perform arithmetic operations on the cell, such as…
=B5 + 5…and we will see the answer 5.
Reusing Custom Number Formats
One of the great features offered when creating custom number formats is that each format is added to the Custom category’s library.
This means you can use the format repeatedly without having to recreate the format each time you put it to use.
The library is also a great resource to consult to see how many of your favorite formats were created. You can use this code as a learning tool or change the code directly. This saves time when creating a new custom number format that is like an existing format.
Difference Between 0 (zero) and #
There are many symbols in the custom number library, but two of the symbols will be used more than most others; the 0 (zero) and the # (pound sign; or as the kids call it, the hashtag.)
Both symbols are placeholders. The difference is that the # is a variable placeholder, while the 0 is a fixed placeholder.
Zeroes tell the system that a digit must be displayed in this place position regardless of the significant/insignificant status. In other words, if the format code…
0.00…is defined, and the value in the cell is .5 (one-half), the system will display 0.50.
If the format code were…
#.00…and the value in the cell is .5, the system will display .50.
Pound signs are useful to define the position to display comma separators if needed but hidden if not.
#,##0In the above example, the number “1234” would be displayed as “1,234”, where the number “12” would be displayed as “12”.
Preserving Leading Zeroes
One of the most common uses of custom number formatting is to preserve leading zeroes.
Any number, such as a product code number or postal code, that is required to display a specific number of digits, regardless of weather all the digit positions are used, will benefit from a custom format.
Consider the following list of product codes.
If we need to display each number as a 4-digit number, we can apply the following custom number format to the cells containing those numbers.
0000 (4 zeroes)
The result will be product codes displayed as follows.
Adding Text to Numbers
A very creative way to integrate text into values without losing the ability to perform arithmetic operations on the values, is to concatenate forced text to the number format code.
Suppose we wish to indicate that an entry is a product return if the value is negative.
Instead of placing the text label in an adjacent column to indicate a return, we can place the text within the same cell as the negative value.
In this example, we will keep the custom format as simple as possible: select the cells containing values and enter the following custom number format.
#;-# “(Return)”
The result will be sales displayed as follows.
Even though we have text displayed in the cell, the number is the only thing in the cell. This means we can still perform arithmetic operations on the cell.
A Common Number/Text Mistake
A common mistake made when integrating text with numbers is to concatenate text to the answer of a formula.
=SUM(A4:A8) & “ Total”
Although this produces the desired result, the answer is now seen as text to other formulas. Further calculations which include the total are no longer possible.
If we attempt to calculate a 10% tax on the total in cell A9, we are presented with a #VALUE error message.
The proper application of a custom number format (see the “Adding Text to Numbers” section above) will allow further calculations.
# “ Total”,-# “ Total”
The Secret to Hiding Data
One of the quirks to the 4-segment custom number codes is that if you leave out codes for a specific segment, that segment type of data will not be displayed.
It’s as if you purposely told Excel to not display a value if that value is of a certain segment type.
When we used the code…
#,##0;-#,##0;…the trailing semi-colon tells Excel to format zeroes with the following codes, but we failed to supply any codes. Thus, zeroes are hidden.
Imagine if we formatted a cell with the following codes.
;;; (three semi-colons)
We are instructing Excel to format positive numbers, negative numbers, zeroes, and text with the following codes, but we don’t give Excel any codes to work with.
Excel’s reaction to this is to show nothing for these four types of data. The result is a cell where nothing will be displayed.
Resetting the Number Formatting to the Excel Default
If you wish to return the cells back to their original formatting behavior, select the cells and then select General from the Number Formatting dropdown list.
The result will be that any type data will now appear as typed, with no formatting applied.
For More Information on Custom Number Formatting
In a previous post written by guest contributor Michael Diamond, Michael gives details and many examples on custom number formatting.
https://www.xelplus.com/excel-custom-number-formatting_1/
Michael’s post explains all the symbols and gives examples for each of their uses.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
