# Custom Excel number format

This tutorial explains the basics of the Excel number format and provides the detailed guidance to create custom formatting. You will learn how to show the required number of decimal places, change alignment or font color, display a currency symbol, round numbers by thousands, show leading zeros, and much more.

Microsoft Excel has a lot of built-in formats for number, currency, percentage, accounting, dates and times. But there are situations when you need something very specific. If none of the inbuilt Excel formats meets your needs, you can create your own number format.

Number formatting in Excel is a very powerful tool, and once you learn how to use it property, your options are almost unlimited. The aim of this tutorial is to explain the most essential aspects of Excel number format and set you on the right track to mastering custom number formatting.

## How to create a custom number format in Excel

To create a custom Excel format, open the workbook in which you want to apply and store your format, and follow these steps:

1. Select a cell for which you want to create custom formatting, and press Ctrl+1 to open the Format Cells dialog.
2. Under Category, select Custom.
3. Type the format code in the Type box.
4. Click OK to save the newly created format.

Done!

Tip. Instead of creating a custom number format from scratch, you choose a built-in Excel format close to your desired result, and customize it.

Wait, wait, but what do all those symbols in the Type box mean? And how do I put them in the right combination to display the numbers the way I want? Well, this is what the rest of this tutorial is all about :)

## Understanding Excel number format

To be able to create a custom format in Excel, it is important that you understand how Microsoft Excel sees the number format.

An Excel number format consists of 4 sections of code, separated by semicolons, in this order:

`POSITIVE; NEGATIVE; ZERO; TEXT`

Here's an example of a custom Excel format code:

1. Format for positive numbers (display 2 decimal places and a thousands separator).
2. Format for negative numbers (the same as for positive numbers, but enclosed in parenthesis).
3. Format for zeros (display dashes instead of zeros).
4. Format for text values (display text in magenta font color).

### Excel formatting rules

When creating a custom number format in Excel, please remember these rules:

1. A custom Excel number format changes only the visual representation, i.e. how a value is displayed in a cell. The underlying value stored in a cell is not changed.
2. When you are customizing a built-in Excel format, a copy of that format is created. The original number format cannot be changed or deleted.
3. Excel custom number format does not have to include all four sections.

If a custom format contains just 1 section, that format will be applied to all number types - positive, negative and zeros.

If a custom number format includes 2 sections, the first section is used for positive numbers and zeros, and the second section - for negative numbers.

A custom format is applied to text values only if it contains all four sections.

4. To apply the default Excel number format for any of the middle sections, type General instead of the corresponding format code.

For example, to display zeros as dashes and show all other values with the default formatting, use this format code: `General; -General; "-"; General`

Note. The General format included in the 2nd section of the format code does not display the minus sign, therefore we include it in the format code.
5. To hide a certain value type(s), skip the corresponding code section, and only type the ending semicolon.

For example, to hide zeros and negative values, use the following format code: `General; ; ; General`. As the result, zeros and negative value will appear only in the formula bar, but will not be visible in cells.

6. To delete a custom number format, open the Format Cells dialog, select Custom in the Category list, find the format you want to delete in the Type list, and click the Delete button.

### Digit and text placeholders

For starters, let's learn 4 basic placeholders that you can use in your custom Excel format.

 Code Description Example 0 Digit placeholder that displays insignificant zeros. #.00 - always displays 2 decimal places. If you type 5.5 in a cell, it will display as 5.50. # Digit placeholder that represents optional digits and does not display extra zeros. That is, if a number doesn't need a certain digit, it won't be displayed. #.## - displays up to 2 decimal places. If you type 5.5 in a cell, it will display as 5.5. If you type 5.555, it will display as 5.56. ? Digit placeholder that leaves a space for insignificant zeros on either side of the decimal point but doesn't display them. It is often used to align numbers in a column by decimal point. #.??? - displays a maximum of 3 decimal places and aligns numbers in a column by decimal point. @ Text placeholder 0.00; -0.00; 0; [Red]@ - applies the red font color for text values.

The following screenshot demonstrates a few number formats in action:

As you may have noticed in the above screenshot, the digit placeholders behave in the following way:

• If a number entered in a cell has more digits to the right of the decimal point than there are placeholders in the format, the number is "rounded" to as many decimal places as there are placeholders.

For example, if you type 2.25 in a cell with #.# format, the number will display as 2.3.

• All digits to the left of the decimal point are displayed regardless of the number of placeholders.

For example, if you type 202.25 in a cell with #.# format, the number will display as 202.3.

Below you will find a few more examples that will hopefully shed more light on number formatting in Excel.

 Format Description Input values Display as #.000 Always display 3 decimal places. 2 2.5 0.5556 2.000 2.500 .556 #.0# Display a minimum of 1 and a maximum of 2 decimal places. 2 2.205 0.555 2.0 2.21 .56 ???.??? Display up to 3 decimal places with aligned decimals. 22.55 2.5 2222.5555 0.55 22.55 2.5 2222.556 .55

## Excel formatting tips and guidelines

Theoretically, there are an infinite number of Excel custom number formats that you can make using a predefined set of formatting codes listed in the table below. And the following tips explain the most common and useful implementations of these format codes.

 Format Code Description General General number format # Digit placeholder that represents optional digits and does not display extra zeros. 0 Digit placeholder that displays insignificant zeros. ? Digit placeholder that leaves a space for insignificant zeros but doesn't display them. @ Text placeholder . (period) Decimal point , (comma) Thousands separator. A comma that follows a digit placeholder scales the number by a thousand. \ Displays the character that follows it. " " Display any text enclosed in double quotes. % Multiplies the numbers entered in a cell by 100 and displays the percentage sign. / Represents decimal numbers as fractions. E Scientific notation format _ (underscore) Skips the width of the next character. It's commonly used in combination with parentheses to add left and right indents, _( and _) respectively. * (asterisk) Repeats the character that follows it until the width of the cell is filled. It's often used in combination with the space character to change alignment. [] Create conditional formats.

### How to control the number of decimal places

The location of the decimal point in the number format code is represented by a period (.). The required number of decimal places is defined by zeros (0). For example:

• `0` or `#` - display the nearest integer with no decimal places.
• `0.0` or `#.0` - display 1 decimal place.
• `0.00` or `#.00` - display 2 decimal places, etc.

