

Excel provides many default number formats. But often, these formats are not enough. For example:
Example: The default and a modifyed number format.
In such case, you need to create a custom number format. In this article, you learn everything you need to know. For making it easier for you, please feel free to download the example Excel file or the handy custom format card for printing it out.
Contents
Insert a custom number format by pressing Ctrl + 1 on the keyboard and type the code in the text field of the “Custom” category.
Inserting a custom number format in Excel is very easy:
Recommendation: Select a number format which is very close to your desired format first (number (0) on the screenshot). That way you only have to do some minor changes and don’t have to create the whole format from the scratch.
If you create a custom number format, you have to regard some rules. A custom number format can be only a few characters or a long text string. They have at least one and at most four parts. Here are the four parts:
Structure of custom number format codes in Excel
Let’s go through the 4 sections:
Hold on a second. Was this information helpful so far?
...or on other networks!
Twitter: Follow @professorexcel
Facebook:
Sign | Description | Code | Before | After |
# | Add a # for a normal number. The digit won’t be displayed, if there is a preceding zero. | #,##0 | 123 1234 1234.5 0.123 | 123 1,234 1,235 0 |
0 (zero) | Add a 0 (zero) if there should for sure be a digit displayed, even if it’s a zero. | 00000 | 123 | 00123 |
% | Add the percentage sign % if you want to display a number as percentage. | 0% | 0.8 | 80% |
Don’t use any sign if you want to hide number. Please note: You need at least one semicolon, otherwise Excel will interpret it as “General” | ; | 123 | ||
, | Insert a thousands separator. You can use this for showing numbers in thousands or millions as well (scroll down for more information). | #,##0 | 1234 | 1,234 |
. | Insert a decimal point in a number. | 0.0 | 123 | 123.0 |
[Red] | Uses red color. Please note: Conditional formatting rules offer much more color options. | [Red] | 123 | 123 |
? | If you want to align the decimal points, use the question mark. Question marks can also be used for fractions. | ???.??? 0 ???/??? | 12.345 1234.5 12.34 | 12.345 1234.5 12 17/50 |
“abc” | Within quotation marks, you can add any text. | “USD “0.0 | 123.45 | USD 123.5 |
@ | The @ sign is only used in the fourth section of the format code. Use it, if you want to display text in a cell. If you don’t use it (but still have 3 semicolons and therefore 4 sections), text will be hidden. | @”!” | Some text | Some text! |
Example: You want to create this number format
There are 3 steps for creating your own custom number format. The idea is to do it as simple as possible and therefore utilize built-in features. The 3 steps are shown with the example on the right-hand side. You want to create a custom number format, which
First step: Create a number format with the “Format Cells” window.
As the first step, choose a built-in number format which is very close to the one you eventually want to create.
If you open the “Format Cells” window again and click on “Custom” on the left-hand side, you’ll see the format code. In this example it should be #,##0.00 .
In this example, you want to further modify the number format for negative values, zeros and text. You continue with the code #,##0.00 created in step 1 above. If there is just one section, the format code is used for all kinds of values: For positive values, negative values, zeros and text because there is no particular definition for negative numbers, zeros and text.
Fine-tunee the number of decimals
In the last step, you can further fine-tune the number of decimals. Therefore, use the buttons “Increase Decimal” or “Decrease Decimal” in the center of the “Home” ribbon.
You don’t want to create your own code but just copy & paste one of the most popular custom number formats? Here are the most frequently asked codes.
The easiest way: Add a comma “.” to your code.
If you got this code #,##0 just add a comma “,” and you got #,##0, . That way, 2,345 becomes 2.
For more information about thousands and millions, please refer to this article.
#,##0%
If you want to hide zeros with a custom number format, make sure to use a third part of the number format and leave it blank. Some examples:
Excel too slow? Speed it up. Get the book now!
Tired of waiting for Excel? Use the 30 best methods described in this book to speed up Excel calculations!
Learn more or get it on Amazon!
Download this format code sheet for free
As a gift for you, please feel free to download these files:
Was the information in this article helpful? Why don’t you sign up for our free, monthly Excel newsletter?
Save your favorite number format and apply it with one click
With our Excel add-in “Professor Excel Tools”, you can create your favorite number format. Even better, you can apply it with just one click. Why don’t you give it a try?
This function is included in our Excel Add-In 'Professor Excel Tools'
Learn more Download Free Trial
(No sign-up, download starts directly)
More than 10k people on Facebook can't be wrong.
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Subscribe to our popular Excel newsletter! You get all this:
Your welcome gift: Our big 45 pages keyboard shortcuts package.
Subscribe now! In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Please use this download link to download the Currency Converter Excel add-in!
Please use this download link to download the comment extraction example: http://bit.ly/Extract_Comment_Example