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.

Download the Practice Workbook

Understanding the Basics of the Number Format

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: 13 Unique Examples

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

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.

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.

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.

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

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

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

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.

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).

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

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 (%).

Example 8 – Fraction in a Number Format

The image below illustrates the symbols.

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).

Example 10 – Create a Scientific Format for a Number

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

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.

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.

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

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