The difference between 0 and # in the integer part of the format code is as follows. If the format code has only pound signs (#) to the left of the decimal point, numbers less than 1 begin with a decimal point. For example, if you type 0.25 in a cell with #.00 format, the number will display as .25. If you use 0.00 format, the number will display as 0.25.

### How to show a thousands separator

To create an Excel custom number format with a thousands separator, include a comma (,) in the format code. For example:

• `#,###` - display a thousands separator and no decimal places.
• `#,##0.00` - display a thousands separator and 2 decimal places.

### Round numbers by thousand, million, etc.

As demonstrated in the previous tip, Microsoft Excel separates thousands by commas if a comma is enclosed by any digit placeholders - pound sign (#), question mark (?) or zero (0). If no digit placeholder follows a comma, it scales the number by thousand, two consecutive commas scale the number by million, and so on.

For example, if a cell format is #.00, and you type 5000 in that cell, the number 5.00 is displayed. For more examples, please see the screenshot below:

### Text and spacing in custom Excel number format

To display both text and numbers in a cell, do the following:

• To add a single character, precede that character with a backslash (\).
• To add a text string, enclose it in double quotation marks (" ").

For example, to indicate that numbers are rounded by thousands and millions, you can add \K and \M to the format codes, respectively:

• To display thousands: `#.00,\K`
• To display millions: `#.00,,\M`
Tip. To make the number format better readable, include a space between a comma and backward slash.

The following screenshot shows the above formats and a couple more variations:

And here is another example that demonstrates how to display text and numbers within a single cell. Supposing, you want to add the word "Increase" for positive numbers, and "Decrease" for negative numbers. All you have to do is include the text enclosed in double quotes in the appropriate section of your format code:

`#.00" Increase"; -#.00" Decrease"; 0`

Tip. To include a space between a number and text, type a space character after the opening or before the closing quote depending on whether the text precedes or follows the number, like in "Increase ".

In addition, the following characters can be included in Excel custom format codes without the use of backslash or quotation marks:

 Symbol Description + and - Plus and minus signs ( ) Left and right parenthesis : Colon ^ Caret ' Apostrophe { } Curly brackets < > Less-than and greater than signs = Equal sign / Forward slash ! Exclamation point & Ampersand ~ Tilde Space character

A custom Excel number format can also accept other special symbols such as currency, copyright, trademark, etc. These characters can be entered by typing their four-digit ANSI codes while holding down the ALT key. Here are some of the most useful ones:

 Symbol Code Description ™ Alt+0153 Trademark © Alt+0169 Copyright symbol ° Alt+0176 Degree symbol ± Alt+0177 Plus-Minus sign µ Alt+0181 Micro sign

For example, to display temperatures, you can use the format code #"°F" or #"°C" and the result will look similar to this:

You can also create a custom Excel format that combines some specific text and the text typed in a cell. To do this, enter the additional text enclosed in double quotes in the 4th section of the format code before or after the text placeholder (@), or both.

For example, to proceed the text typed in the cell with some other text, say "Shipped in", use the following format code:

`General; General; General; "Shipped in "@`

### Including currency symbols in a custom number format

To create a custom number format with the dollar sign (\$), simply type it in the format code where appropriate. For example, the format \$#.00 will display 5 as \$5.00.

Other currency symbols are not available on most of standard keyboards. But you can enter the popular currencies in this way:

• Turn NUM LOCK on, and
• Use the numeric keypad to type the ANSI code for the currency symbol you want to display.
 Symbol Currency Code € Euro ALT+0128 £ British Pound ALT+0163 ¥ Japanese Yen ALT+0165 ¢ Cent Sign ALT+0162

The resulting number formats may look something similar to this:

If you want to create a custom Excel format with some other currency, follow these steps:

• Open the Format Cells dialog, select Currency under Category, and choose the desired currency from the Symbol drop-down list, e.g. Russian Ruble:
• Switch to Custom category, and modify the built-in Excel format the way you want. Or, copy the currency code from the Type field, and include it in your own number format:

### How to display leading zeros with Excel custom format

If you try entering numbers 005 or 00025 in a cell with the default General format, you would notice that Microsoft Excel removes leading zeros because the number 005 is same as 5. But sometimes, we do want 005, not 5!

The simplest solution is to apply the Text format to such cells. Alternatively, you can type an apostrophe (') in front of the numbers. Either way, Excel will understand that you want any cell value to be treated as a text string. As the result, when you type 005, all leading zeros will be preserved, and the number will show up as 005.

If you want all numbers in a column to contain a certain number of digits, with leading zeros if needed, then create a custom format that includes only zeros.

As you remember, in Excel number format, 0 is the placeholder that displays insignificant zeros. So, if you need numbers consisting of 6 digits, use the following format code: 000000

And now, if you type 5 in a cell, it will appear as 000005; 50 will appear as 000050, and so on:

Tip. If you are entering phone numbers, zip codes, or social security numbers that contain leading zeros, the easiest way is to apply one of the predefined Special formats. Or, you can create the desired custom number format. For example, to properly display international seven-digit postal codes, use this format: 0000000. For social security numbers with leading zeros, apply this format: 000-00-0000.

### Percentages in Excel custom number format

To display a number as a percentage of 100, include the percent sign (%) in your number format.

For example, to display percentages as integers, use this format: #%. As the result, the number 0.25 entered in a cell will appear as 25%.

To display percentages with 2 decimal places, use this format: #.00%

To display percentages with 2 decimal places and a thousands separator, use this one: #,##.00%

### Fractions in Excel number format

Fractions are special in terms that the same number can be displayed in a variety of ways. For example, 1.25 can be shown as 1 ¼ or 5/5. Exactly which way Excel displays the fraction is determined by the format codes that you use.

For decimal numbers to appear as fractions, include forward slash (/) in your format code, and separate an integer part with a space. For example:

• `# #/#` - displays a fraction remainder with up to 1 digit.
• `# ##/##` - displays a fraction remainder with up to 2 digits.
• `# ###/###` - displays a fraction remainder with up to 3 digits.
• `###/###` - displays an improper fraction (a fraction whose numerator is larger than or equal to the denominator) with up to 3 digits.

To round fractions to a specific denominator, supply it in your number format code after the slash. For example, to display decimal numbers as eighths, use the following fixed base fraction format: # #/8

The following screenshot demonstrated the above format codes in action:

As you probably know, the predefined Excel Fraction formats align numbers by the fraction bar (/) and display the whole number at some distance from the remainder. To implement this alignment in your custom format, use the question mark placeholders (?) instead of the pound signs (#) like shown in the following screenshot:

Tip. To enter a fraction in a cell formatted as General, preface the fraction with a zero and a space. For instance, to enter 4/8 in a cell, you type 0 4/8. If you type 4/8, Excel will assume you are entering a date, and change the cell format accordingly.

### Create a custom Scientific Notation format

To display numbers in Scientific Notation format (Exponential format), include the capital letter E in your number format code. For example:

• `00E+00` - displays 1,500,500 as 1.50E+06.
• `#0.0E+0` - displays 1,500,500 as 1.5E+6
• `#E+#` - displays 1,500,500 as 2E+6

### Show negative numbers in parenthesis

At the beginning of this tutorial, we discussed the 4 code sections that make up an Excel number format: `Positive; Negative; Zero; Text`

Most of the format codes we've discussed so far contained just 1 section, meaning that the custom format is applied to all number types - positive, negative and zeros.

To make a custom format for negative numbers, you'd need to include at least 2 code sections: the first will be used for positive numbers and zeros, and the second - for negative numbers.

To show negative values in parenthesis, simply include them in the second section of your format code, for example: `#.00; (#.00)`

Tip. To line up positive and negative numbers at the decimal point, add an indent to the positive values section, e.g. `0.00_); (0.00)`

### Display zeroes as dashes or blanks

The built-in Excel Accounting format shows zeros as dashes. This can also be done in your custom Excel number format.

As you remember, the zero layout is determined by the 3rd section of the format code. So, to force zeros to appear as dashes, type "-" in that section. For example: `0.00;(0.00);"-"`

The above format code instructs Excel to display 2 decimal places for positive and negative numbers, enclose negative numbers in parenthesis, and turn zeros into dashes.

If you don't want any special formatting for positive and negative numbers, type General in the 1st and 2nd sections: `General; -General; "-"`

To turn zeroes into blanks, skip the third section in the format code, and only type the ending semicolon: `General; -General; ; General`

### Add indents with custom Excel format

If you don't want the cell contents to ride up right against the cell border, you can indent information within a cell. To add an indent, use the underscore (_) to create a space equal to the width of the character that follows it.

The commonly used indent codes are as follows:

• To indent from the left border: _(
• To indent from the right border: _)

Most often, the right indent is included in a positive number format, so that Excel leaves space for the parenthesis enclosing negative numbers.

For example, to indent positive numbers and zeros from the right and text from the left, you can use the following format code:

`0.00_);(0.00); 0_);_(@`

Or, you can add indents on both sides of the cell:

`_(0.00_);_((0.00);_(0_);_(@_)`

The indent codes move the cell data by one character width. To move values from the cell edges by more than one character width, include 2 or more consecutive indent codes in your number format. The following screenshot demonstrates indenting cell contents by 1 and 2 characters:

### Change font color with custom number format

Changing the font color for a certain value type is one of the simplest things you can do with a custom number format in Excel, which supports 8 main colors. To specify the color, just type one of the following color names in an appropriate section of your number format code.

 [Black] [Green] [White] [Blue] [Magenta] [Yellow] [Cyan] [Red]
Note. The color code must be the first item in the section.

For example, to leave the default General format for all value types, and change only the font color, use the format code similar to this:

`[Green]General;[Red]General;[Black]General;[Blue]General`

Or, combine color codes with the desired number formatting, e.g. display the currency symbol, 2 decimal places, a thousands separator, and show zeros as dashes:

`[Blue]\$#,##0.00; [Red]-\$#,##0.00; [Black]"-"; [Magenta]@`

### Repeat characters with custom format codes

To repeat a specific character in your custom Excel format so that it fills the column width, type an asterisk (*) before the character.

For example, to include enough equality signs after a number to fill the cell, use this number format: #*=

Or, you can include leading zeros by adding *0 before any number format, e.g. *0#

This formatting technique is commonly used to change cell alignment as demonstrated in the next formatting tip.

### How to change alignment in Excel with custom number format

A usual way to change alignment in Excel is using the Alignment tab on the ribbon. However, you can "hardcode" cell alignment in a custom number format if needed.

For example, to align numbers left in a cell, type an asterisk and a space after the number code, for example: "#,###* " (double quotes are used only to show that an asterisk is followed by a space, you don't need them in a real format code).

Making a step further, you could have numbers aligned left and text entries aligned right using this custom format:

`#,###* ; -#,###* ; 0* ;* @`

This method is used in the built-in Excel Accounting format . If you apply the Accounting format to some cell, then open the Format Cells dialog, switch to the Custom category and look at the Type box, you will see this format code:

`_(\$* #,##0.00_);_(\$* (#,##0.00);_(\$* "-"??_);_(@_)`

The asterisk that follows the currency sign tells Excel to repeat the subsequent space character until the width of a cell is filled. This is why the Accounting number format aligns the currency symbol to the left, number to the right, and adds as many spaces as necessary in between.

### Apply custom number formats based on conditions

To have your custom Excel format applied only if a number meets a certain condition, type the condition consisting of a comparison operator and a value, and enclose it in square brackets [].

For example, to displays numbers that are less than 10 in a red font color, and numbers that are greater than or equal to 10 in a green color, use this format code:

`[Red][<10];[Green][>=10]`

Additionally, you can specify the desired number format, e.g. show 2 decimal places:

`[Red][<10]0.00;[Green][>=10]0.00`

And here is another extremely useful, though rarely used formatting tip. If a cell displays both numbers and text, you can make a conditional format to show a noun in a singular or plural form depending on the number. For example:

`[=1]0" mile";0.##" miles"`

The above format code works as follows:

• If a cell value is equal to 1, it will display as "1 mile".
• If a cell value is greater than 1, the plural form "miles" will show up. Say, the number 3.5 will display as "3.5 miles".

Taking the example further, you can display fractions instead of decimals:

`[=1]?" mile";# ?/?" miles"`

In this case, the value 3.5 will appear as "3 1/2 miles".

Tip. To apply more sophisticated conditions, use Excel's Conditional Formatting feature, which is specially designed to handle the task.

### Dates and times formats in Excel

Excel date and times formats are a very specific case, and they have their own format codes. For the detailed information and examples, please check out the following tutorials:

Well, this is how you can change number format in Excel and create your own formatting. Finally, here's a couple of tips to quickly apply your custom formats to other cells and workbooks:

• A custom Excel format is stored in the workbook in which it is created and is not available in any other workbook. To use a custom format in a new workbook, you can save the current file as a template, and then use it as the basis for a new workbook.
• To apply a custom format to other cells in a click, save it as an Excel style - just select any cell with the required format, go to the Home tab > Styles group, and click New Cell Style….

To explore the formatting tips further, you can download a copy of the Excel Custom Number Format workbook we used in this tutorial. I thank you for reading and hope to see you again next week!

## You may also be interested in

Category: Excel Tips

### 366 responses to "Custom Excel number format"

1. Nana says:

Ok how can i customized my excel cell as in ms access. for eg. i want to customized my cell A1 in this format cell A1 list of customers (den names of customers pop up as in ms access where u can create macro for such purpose....Help

• Raghavendra says:

Convert the data to table by Selecting the data and then
Insert -> Table

2. Sandip Talware says:

Hi

Adavnce Vlookup examples are very fine, but when we are running the same formula at my system, the result get blanks. Why this is happened. I checked all the syntax however it shows blanl

• Hi Sandip,

Sorry, it's difficult to say anything without seeing your worksheets. There can be a number of reasons that cannot be detected remotely, e.g. numbers formatted as text. Usually, we publish a sample workbook containing all of the formulas discussed in a post. Have you downloaded that workbook? Do those formulas work on your side?

• Solomon Anthony Dean says:

Hey Svetlana,
i am wondering how can i fixe some of data in a cell and add only the variable data instead of doing as follow,

1300-FF-02-0001
1300-FF-02-0002
1300-FF-02-0003
1300-FF-02-0004

• J. Carroll says:

1. Highlight cells you want to format
2. Bring up cell formatting
3. In "Custom" tab enter:

"1300-FF-02-"0000

4. Click enter and voila!

All you should have to do is input...
This: To get this:
1 1300-FF-02-0001
2 1300-FF-02-0002
3 1300-FF-02-0003
4 1300-FF-02-0004
etc... etc...

If you want to add the variable data elsewhere in the text string just alter the "constants" in the formatting and enter your zeros or place holders where you want your variable data to be entered.

For example:

Formatting: Entry: Result:
0000"-FF-02-0001" 1 0001-FF-02-0001
2102 2102-FF-02-0001
267594 267594-FF-02-0001 etc...

"1300-"@"-02-0001" Q 1300-Q-02-0001
FF 1300-FF-02-0001
HEY 1300-HEY-02-0001 etc...

"1300-FF-"00"-0001" 7 1300-FF-07-0001
99 1300-FF-99-0001
1277 1300-FF-1277-0001 etc...

Just make sure that when you format the cell for numbers (using 0s) don't enter alphabetical data. And vice versa for text formatting (using @). If your cell is looking for letters and gets numbers or the other way around, the formatting won't work. you'll just see the numbers or letters you input into the cell.

Hope this helps

3. Vin says:

Is there a way to get Custom Formatting to be ALL CAPS for datetimes?

For instance, I am using the Custom Format:
ddd dd mmm yyyy hh:mm:ss AM/PM
which, for today and now would be:

Wed 19 Oct 2016 03:00:00 PM

What I want are my day of the week and month in text and in all caps just like the US DoD has on their documents (although they use 24-hour time):

WED 19 OCT 2016 03:00:00 PM

I can't apply the UPPER function which applies to text because Excel stores the date as a datetime code which is a pure number.

• Vin says:

I may have found it:

=UPPER(TEXT(MyCell,"ddd dd mmm yyyy hh:mm:ss AM/PM"))

4. Sanoj says:

How can I trim only the decimal part from my figure.
EG: If the # is 1425.50 or 1425.00 I need only the decimal like 50 or 00

• sanoj says:

I did like this but I am not getting the zero value
=IF(IFERROR(FIND(".",B129:B129),0)>0,MID(B129:B129,FIND(".",B129:B129)+1,99),"")

• Sanoj says:

• Sanoj says:

Hi Sanoj, it's a very simple solution, just need to "input" it into your formula as text formatted with two digits behind the decimal point:

Option 1 using your original function:
=IF(IFERROR(FIND(".",TEXT(B129,"0.00")),0)>0,MID(TEXT(B129,"0.00"),FIND(".",TEXT(B129,"0.00"))+1,99),"")

Option 2 which is much shorter:
=MID(TEXT(B129,"0.00"),FIND(".",TEXT(B129,"0.00"))+1,2)

These options would only treat up to two digits behind the decimal point and would trim any additional digits.

• Hillyman says:

=MOD(1435.50,1) will give you the "remainder" of the number as divided by 1 (the argument in the MOD), so you will have .50.

If you need it to be 50, then it's =MOD(1435.50,1)*100.

5. Roger says:

I work for an engineering company and the engineers requests can be quit specific. I have searched and searched to find the code to make diagonal stacked fractions that show the numerator as superscript and the denominator as subscript. Can you help with this.

6. MK says:

This has been helpful but hasn't quite hit the nail on the head for me. I have a cell that is displaying numbers through vlookup, some of the numbers are whole numbers and others have a decimal place. I would like the whole numbers to show as whole numbers without a decimal point. I also would like to show a unit with it. It seems I can do either one or the other but haven't figured out how to get both into one format. Is it possible to do that?

• MK says:

Immediately figured it out, just had to have General "unit" after the semicolon.

7. Jono says:

I have to create a custom format for all of these but I just dont understand the book? May someone please help....

1.3217874955 i need to create a custom format to look like this 321.787.4955
2. PN-25831 i need to create a custom format to look like this PN-025769
3.110 dollars and .9 cents i need to create a custom format to look like this 340 dollars and .90 cents
4. -150 150 loss its red though

8. Rob says:

How do I make it so that:
Zero = Black
Less than Zero = Black, in brackets, thousands separator, no decimals
More than Zero = Red, no brackets, thousands separator, no decimals

I think I am close but not quite:
[Red][>0]#,##0;[Black][<0](#,##0)

• AFAR says:

;General
So it becomes:
[Red][>0]#,##0;[Black][<0](#,##0);General

• Norman says:

Use this code, nice to see
[blue][>0]"J";[red][<0]"L";General
change the Font type to Wingdings

9. Jim Harris says:

How do I put commas in the numerator and/or denominator of fractions? For example, 23 3,289/4,536. I tried various combinations of #, 0, and ? but to no avail.
I tried:
# #,###/#,###
? ?,???/?,???
# #,##0/#,##0
# ?,??0/?,??0
and others.

10. Maria says:

I have a mixture of digits and numbers and would like to format cell to have leading zeros
eg. 12365L to show automatically as 00012365L

• Ryan says:

I am also seeking a solution for leading zeros with a mix of digits and numbers so I can sort by number, rather than having all text-only entries at the end. For example, I want the following data: 001, 001A, 001R, 002, 002R to sort in that order. If I format as text, my leading zeros disappear and if I format as a number using 000 as a custom format to maintain the leading zeros, the data sorts as follows: 001, 002, 001A, 001R, 002R. Any suggestions would be greatly appreciated!

• Norman says:

Press CTRL+F or Ctrl+P, then go to
number, search Custom, copy this code

00000000"L"

11. vamsi says:

Hi,

Hi How can I enter format like x.xxx-xxx.x (digits only)
For example--------------------1.234-567.8
in excel.

plz guide me on the above.

• Joseph says:

0"."000"-"000"."0

12. Udai das says:

sir, want to display A = 22° 05' 17.10 with increase A,B,C,D......
i use the custom formula is #° 00' 00.00 for 22° 05' 17.10.

13. Ali Reza Habibi says:

Hi dear Svetlana,

some times when I writ number in a cell, the number automatically changes to date.
thanks

• Norman says:

right click the cell and go to number format
choose general

14. Vincent says:

Hi

I have a column of about 2000 numbers. How do I change the number format from, say, 11709 to 1170/900?

Thanks

15. sanjiv says:

how to place thousand separator as follows:
the number is 19 lacs, 05 thousand, six hundred fortyfive and zero paisa

that should be 19,05,645.00

thankyou

• palanisamy says:

HOW TO CHANGE NUMBER FORMAT AS 12,54,780.34

16. Gloria E Greene says:

All of my excel spreadsheets seem to be corrupted. When I go to the format cell, the only option given me is negative numbers in Numbers and Currency. What happened and how do I get back to the default Format?

17. vivek says:

How to write in excel cell like password in any website login.
Want to write : 123456789
Look like : *********
Reply if any formatting available for it or any function.

• AFAR says:

Otherwise, just type
;;;*********
into the format field.

18. mitko says:

Hello all,

I want to create an Excel sheet with user names and Passwords which can be opened by multiple users. However, I do not want them to see the passwords entered there.

Example:

User ID: User 1

Instead of seeing AB12 in the cell, I would like to see XXXXXX or ****.

Is that possible?

• AFAR says:

Otherwise, just type
;;;XXXXXX
into the format field.

19. lateef says:

How can format a cells to read same digit,I be an excel sheet with numbers in four digit,now am crossing to 5 digit and keeps rearranging it bring eg 10000 before 1001

20. Vibin says:

Hi

if we enter in cell value as a 88 55 68 means Lenght width height should show in cell L88xW88xH68 any formula is there? Please help me

• AFAR says:

If they are in cells A1 B1 and C1 then ="L"&A1&"XW"&B1&"XH"&C1

21. ambikesh says:

hi,

when i am trying to make a histogram out of my data , in x- axis it is showing two values with a comma in between , i want to replace dash(-) in place of comma(,)

can anyone help?

22. Mark says:

Great job making these instructions! I easily found what I needed for a change! Kudos.

23. kishore kumar says:

hi, i want to change the thousand separator for Style 1,234,633.53. when I have tried as per the suggestions above..I can only change as 12,34,633.53.

thank you..

• palanisamy says:

HOW TO CHANGE THE NUMBER FORMAT AS 54,64,789.25

24. Maureen says:

How do I get the result of a formula to display as currency?
I have already formatted the cells as currency, they are all the same.

This is my formula. I am starting with a base number, not in a cell.
=IMSUB(390,H19)

The number in cell H19 is a currency amount, and formatted as such.
my result is 240, but it will not show up with the '\$' no matter what I try.

• AFAR says:

;;;\$@

25. SZ says:

What about if I want to a format code to do the following:

7, 0
(As many countries use commas instead of decimal points and even have a space following the comma)

Thanks!

• Joseph says:

File > Options > Advanced > Editing options > Use system operators > select "," as the Decimal separator

26. Phil says:

I have a number 1.026596 but I'm wanting to format it to read 1026.596 so what Custom format would I use??? Thanks!

• Peshiya Boy says:

Hi Phil,

I am unable to find any format to change decimal place however you can use below formula.

Imagine Your value is in A1
so paste this formula in A2 Cell.

= CONCATENATE(LEFT(A1,1),(MID(A1,3,3)&"."&(MID(A1,6,10))))

• Don says:

There are a few issues I'd have with the suggested formula:

* What happens if the length of the whole number portion is longer? This formula depends upon the decimal being the second character.

* This formula also converts the value to text. If you want to use the value elsewhere, then you have to convert it back to a number.

Just multiply the A1 value by 1000 and the decimal place will be moved.

27. Wilson,Mangaluru says:

Hi

Thanks for the page.As you said,holding ALT key and pressing ANSI code, say 0176 for degree symbol returns a error sound.any other method for format numbers with special characters? i'm using office 2010 version.

• AFAR says:

Make sure you are typing on the numpad. And make sure the numlock is on.

28. JAFFAR says:

How can I make date in
Arabic Numbers in Excel work sheet

Use this code:
[\$-2000401]0
or
[\$-3000401]0

29. sengolraj says:

How can I show the amount like following .
left aligned dollar symbol and right aligned the amount
\$ 1000.00
in a single cell in excel ?

• Hi Sengolraj,

Simply apply the Accounting format to the cell.

30. Devon says:

When rounding to zero decimals, I want 0.4 and (0.4) to display as a "-". I'm using the following Custom format:

[>=0.5]_(* #,##0_);[<-0.5]_(* (#,##0);_(* "-"_);_(@_)

The positive 0.4 now displays as a "-", but the (0.4) displays as a "- -"...

How do I get it to work for the negative?

• Don says:

This worked for me in Excel 2010.

[>=0.5]_#0_ ;[>=0]"-";#,##0

31. chael says:

Hi how do i write a custom number format for numbers like this
6.8.3.5.1
6.8.3.5
6.8.3
6.21.1.3.2

it does not want to sort it

• AFAR says:

Are those the original numbers or what they are supposed to be? Explain what they should be.

Hi I am able to hide the cell value with format cell >> Custom >> Type after adding three times semicolon but I want if any one take cursor on that cell then value should be visible. Please help.

Thanks & Regards,
Ajit

i have a coding structure and want output to be ###-###-### in the same cell
so for example:
1-234-45 >> 001-234-045
1-2-3 >> 001-002-003

can anyone help?

• AFAR says:

It's not neat, but if your three numbers are in A1:A3:
=IF(LEN(A1)>3,NA(),IF(LEN(A1)=1,"00"&A1,IF(LEN(A1)=2,"0"&A1,TEXT(A1,"@"))))&"-"&IF(LEN(B1)>3,NA(),IF(LEN(B1)=1,"00"&B1,IF(LEN(B1)=2,"0"&B1,TEXT(B1,"@"))))&"-"&IF(LEN(C1)>3,NA(),IF(LEN(C1)=1,"00"&C1,IF(LEN(C1)=2,"0"&C1,TEXT(C1,"@"))))

34. Dusty says:

I have string of text/numbers in the form "A123412123456" that I would like displayed as "A1234 12 123455" with the spacing as indicated. Can anyone help?

• AFAR says:

It is text so would be formula if original is in A1:
=LEFT(A1,5)&" "&MID(A1,6,2)&" "&RIGHT(A1,6)

35. MrJuzernejm says:

Hi everyone!

I have an issue with Fraction in Microsoft Excel 2016 when I want to calculate fraction of, an example, 0 and 7.

Excel calculate it as 0 but I want to calculate it as 0/7 so I don't need to write it manually.

Can anyone help me?

Take you a lot!

• AFAR says:

Give example of other things that work, your question doesn't make much sense alone.

36. Jim Wood says:

This is all fine, except I need a single custom format that will display a certain number of digits for both positive and negative numbers:
0 <= ABS(value) 9.99)
10 <= ABS(value) -10.0, 10.0 -> 999.9)
1000 <= ABS(value): 0 digits (e.g. -1000, 1000)

I have read several articles and I have not found how to apply a single format to accomplish the above.
Thanks,
Jim

37. Andreanne says:

Hi Svetlana,

This is a great help, thank you very much.
I followed the instructions and I have now the following format
#,##0.##;(###0.##);"-"

My problem is that when I have an integer it displays with a period

Example:

15 is formatted as 15.
14.3453453 is formatted as 14.35
-12.1 is formatted as (12.1)
0 is formatted as -

Would you be able to help with that?

Many thanks

38. Lisa Smits says:

Help please, I need to format cells, so when i enter in 12344444 it will show 1234.44.44, can this be done? Its just a reference number

• Zulqarnain says:

Try This 0000"."00"."00

39. Zulqarnain says:

40. Nick says:

I figured out how to change 131430026910035 into 13-143-0-0269-10-035 which is what I need for one spreadsheet. For another spreadsheet I need the same number but without the first 6 digits. 0269-10-035 Is there a way to do this?

41. Anastasia says:

Hi! Is it possible to change the colour of one of the labels in X axe?? They are formated as text and I need so mark one of them (as it's a special case).

42. Pawel says:

Is there a way to use value of a cell in custom formatting.

Let's say I want the number to appear with a prefix created by the user. For example:
1. Cell A1 value = "A"
2. The formatted cell has 3.01 value.
3. I would like the number to be formatted as : "A3.01"
4. If the value of A1 changes to "B", the formatted cell should display "B3.01"

This does not work : [A1]0.##,[A1]-0.##

• Hello, Pawel,

I'm afraid that it's impossible to use any formulas in custom formatting.

However, as a workaround I can advise you to try and use conditional formatting instead.
You'll be able to set the rules that will change the value in one cell depending on the other one.

43. naveen says:

I have huge data in one column in excel in custom format (like 40000.00Cr or 5000.00DR) I want to convert the Cr amounts into negative.
How can i convert?

44. Yasir says:

Thx.
Can I create no format as follows:
10,00,00,00,000 ie. last should be 000 all others 00,00,

45. Tushar says:

Please suggest which formula is required for customer formatting in excel.

I want custom formatting in text & number with general.

I want to display in excel cell like 0 - 5 KM OR else 0 - All KM

"0 - "#0" KM";"0 - "General;@" KM" this formula is correct or not please suggest.

46. Boris says:

Hi. I want to display a number, say, 20 in format "at 20°C". I make Custom Formatting as "at "#"°C", and it shows what I want if the temperature is positive. When it is negative I see "-at 20°C". Please help.

47. Paul says:

How do I format a percentage to show the “0” in front of the decimal.

Example when I type in .25% it shows up as 25.00% but when I type in 0.25% then it shows up as 0.

I’m trying to make it that if you type the “0” prior to the decimal that it will not matter either way

48. Bong says:

How can i carry the formatting of a cell in a text box? For example, i formatted a cell with red font when negative value. The color doesn't carry over to the text box if i reference it to that cell.

49. Bong says:

How can i carry over the formatting of a cell in a text box? For example, i formatted a cell with red font when it has a negative value. The color doesn't carry over to the text box if i reference it to that cell.

50. Roda says:

I have 5 cells in column:
G17 = 5
H17 = IF(G17=4,"4",0))
I17 = H17*OT_pay*1.25
J17 = IF(G17 >4,G17-4,0)
K17 = J17*OT_pay*1.5

• Sulev says:

G17=5
H17=IF(G17=4,H17*OT_pay*1.25,IF(G17>4,(G17-4)*OT_pay*1.5,0))

• Sulev says:

Sorry, should be
H17=IF(G17=4,G17*OT_pay*1.25,IF(G17>4,(G17-4)*OT_pay*1.5,0))

51. willamdavid says:

The Format Cells Excell 2010 to 0.5%

52. Hesam says:

Amazing, perfectly complete, big LIKE

53. snowburn14 says:

It looks like you've got the Excel formatting down, but the web formatting isn't cooperating: the formatting involving ???.??? doesn't actually line things up in the "Display as" boxes, though in Excel it works perfectly as described.

54. Bill says:

I noticed that excel 2016 (.xlsx) doesn't have the same complement of cell custom formats as Excel 2007 (.xls). I am using 'times' expressed in minutes, seconds & thousands of secs (i.e. mm:ss.00), Excel 2016 lets you choose (mm:ss.0). I need to save these values as a .csv file. I can modify them in the spreadsheet in the 'Type' line by adding an extra '0' digit to the format. Problem is, it's not permanent as the next time I open the .csv file in Excel, its back to the old 'mm:ss.0' format.

Any way to make that permanent ?
...Bill

55. Datta says:

Location Capacity Usage Query
Agra 392 220 IF usage is >70% of capacity background colure of value Yellow
Ahmedabad 390 324 IF usage is >80% of capacity background colure of value Red
Ambala 120 100 IF usage is >90% of capacity background colure of value Green

• Hello,

If I understand your task correctly, you need to create a rule for conditional formatting

We have an article on our blog that describes how to use conditional formatting in Excel. Please have a look at it.

Hope this information will be helpful for you.

56. Tom says:

How would I custom format the cells to get:
0=Apple
1=Oranges
2=Pears
3=Berries

It will always be an integer value between 0-3.

I'm in too deep with formulas to change the actual cell value.

Thanks!

57. BobTheProgrammer says:

Thank you! This is an excellent resource you've compiled.

58. David says:

I would like to have a number formatting that does the following:

10% would be 10%, not 10.% or 10.0%
...but 10.5% would be 10.5%
...and 105% would be 10.25
In other words, I don't want any extra 0's after the decimal point, and if there are no fractions, I don't want the decimal point at all.

Thank you!

59. sheetal says:

Thankyou sir, its very helpful for us..

60. Kaushal says:

Awesome tutorial. Makes for a great reference guide. Thank you!

61. James says:

I'm trying to use text and currency in the same cell using distributed indent. with the currency on the right. how do i get the dollar sign to show up?

ex. Supplies \$20.00

• Rod says:

These both work, but #2 is the more elegant solution.

1. "Supplies \$"General

2. "Supplies "\$#,##0

62. abolfazl says:

Thank you so much honey.

63. Rod says:

The conditional formatting is great. It took a while for me to realize that you can then no longer format negative numbers, zero, and text. Thanks for all of this! I did this: [=1000000]#,###.##,, \M_)

Example, it get's you this:

1,666 = 1.7 K
166,666 = 166.7 K
1,666,666 = 1.67 M
166,666,666 = 166.67 M
1,666,666,666 = 1,666.67 M

64. Rod says:

The conditional formatting is great. It took a while for me to realize that you can then no longer format negative numbers, zero, and text. Thanks for all of this! I did this: [=1000000]#,###.##,, \M_)

As examples, it get's you this:

1,666 = 1.7 K
166,666 = 166.7 K
1,666,666 = 1.67 M
166,666,666 = 166.67 M
1,666,666,666 = 1,666.67 M

65. Rod says:

For whatever reason, it truncated the format formula. Here it is in full:

[=1000000]#,#.##,, \M_)

66. Rod says:

Huh? OK, so let's try splitting it up (note that this commenting system also deleted the ">" in the ">=". So maybe it doesn't like the ">" operator?

The first part should read: "[=1000000]#,#.##,, \M_)"

If this doesn't work, I give up.

67. Fabrizio says:

hi i need to convet this numebr
4184
2459
4918
3164
5942
4925
1120
6804
7990
3300

in

41,84
24,59
49,18
31,64
59,42
49,25
11,20
68,04
79,90
33,00
how to do?

68. William says:

"For example, 1.25 can be shown as 1 ¼ or 5/5."

That should be 5/4.

69. Jenn says:

I am trying to use Custom format for a chart. If the data is a number that it is pulling from, then I want a number to be shown. If the data changes to a currency, I want the chart to automatically change to currency. Is there a way to do that?

70. Jenn says:

I am trying to use Custom format for a chart. If the data is a number that it is pulling from, then I want a number to be shown. If the data changes to a currency, I want the chart to automatically change to currency. Is there a way to do that?

71. Jane Anne says:

Hi!

I would like to know if there is a way to add an inch (") after the values on my dimensions?

For example:

2.75x5x.25 is my dimension, and I need it to look like 2.75"x5"x.25" on Excel.
Is there a way to do that? Any help would be greatly appreciated.

Thank you!

• Doug says:

The way to do this is to enter #?'' in the custom format list and then apply that formatting to the cell. Those are two single apostrophes after the question mark. The cell will display 2.75'', but you can use arithmetical operations on the entries.

72. Amanda says:

Hello,

Wondering if it is possible to format a cell to automatically divide whatever # is entered into the cell by 3.28?

Thanks!

73. Joel says:

Is there a custom format to always round up or down or does it have to be part of a formula?
For example...
Sometimes when rounding up I want a result or copied value of 1.1 to show as 2.
Other times when rounding down I want a result or copied value of 1.9 to show as 1.

74. Yasmin says:

I'm trying to convert from 1000000 to 1 M in office 2016 and none of the methods above are working. anyone else know a solution?

75. Doug says:

This should work:
Enter this into the Format Cell window under the Number/Custom tab 0,,\M You'll still need to enter the number as 1000000 but it will be displayed as 1M and you can use it arithmetic operations as 1000000.

76. zylstra says:

Any way to get rid of the decimal for whole numbers when aligning decimals (for mixed fractional/whole numbers) with something like the 0.?? format? In other words is there a conditional for determining whole numbers?

77. San says:

I would like to know if there is a method that when I type Up in my cell, it shows 1, and when I type Down in the cell, it shows 0.

78. chris15 says:

i wonder in the customized number format which is 0;;;@
how could i show the cell with the negative values with pharenthesis?
just wondering why? for my excel formatting project,
thank you so much

79. carolina says:

Hi,
How can I make a '0' on the left side of another digit visible in the cell? Ex. 03, 01, etc.
Thanks, Carolina

• Doug says:

Carolina:
Change the number format to custom to include the number of zeros and digits you need e.g., "0000". This will display 0123.
Select the cells you want to format right click the cell
selection
Select Format Cells
Select the Number tab
Select the Custom option
Highlight the first 0 option
In the Type field enter the total number of digits you want to display. For example, entering five zeros will allow the cells to display five digits including any leading zeros.

80. Shariful says:

I need to format numbers in excel as below:

100,00,00,000/-

How to do?

81. Sandeep says:

Hello,

How can i write in excel "5-15" whenever i write it shows May 15,

• Hi Sandeep,

Set the cell's format to Text, and then type 5-15.

82. vasu says:

Hi.. i want to convert following numbers

19,50,000 to 19.50
01,00,000 to 1.00

• Doug says:

Vasu:
I think what you want to do is display the numbers with two decimal places.
You do this by formatting the cells that contain the numbers.
Start by selecting the cells that contain these numbers then right click, choose Format Cells, then click the Numbers tab and you will see a list of formatting options. You can test each format for your particular need.
If I'm understanding your question, you want the Number format as its default display uses two decimal places.

Awesome document: clear, to the point, and the instructions work! Glad I found this website.

84. habib says:

hi
i have AMN and 0001 IN tow column in excel ,when CONCATENATE(A1,"-",B1) It shown AMN-1 ,and do not show 000.
why?
what happen this problem? and what's the solution? pleas

• Doug says:

Habib:
Is the "0001" in B1 formatted as text or a custom format "0000"?
How is the cell that displays "AMN-1" formatted?
It appears as if the formatting is different for the two cells.
Excel won't display leading zeros as in "0001" unless the value is text or the cell has the Custom Format "0000". The General formatting will work for this, too.
In your case I would assume that General formatting will work because there is the "AMN-" included with the "0001".
Right click on the cell that holds the "0001" and choose Format Cells, then choose General from the list. Do the same for the cell that holds the concatenated result. Both cells should be formatted as General.

• habib says:

Hi,
that format is custom "0000" and "AMN-1" Format Is General(
Of course he did not care),The two cell formats are the same. when click on "0001" cell shown "1" I do not know what to do. Can i send you a file?
thanks

85. habib says:

Hi,
that format is custom "0000" and "AMN-1" Format Is General(
Of course he did not care),The two cell formats are the same. when click on "0001" cell shown "1" I do not know what to do. Can i send you a file?
thanks

86. habib says:

hi
I have a problem with Access. Can anyone help?

87. داوود سالمی says:

Hi,
I enter seven,eight,or nine digits as text in a cell and I want it to be formatted as follows,Pls. help me what is the custom format code
For example:
0348971>>>B0348/97/1
23209715>>>B2320/97/15
044499565>>>B0444/98/565
What ever format I choose to change the code the excel returns back an error msgbox that ((not included,excel can't create...)).
Pls help me with the case which format code I should choose or write in the costume format...

88. داوود سالمی says:

Maybe the code is something like
"B"0000/00/
But the problem is that excel doesn't let me to change/enter the code in the costume format cell box

• habib says:

• Doug says:

I don't think its a matter of formatting the cell. As best that I can tell you can achieve the end result like this. Because there is no pattern to the text other than the number of digits I used three different formulas.
For the seven digit texts I got this to work:
="B0"&LEFT(O1,3)&"/"&MID(O1,4,2)&"/"&RIGHT(O1,1)
For the eight digit texts this works:
="B"&LEFT(O2,4)&"/"&MID(O2,3,2)&"/"&RIGHT(O2,2)
For the nine digit texts this works:
="B0"&LEFT(O3,3)&"/"&MID(O3,4,2)&"/"&RIGHT(O3,3)
I use Excel 2016, so my concatenation function might be different than yours.

89. habib says:

hi
I Have a Question About An Access File?
Who can help?

90. José says:

Hi,
How do I format a decimal number to the unit using the costume format.
I don´t want it to roud the number.
Example: The number is 3,84 and I want it to display only the number 3 (not rouning to 4)
Thank you

• Doug says:

Jose:
You can use the ROUNDDOWN function to return only the first digit like this:
=ROUNDDOWN(M40,0)
Where 3,84 is in cell M40 you want the function to return only the first digit.

91. Vítor Barreto says:

Great job! Thank you.

92. Tony says:

I have a dilemma that I can't resolve and can find no relevant examples.
I have two tables with Class mnemonics listed in the format - 00:00.00.
some are formatted as custom but with different custom formats and some are as general.
some show up as 00:00 while others might be 00:00:00.
The last 2 digits may sometimes be intentionally blank which adds to the confusion.
I need to get all these into a consistent format so I can do a VLOOKUP across the 2 tables and match data.
Also, if I try formatting to test or number I get very strange results and sometimes the format wants to make it time (complete with am or pm) aaaggghhh.
for example 28:08.92 sometimes shows up as 28:08.9. 28:08 may show up as 28:08 or 28:08:00. If it is formated as general it is 0.019548, if it is number it is 0.06, there is a time format of 28:08.9 but if I try to make a custom I get a message that says Microsoft cant use the number format I typed.
Any thoughts?

93. Praveen says:

how can i write in excel in one cell 55-10-a with data entry input 5510a
eg: 60-10-b with data entry input 6010b, means last one is letter and others are numbers.
when i gave in cell format 00"-"00"-0 its working only for numbers...if i write 55101 wil display as 55-10-1 like this...but if i give 5510a it will display same as 5510a
if any solution for this pls help me
thank you

94. Michael says:

Hi,
I have cells that contain both text and numbers such as ABC 123. Within the same column, I also have ABC 23. When sorting, 123 comes before 23.
Is there a formula to make ABC 23 ABC 023?
Thanks.

hi. if you have several rows where some rows have the same text content while others have different, how do you NOT repeat showing the row content with same value?

for example, rows 1 to 5 have ABC value and rows 6 to 10 have DEF value. how to show only ABC in row 1 and blank in remainings rows until it hits a row with a different value like DEF in which case, it should show the first occurence of DEF and blank for succeeding occurence.

i have a picture to make it clearer but how to attach here?

96. Jr says:

Hi,

How do I display this number "SUSU/067/2018" in Arabic? I've tried using "[\$-ar-SA,200]0" but it doesn't work.

Great post, there is a way to create a custom format to change numbers for letters? For example in a sheet with test results, if I type 90 or more replace the number with "A" if I type a number from 80 to 89 its returns "B" instead of 80. I already have something similar with conditional formatting @" B" and the result is for example "85 B" or "90 A", There is a way to hide this "85" and "90"?

98. Maros (SVK) says:

Hi, nice page. But I have a problem not mentioned here. :)
I want to see as many decimal numbers as there are no zeros. I use format 0,???. Works nicely. But in case there are no decimal numbers, result is shown with decimal point (comma) at the end and it looks terrible (ex. "3," instead of just "3"). Is it possible to solve it somehow easy?