Custom Number Format in Excel (13 Unique Examples)

Here’s the overview of the Format Cells dialog box, where you can use Custom Formats. Let’s showcase some examples for custom formats.

Custom number format in Excel

Download the Practice Workbook

Understanding the Basics of the Number Format

Format Code Format Represents  Explanation
#,###.00 Positive numbers Two decimal numbers and a thousand separator.
(#,###.00)  Negative numbers Two decimal numbers and a thousand separator enclosed in parentheses.
“-” Zeros Displays dash (-) instead of zero.
[Red]@ Text Displays font color as red.

How to Create a Custom Number Format in Excel

  • Select the cells you want to apply custom format and press Ctrl + 1.
  • This command will open the Format Cells dialog box.
  • Under Category in the Number group, select Custom.
  • Type the number format code in the Type field or choose a format from the list.
  • Click OK to save the format.

Custom number format in Excel

Custom Number Format in Excel: 13 Unique Examples

Example 1 – Change Decimal Places of a Number in a Custom Format

Format Code Explanation
#,###.00 Two decimal numbers and a thousand separator.
(#,###.00) Two decimal numbers and a thousand separator enclosed in parentheses.
“-” Displays dash (-) instead of zero.
[Red]@ Displays font color as red.

You can see that # and 0 return the same decimal place in a custom format. The difference between 0 and # in the integer part of an Excel format code lies in how they display numbers less than 1 when there are no non-zero digits to the left of the decimal point.

When the format code contains only pound signs (#) to the left of the decimal point: If you enter a number less than 1, it will be displayed starting only with a decimal point.

For example, if you type 0.5 in a cell with a format code like #.00, the number will be displayed as .5. The absence of a leading zero before the decimal point indicates values less than 1.

When the format code contains only zero (0) to the left of the decimal point: If you enter a number less than 1, it will be displayed with a leading zero before the decimal point.

For instance, using a format code like 0.00, if you enter 0.5, the number will be displayed as 0.5. The leading zero signifies that values less than 1 should have a zero digit before the decimal point.

Decimal places in custom number format

Example 2 – Display the Thousand Separator in a Number

As Excel indicates the thousands separators with commas, you have to use commas to insert a thousand separators in a custom format.

By using the custom number format with a comma as the thousands separator, you can enhance the readability of large numbers in your Excel spreadsheet.

Symbol Display
#,### thousands separator without decimal places.
#,##0.00 thousands separator and two decimal places.

Thousands separator in number with custom format

Example 3 – Convert the Number Format to a Round Figure

Converting a number to a rounded figure using a custom number format in Excel involves creating a format code that displays the number in a rounded format without showing the decimal places.

When you use a custom number format and include a comma (,) between any digit placeholders like #, ?, or 0, it’s a signal to Excel to insert a comma as a thousand separator in the displayed number.

However, if the comma is used without any digit placeholders immediately following it, Excel interprets this as scaling the number by a certain factor:

  • One comma scales the number by a thousand.
  • Two consecutive commas (,,) scale the number by a million.

For a better illustration, follow the image below.

Format number to round figure

Remember that this formatting is purely visual and doesn’t affect the actual value of the cell. If you need to perform calculations using the rounded value, you might want to use the ROUND function in a separate cell to achieve the rounding operation and then apply the custom format for display purposes.

Example 4 – Control Text and Spacing in Custom Format

You can use custom number formats in Excel to include text and spacing alongside numeric values. This can be particularly useful for adding context or labels to your numbers.

Here, we have used actually two types of format.

  • For thousand: #.00,\K or #,###.0,\K
  • For million: #.00,,\M or #,###.0000,,\M

Control text and spacing in number

Example 5 – Control Zeroes in Number Format

Controlling zeros in number formats in Excel is crucial for displaying numbers exactly as you need them. Here’s how you can achieve different zero-related formats using custom number formats.

Case 5.1 – Show Leading Zeroes

If you try to enter some number in Excel containing leading zeros, you will see that Excel removes the leading zeroes and keeps only the non-zero values.

However, there are several methods to keep the leading zeroes in Excel.

If you want to keep the leading zeros in a number with 5 digits, use the format: 00000

Show leading zeros in number with custom format

Case 5.2 – Format a Zero as Dash or Blank

The accounting number format shows a zero as a dash in Excel by default. But you can also perform this task by using a custom number format in Excel. Amazing, isn’t it?

To show a zero as a dash, type “-” in the third part of the custom format: General; -General; “-“

If you want to show zero as blank, then use this format: General; -General; ; General

Format zero as dash or blank in a number

Example 6 – Show the Currency Symbol in Custom Format

There are several ways to add currency symbols in Excel. You can insert the available symbols in Excel in the custom format.

Showing currency symbol in custom number format

But if you want to insert any type of currency format from the Format Cells dialog box:

  • Launch the Format Cells dialog box and go to the Currency group, then choose a symbol (i.e. ₽ Tatar).

Currency symbol

  • Go to the Custom group and edit the custom format according to your needs.

Change currency symbol in custom format

Example 7 – Insert Percentages in a Custom Number Format

If you want to convert a number to a percentage or show a number as a percentage of 100 in a custom number format, you have to insert the percentage symbol (%).

Symbol Display
#% Without decimal place
#.00% Two decimal places
#,##.00% Thousands separator with two decimal places.

Percentage in custom format

Example 8 – Fraction in a Number Format

Symbol Display
# #/# remainder with up to a single digit.
# ##/## remainder with up to two digits
# ###/### remainder with up to three digits
###/### improper fraction where the numerator is equal to or greater than the denominator, presented with a maximum of three digits.

The image below illustrates the symbols.

Fraction in custom format

Example 9 – Display Negative Numbers in Parentheses

To create a customized format for negative numbers, you should incorporate a minimum of two code sections: the initial section applies to positive numbers and zeros, while the subsequent section is designated for negative numbers.

  • Insert this format code within the second section: #.00; (#.00).

Formatting for negative number

Example 10 – Create a Scientific Format for a Number

The image below describes how to use a scientific notation format for numbers.

Custom scientific format

Example 11 – Change the Font Color in a Custom Number Format

  • Enclose the color name with the [] brackets and apply other necessary modifications for formatting.

Changing font color in custom format

Example 12 – Repeat Characters in a Custom Format

  • To extend a certain character across the width of a column using a custom Excel format, place an asterisk (*) before the character.
  • To introduce leading zeros, include *0 before a numerical format.

Repeating characters

Example 13 – Apply a Custom Format to Change the Alignment of Number

Symbol Display
#,###*  (a space after the asterisk) Alignment: left
#,###* ; -#,###* ; 0* ;* @ Numbers: left-aligned ; Texts: right-aligned

Changing alignment

Frequently Asked Questions

Does Custom Formatting Affect Calculation Results?

No, custom number formatting only affects the appearance of numbers in cells, not their actual values. Formulas and calculations remain unaffected by the formatting.

How can we highlight the Custom Number Format in Excel?

We can highlight Custom Number Format in Excel by using Conditional Formatting and Format Painter.

Are Custom Formats Portable to Other Excel Files?  

Yes, custom formats are saved with the workbook and can be applied to other cells within the same workbook. To apply them to other workbooks, you might need to recreate the custom formats.

Key Takeaways from Article

  • Custom number format in Excel allows you to control how numbers are displayed in cells without changing their actual values.
  • Format codes are the building blocks of custom number formatting. They consist of placeholders, symbols, and special characters that define how numbers appear.
  • You can adjust decimal places using placeholders like 0 and #. Using 0 displays zeroes, while # only shows necessary digits.
  • If you want to use the same formats in other workbooks, you’ll need to recreate them.

Custom Number Format in Excel: Knowledge Hub

<< Go Back to Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rafiul Hasan
Rafiul Hasan

Rafiul Hasan, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering & Technology, contributes significantly to the ExcelDemy project with almost 1.6 years of dedicated work. Currently an Excel and VBA Content Developer, he has a passion for problem-solving. Authoring over 100 articles for ExcelDemy showcases expertise in Microsoft Office Suites and Data Analysis. In addition to content development, Rafiul actively engages with the ExcelDemy forum, offering valuable solutions to user queries and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF