Custom Number Format in Excel (13 Unique Examples)

In this article, you will learn how to create a custom number format in Excel with proper illustrations and examples.

Custom number formats in Excel provide a powerful way to control how numbers are displayed in cells, allowing you to tailor the appearance of numerical data to suit specific needs and enhance data interpretation.

While Excel offers various built-in number formats, there are situations where these standard formats might not fully address your requirements. This is where custom number formats come into play. It offers unparalleled flexibility and customization in Excel.

Let’s explore some compelling use cases that highlight the importance of using custom number format in Excel.

Download Practice Workbook

You can download the practice book from the link below.

Understanding Basics of Number Format

Before diving into custom number formats, it’s essential to grasp the basics of Excel’s built-in number formatting options. These include options for decimal places, thousands separators, currency symbols, and percentage signs.

Excel provides various predefined number formats covering the most common use cases. However, there are times when these defaults fall short of representing data precisely as needed.

How to Create Custom Number Format in Excel

To create a custom number format in Excel:

• Select the cells you want to apply custom format > press CTRL+1.
• This command will open the Format Cells dialog box.
• Under the 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.

In each of our corresponding examples, we will launch the Format Cells dialog box and apply the number format in the Type field of the Custom group in this way.

Custom Number Format in Excel: 13 Unique Examples

Understanding and initializing the power of custom number format can elevate your Excel skills to the next level. Whether you’re a financial analyst, a project manager, or a student crunching numbers for a research paper, this skill is a fundamental demand for either a beginner or an expert of Excel. Let’s illustrate the custom number format in Excel with 13 unique examples.

1. Change Decimal Places of Number in Custom Format

Controlling the number of decimal places in Excel using a custom number format allows you to precisely format numeric values according to your needs.

Excel provides various predefined number formats covering the most common use cases. However, there are times when these defaults fall short of representing data precisely as needed.

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.

2. Display Thousand Separator in 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.

3. Convert Number Format to 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.

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

5. Control Zeros 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.

5.1. Show Leading Zeros

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

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

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

5.2. Format Zero as Dash or Blank

The accounting number format shows 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 zero as a dash, type “-” in the third part of the custom format: General; -General; “-“

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

6. Show Currency Symbol in Custom Format

All currency symbols are not available on most of the keyboards. 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 > go to the Currency group > choose a symbol (i.e. ₽ Tatar).

• Then go to the Custom group and edit the custom format according to your need.

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

8. Fraction in Number Format

A fraction is a mathematical representation of a part of a whole or a division of quantities. It consists of two numbers separated by a horizontal line (known as the fraction bar or division bar).

You convert decimal to fraction or write fraction in Excel.

The image below illustrates the symbols.

9. Display Negative Number

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.

To exhibit negative values enclosed in parentheses, insert this format code within the second section: #.00; (#.00).

10. Create Scientific Format for Number

Sometimes you may want to create a custom scientific format for numbers. The image below describes how to use a scientific notation format for numbers.

11. Change Font Color in Custom Number Format

In Excel, you can change the font color using a custom number format by including color codes within the format code.

The task is quite simple. Just mention the color and enclose it with [] bracket and do the necessary modification for applying any formatting.

12. Repeat Characters in Custom Format

To extend a certain character across the width of a column using a custom Excel format, place an asterisk (*) before the character.

And to introduce leading zeros, include *0 before a numerical format.

Notice the image below for details.

13. Apply Custom Format to Change Alignment of Number

The usual way to change alignment in Excel is to use the Alignment group in the Home tab. However, you can use a custom format for changing alignment.

Frequently Asked Questions

1. Does Custom Formatting Affect Calculation Results?

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

2. How can we highlight the Custom Number Format in Excel?

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

3. Are Custom Formats Portable to Other Excel Files?  

Ans: 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 zeros, while # only shows necessary digits.
• If you want to use the same formats in other workbooks, you’ll need to recreate them.

Conclusion

Throughout the article, we have tried to illustrate custom number format in Excel with different examples. Mastering custom number formatting can significantly enhance the visual presentation of your data to easily interpret and analyze.

By combining your understanding of format codes with creative customization, you can create professional and visually appealing spreadsheets tailored to your specific needs.

Custom Number Format in Excel: Knowledge Hub

<< Go Back to Number Format | Learn Excel