Excel’s default number formats often don’t show data exactly as you need. You might want to display a fixed number of decimal places, hide insignificant zeros, or add consistent leading zeros to codes. The custom number format feature gives you this control. It uses special codes like the # and 0 symbols to define how digits appear. This article explains the rules for these placeholders and provides steps to build your own formats.
Key Takeaways: Controlling Digit Display with # and 0
- 0 digit placeholder: Forces a digit to display in that position, adding a leading or trailing zero if the number lacks a digit there.
- # digit placeholder: Displays a digit only if the number has a significant digit in that position, otherwise it shows nothing.
- Format Cells dialog (Ctrl+1): The primary location to create and apply custom number formats to your selected cells.
Understanding the # and 0 Placeholder Codes
A custom number format in Excel is a code with up to four sections separated by semicolons. These sections define formats for positive numbers, negative numbers, zero values, and text, in that order. The # and 0 symbols are the core digit placeholders used within these sections. Their behavior is precise and consistent.
The 0 placeholder is a mandatory digit spot. If the number you are formatting has a digit for that decimal place, it shows that digit. If the number is smaller and does not have a digit for that place, Excel displays a zero instead. For example, the format 0.00 will show the number 5 as 5.00, adding two trailing zeros. The format 00000 will show the number 123 as 00123, adding two leading zeros.
The # placeholder is an optional digit spot. It displays a digit only if the number has a significant digit for that place. If the number lacks a digit there, Excel displays nothing, not even a zero. The format #.## will show 5.5 as 5.5, 5 as 5, and 5.0 also as 5. It suppresses insignificant zeros. You often combine # and 0 in one format. A common pattern is #,##0.00, which uses # to suppress unwanted thousands separators at the far left, 0 to ensure at least one digit appears before the decimal, and 0.00 to force two decimal places.
Steps to Create Custom Formats with # and 0
- Select your cells
Click and drag to select the cell or range of cells you want to format. - Open the Format Cells dialog
Press Ctrl+1 on your keyboard. Alternatively, right-click the selected cells and choose Format Cells from the context menu. - Navigate to the Custom category
In the Format Cells dialog box, click the Number tab. In the Category list on the left, select Custom. - Enter your custom format code
In the Type field at the right, you will see the current format of the cell. Click in this field and type your new format using # and 0 symbols. The Sample area above will show a preview. For example, type #,##0.00 to format with a thousands separator and two fixed decimals. - Apply the format
Click the OK button to apply the new custom format to your selected cells. The display will change immediately, but the underlying cell value remains unchanged.
Method for Adding Leading Zeros to Codes
- Select the data range
Highlight the cells containing product codes, IDs, or numbers that need a fixed length. - Open the Format Cells dialog (Ctrl+1)
Go to the Number tab and select the Custom category. - Use the 0 placeholder for total length
In the Type field, enter a series of zeros equal to the total digits you want. To format a number as a 5-digit code, type 00000. The number 42 will display as 00042.
Common Mistakes and Formatting Limitations
Numbers Appear as Asterisks (*****)
This happens when the column is too narrow to display the formatted number. Excel shows asterisks instead of truncating your data. The fix is to double-click the right border of the column header to auto-fit the width, or manually drag the border to make it wider.
Format Shows Too Many Decimal Places
If you use a format with many 0 placeholders, like 0.0000, Excel will always show that many decimals. To show decimals only when needed, replace the trailing zeros with # symbols. Change 0.0000 to 0.####. This will show up to four decimals but suppress trailing zeros.
Leading Zeros Disappear When Saving to CSV
Custom formats only change a cell’s visual display. When you save the workbook as a CSV file, Excel saves the underlying raw cell value, not the formatted display. To preserve leading zeros in a CSV, you must convert the numbers to text first. Pre-format the cells as Text, or type an apostrophe before the number.
Comparison of # and 0 Placeholder Behaviors
| Item | 0 (Zero Placeholder) | # (Hash Placeholder) |
|---|---|---|
| Primary Function | Forces a digit to display | Displays digit only if significant |
| If number digit is missing | Shows a zero | Shows nothing (space) |
| Common use case | Fixed decimal places, leading zeros for codes | Suppressing insignificant leading/trailing zeros |
| Example format | 000-00-0000 | #.## |
| Value 5.2 displays as | 005.20 (with format 000.00) | 5.2 (with format #.##) |
You can now build custom number formats to display data exactly as required. Use the 0 placeholder for fixed-length codes and mandatory decimals. Use the # placeholder to create cleaner numbers by hiding unnecessary zeros. For more control, explore adding text and conditions to your format codes in the Custom category. A useful advanced tip is to use the format #,##0.0_);(#,##0.0) to align positive and negative numbers on the decimal point when parentheses are used for negatives.