Download the workbook 👉 HERE and follow along.
Custom number formatting in Excel helps you present your data clearly and effectively. Let’s explore some simple tips to save you time and avoid common mistakes.
Show Numbers in Millions with Comma
Do you want to display large numbers in millions to improve readability in Excel? Here’s how you can do it:
- Select Your Cells: Highlight the cells with the numbers you want to format.
- Open Format Cells: Press
CTRL + 1or right-click and choose “Format Cells”. - Go to Custom Format: In the “Number” tab, select “Custom”.
- Enter Format Code: In the “Type” field, enter
0,, "M"
This Excel Custom Number Format will display your numbers in millions with an “M” next to them,
Format Millions with One Decimal
If you want to show millions with one decimal place, use 0.0,, "M" instead.
Thousands Separator with No Decimals
Formatting numbers with commas and no decimals in Excel is simple. Follow these steps:
- Select Your Cells: Highlight the cells you want to format.
- Open Format Cells: Press Ctrl + 1 or right-click and choose “Format Cells”.
- Go to Number Tab: In the “Number” tab, select “Number”.
- Use 1000 Separator: Check the box for “Use 1000 Separator (,)”.
- Set Decimal Places: Change “Decimal Places” to 0.
How to Hide Zeros in Excel
To hide zero values in Excel, follow these simple steps:
- Select Your Cells: Highlight the cells where you want to hide zeros.
- Open Format Cells: Press Ctrl + 1 to open the Format Cells dialog.
- Go to Custom Category: Select “Custom” from the list.
- Enter Format Code: In the “Type” field, enter
#,##0;-#,##0;
While the zero is still in the cell, Excel will hide zero values in your selected cells.
Featured Course
Visually Effective Excel Dashboards
Understanding Excel Custom Number Format
Custom number formats in Excel have four parts, each separated by semicolons:
- Positive Numbers: Format for positive values.
- Negative Numbers: Format for negative values.
- Zero Values: Format for zeros.
- Text: Format for text entries.
How to Display Custom Messages for Zero Values in Excel
You can place almost any formatting instruction in any of the four segments in Excel.
For example, to display “zero values” in cells with a zero:
- Select Your Cells: Highlight the cells you want to format.
- Open Format Cells: Press Ctrl + 1 or right-click and choose “Format Cells”.
- Go to Custom Format: In the “Number” tab, select “Custom”.
- Enter Format Code: In the “Type” field, enter
#,##0;-#,##0;"zero values";
The cell will display “zero values” for zeros.
The formula bar will still show the number zero, and you can perform calculations like =B5 + 5, which will return 5.
=B5 + 5…and we will see the answer 5.
How to Reuse Custom Number Formats in Excel
One of Excel’s great features is the ability to reuse custom number formats. Your formats go to the Custom category’s library.
You can use them repeatedly without recreating them.
This library is also a great learning tool to see and modify your favorite formats, saving you time.
Difference Between 0 (Zero) and # (Pound, Hash)
In custom number formatting, 0 and # are placeholders:
- 0 (Zero): Displays a digit in its place, even if it’s insignificant. For example, 0.00 will display .5 as 0.50.
- # (Pound or Hash Sign): Only displays significant digits. For example, #.00 will display .5 as .50.
Pound signs are also useful for defining comma separators. For instance, #,##0 will display 1234 as 1,234 and 12 as 12.
How to Add a Leading Zero in Excel
Need to display numbers with leading zeros in Excel? Here’s how you can do it, especially useful for product codes or postal codes.
- Select Your Cells: Highlight the cells with the numbers you want to format.
- Open Format Cells: Press Ctrl + 1 or right-click and choose “Format Cells”.
- Go to Custom Format: In the “Number” tab, select “Custom”.
- Enter Format Code: In the “Type” field, enter
0000for a 4-digit number.
The result will be product codes displayed as follows.
How to Add Text to Number
Integrating text with numbers in Excel allows you to label data without losing the ability to perform calculations.
Let’s say, if a value in your list is negative, you want to add the text “(Return)”.
- Select Your Cells: Highlight the cells with the values.
- Open Format Cells: Press Ctrl + 1 or right-click and choose “Format Cells”.
- Go to Custom Format: In the “Number” tab, select “Custom”.
- Enter Format Code: In the “Type” field, enter
#;-# "(Return)".
This will display negative values with “(Return)” next to them. The numbers remain usable for calculations.
How to Hide Cells in Excel
You can hide data in Excel using custom number formatting. This method uses the four-segment codes, and leaving a segment blank hides that type of data.
Open Format Cells: Select your cells and press Ctrl + 1 or right-click and choose “Format Cells”.
Go to Custom Format: In the “Number” tab, select “Custom”.
Enter Format Code: To hide all data, enter ;;; (three semicolons) in the “Type” field.
This will hide positive numbers, negative numbers, zeros, and text, displaying nothing in the cell.
Reset the Number Formatting to Excel Default
If you want to return cells to their original format, follow these steps:
- Select Your Cells: Highlight the cells you want to reset.
- Choose General Format: Go to the Number Formatting dropdown and select “General”.
This will remove any custom formatting, displaying data as typed.
For More Information on Custom Number Formatting
Check out Michael Diamond’s detailed post with examples and explanations of custom number formatting on XelPlus.
Download the Workbook
Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master custom number formatting in Excel with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
