# 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. Kevin says:

I'm trying to convert a number, say 123.50 to 000012350 (always 10 characters, need pennies but no decimal point.

Thanks

• Hi Kevin,

Assuming the original number is in A1, the conversion can be performed with this formula:
=TEXT(A1*100, "0000000000")

However, the result of the formula will be a numeric string, not a number.

If the result should be number, you can multiply the original numbers by 100 to get rid of the decimal point (=A1*100), replace formulas with values if needed (an intermediate result will be 12350), and then apply this custom format: 0000000000 to always display 10 characters with the required number of leading zeros.

2. c.aNUP kUMAR says:

I NEED 4556 A,4557 B,4558 C,4559 D ,,,,,,,,,, serial numbers with 6 rows and 6 columns formula send me plz

• instead of using conditional formatting in excel you can use a simple formula . 1&char(64+Rows(a\$1:a1)) will give you 1A try this says:

instead of using conditional formatting in excel you can use a simple formula . 1&char(64+Rows(a\$1:a1)) will give you 1A try this

3. Rob Lattimore says:

Hi Svetlana,

I'm having trouble formatting text and would like to omit the first two letters. I'm aware that I could use RIGHT(CELL,LEN(CELL)-2) but I don't want to change the actual text, just to display it in a different way, with the underlying 'value' of the cell staying the same.

For example, I can type in the custom formatting bar "Rob" to display only "Rob" no matter what I actually type into the cell.
I would like to format cells so that the first two letters aren't displayed, for example, entering "Steve" would show "eve" in the cell, or "Howard" showing "ward". (Sounds weird but I do genuinely need this.)

Is this possible?

Thank you for the fantastic article, it was very helpful and well written.

Rob

4. jamael says:

I'm trying to round the number 1,230 to 1,200; or 43,540 to 43,500. Thanks.

5. Kaveh says:

I need to convert a 00000 type formatted cell content (shows as 00123) to text with same number of leading zeros. Since Excel understands the value only as 123 converting the cell format to text removes the zeros. Thanks in advance.

6. Ed Medina says:

Super useful. thanks

7. Demi says:

I am trying to format my phone numbers to look like this: 303.555.9876, but instead they look like this:3035559876..

I will like to know how to fix this problem.

• Daniel says:

This works for me.
[<=9999999]###"."####;###"."###"."####

8. RAMJHI says:

I NEED TO CONVERT ALL THE NUMBERS THAT I ENTER SHOULD BE IN LAKHS.

hello

how to format a cell like: AAAA12345-6?

10. Dana says:

Hi Svetlana,

Trying to use Custom Format to color numbers in a cell based on the these conditions: green for 45 but =60.

Any suggestions?

11. Dana says:

Hi Svetlana,

Trying to use Custom Format to color numbers in a cell based on the following conditions: green for numbers less than or equal to 45, amber for numbers greater than 45 but less than 60, and red for numbers 60 and greater.

Any suggestions?

12. Brittany Pitruniak says:

If I am trying to format a phone number to read (so zeros need to be visible):
(XXX) XXX XXXX
and
1 (XXX) XXX XXXX

I cannot find the code to use a comma...

13. kaish says:

I want convert this text 20122018(ddmmyyyy) to this date dd-mm-yyyy format please how we will convert using text formula

14. Yaseen says:

Hi,
could you please help me with the date format, I want space between days and months numbers like dd/mm/yyyy to d d/m m/yyyy.(2 2/1 1/2018)

Thanks

15. mehboob says:

i like it

16. Shaina says:

We get a list of numbers in that actually start with a letter (Example L18-021547) and I would like to figure out how to apply a format to those numbers that adds an asterisk (*) to the beginning and the end of that without having to add them to every single field manually (*L18-021547*). We get a bundle of 100+ of these numbers for each report and I am just trying to come up with a way for it to see the field as a number, which it is not doing at the moment because it starts with "L". Any suggests?

• Dennis says:

try using this formating in cell format

"*L"00-000000"*"

each time you just need to type out the number and it will add the * and also the L for you.

17. Doug says:

Shaina:
Your request is somewhat confusing because Excel is not going to see "L18-021547" as a number. Then you say you're trying to come up with a way to see the field as a number with an asterisk at the beginning and end of the text.
=CONCATENATE("*",A2,"*") will add an asterisk to the front-end and back-end but it will still be text. Excel is not able to convert the letter "L" and the "-" into a number.
Why do you need the data as a number?

18. Dennis says:

#.##% = 1.%
genaral% = 100%

can i know how to remove the decimal point? or did i use the wrong code for it?

i want it to have the expending function like how the general give where if you don't have decimal it don't show but if you have then only it shows.

19. Francois says:

Svetlana, you are a genius! I've been looking everywhere online for the list of font colors that can be used with custom number format" and only found it on your site (It's 'Magenta', not 'Pink', doh!)
Thank you very much for you help, and Merry Christmas from New Zealand :-)

20. Mohammed says:

Hi guys, I need to make a custom currency format same as accounting one, but I need it in ARABIC figures with ARABIC currency symbol "ج.م.". the numbers & symbol should be aligned same as accounting excel format.

• Hi, Mohammed,

Simply select your data, press Ctrl+1, and go to the Accounting category in the dialogue window that appears. You will see a drop-down menu for Symbols. Pick Arabic (Egypt) from the list and hit OK to save changes.

21. Ahmed says:

I’m trying to make the 5th digit (1.0003) smaller in size. Does anyone know how to do it? Thank you

22. Phil says:

Hi - I need to find a way to change the way my data is being displayed in an excel table that I have... where I have an entry of "00024 01" that is formatted as "General" input... and where the blank space is supposed to represent a decimal point ... my question is, how can I get this entry to display simply as the number "24.01" with no leading zeroes and with the decimal point showing??

(Note: I have thousands of entries and cannot retype the data and need an automated solution.)

23. Marie says:

I want to display the following custom format in a cell:
1 487,67€
10 487,67€
101 487,67€
What would be the code I put in "Type:"

24. Dmitry says:

Hi, Svetlana!

Please, tell me, is it possible to display -3.5 (for example) as
(space)(space)(minus)3.5(space)(space)
I managed only to place (minus) only before (left from) insignificant spaces, like this:
(minus)(space)(space)3.5(space)(space).

Thank you!

• Hi Dmitry,

If we understand your task correctly, the following formula should work for you:
=CONCAT(" ", -3.5, " ")

Hope this is what you need.

• Luke says:

Dmitry, I'm assuming that this is what you're after?
_ _ #.#_ _ ;_ _ -#.#_ _ ;_ _ 0.0_ _ ;@

• Luke says:

Correction, sorry:
_ _ 0.0_ _ ;_ _ -0.0_ _ ;_ _ 0.0_ _ ;@

25. Anna says:

Hi,

I have the following custom code: "3189967-"000 which works fine, however, sometimes I will need the letter "T" to appear after "000" but not all the time. Do you know if this is possible?

26. Marina says:

Dear Miss,

How to get that in the same cell number is negative regardless of numbers of digits the number is in brackets?
Example -1,23 is (-1,23) -1,0 is (-1) -23,5698 is (-23,5698) 1,23 is 1,23 2,695 is 2,695 all can be in same cell.

I tried #,###; (-#;###) but this returns (-1,).
Thank you

27. Zaman says:

hii
can i do with 2 number in the cell such {1.2,2.3} so change format make like this -> [1.200,2.300]

thank you

28. Ramesh says:

Hi,
Need your help to convert the cell number format from 1D5F859A to 9A855F1D in excel

29. Jay says:

I am trying to have text display in a cell that displays with a leader. The text within the parentheses needs to display in italics. When I apply the custom format for a leader in the cell (@*.), it removes the italics from the text in parentheses.

An example would be the sentence below, the text "day, week month" would be shown in italics:
Today is a nice (day, week, month)..........

Any idea how to solve so I can keep the italics and also the leader

30. Nalanda High School says:

I am trying to type a number starting with zero. How to type a number starting with zero.

31. CA says:

When using accounting format, true zeros display as a dash, but numbers that round to zero display as a zero, so visually you see a column showing some zeros as - and others as 0. Is there a format to make them all display as a dash without using rounding to change the numbers?

32. JY says:

Hi Svetlana,
I try to format percentage and keep a digit, if the digit is 0, remove it.
I use the Custom as 0.#%;-0.#%; However it will keep dot in there. For example, 24.0% is showing as 24.%
How do I remove the dot as well?

33. Citizen says:

Hi,
I want to display number in 1000 seperator e.g. 1000000 as 10,00,000.00 but on my excel it shows 1,000,000.00

34. LH says:

Hi, I need the display to say: 1506.69.6020. So basically, 4 digts. 2 digts . 4 digts. I set it as " ####.##.####" but it doesn't work. can you help? Thanks, Lillian

35. Charles Schroeder says:

Hi
I would like to display the currency in cell D10 which is R 1 345 678,93 in say Cell B6 with preceding text "Order Book Value is". If I use ="Order Book Value R"&D10 I get "Orderbook Value is R1345678,93". How do I get the custom formatting to format the value to look like that in D10. I have tried various custom format combinations, but to no avail!
Thanks

Thanks

36. Keely says:

Hi I am trying to format numbers into billions so displaying 4.38 billion instead of 4,378,772,008, I have over 40 numbers I need to do this for. Any help would be appreciated.

• lahsohk says:

Did you figure this out? I'm doing the same Econ assignment

37. peter says:

i would like to have a format that is like a Heading sequence:
1
1.1
1.1.1
1.1.1.1
thanks

38. Ozgur says:

Hi,
I have a few questions maybe you could help;
1. I prepared a table in excel like a client data form and in the name and surname columns I want the proper case (Joe not JOE). I used Proper formula and data validation but it doesnt work.
2. How do I avoid duplicate entries automatically? ( I put a full name column that concatenate name and surname data).I want it to check and stop "John Smith" double entries for instance. How is it done with data validation custom formula?
3.Is there a template way for e-mail entries as per data validation like ________@_________.com/ru/en etc?
4.I am also having trouble with dependent data validation entries. Suppose there are two types of buildings and first one has 5 floors while the second has 6. I put data validation list for the blocks but for the next column I want it to choose 5 floors for Block A and the 6 floors for Block B but the dependent list isnt working.Please kindly advise.
5. I used row formula for autonumbering for client id # and I use vlookup for Client name and it is working but if the line is blank it gets the row number for client Id. I mean like 20090001 and 20090003 instead of 20090002. I want it to continue with consecutive numbering. How is it done?
Thank you & Best regards

39. Sneha says:

Hi,
How can i change number format in this way?
For Eg: Take a let us take a number 5000000. Currently in excel its showing like 5,000,000. I want the "commas" in this way.. like 50,00,000.

40. Marcello says:

Hello there,
I am trying to define the following range from Zero to one:
- Negative numbers become automatically 0.01
- Zero becomes automatically 0.01
- Positive numbers >1 become 1
Could you please give me some hints on how to define the custom format?
Thanks a lot!

41. Nisha M says:

How can i format part of text in a single cell that contains a formula result.
eg. a cell contains formula result like "Iam 25" i want to format 25 as bold

42. Michael Carney says:

I want to display the tenths place if it is *NOT* zero and I want to *NOT* display the decimal point if the tenths place is zero. How can I do that?

For example, 20.23 should be displayed as "20.3" but 20.03 should be displayed as "20" without the quotes.

In other words, I only want the decimal point if and only if a non-zero digit is in the tenths placeholder.

Any help is appreciated, Michael Carney

43. line breaks says:

Hi is there anyway to put a Line break into custom number formats. I want to do this so I can display the Date and time on my graph. What I current get is:

00:00 Mon 10 Jul 2019

What I want to display:
00:00
Mon 10 Jul 2019

44. Long says:

Hi,
I need your helps to convert 100 to 10^2.
Thanks

45. Janko Smigoc says:

I want to use fraction in excel with value 1750/28000 and display as 1750/28000, as number type. Best regards Janko

46. lAWRENCE says:

I have a spreadsheet of part numbers that needs separating into blocks. 0123456789 becomes 0 123 456 789. This works for most of the spreadsheet, but a few numbers refuse to convert. I am using the CUSTOM feature like this ==> 0 000 000 000. Some, but not all of the failed results have a letter in them. i.e. 0 123 A56 789. Any thoughts?

47. RAM CHANDER says:

Sir
We need a single space between word in HDFC0000440 like H D F C 0 0 0 0 4 4 0 through cell formatting only. Please adviese.

Ram Chander

48. Gidi says:

Hi,
0,, "Mbps" will display 10000000 as: 10Gbps
How do i display:
0.000001 as 1 usec
?
Thanks

• Gidi says:

Of course i meant:
0,, "Mbps" will display 10000000 as: 10Mbps

49. Sharan says:

Hi,
How can i use customer number formatting to display 12,34,45,456.00 to 12.34 in cell with dividing the value.
Thanks
Sharan

50. Sharan says:

Hi,
How can i use custom number formatting to display 12,34,45,678.00 to 12.34 in cell without dividing the value.
Thanks
Sharan

51. Hidde says:

Hey,
I'm working on a currency (euro) formula, but it's for dutch customers. This meaning that instead of it being written like 1,234.56, we use a period as the thousands separator, and a comma for the decimals. What formula could I use for this? I tried >> "€"\ #.##0,00;[Red]"€"\ \-#.##0,00 << but it keeps jumping back to having the comma as a thousands separator, and a period for the decimals.
Thanks!

52. Kompar says:

Simply brilliant. I wan completely lost in the excel 'wildcards' and your explanations and examples saved me.
BIG thank you

53. Tam says:

Hi Svetlana,
I use Excel to keep track of job numbers for my work. I am using Office 365. Some of these job numbers begin with a 0 and are 14 digits long, but not all begin with 0. I need the 0 in there and have been doing fine with using 0#############, however, when I create a new worksheet, the custom format is no longer in my excel. Is there something I need to do in order to save it permanently so I don't have to keep creating the format each time I create a spreadsheet? I'm not sure if this information helps but the worksheets I create are exported from a database online so it gets saved in HTML format in my downloads folder first then I resave it in documents folder as an xls.

54. Stephen says:

Hi,

Looking to create a format to change a 12 number field into something like this
123456789012
to
ABC1234_123_12345

Cant seem to find if and how I can achieve this.

Stephen

55. dora says:

Hi,
I use IF Function in the cell so that it will show me "yes" or "no"
The thing is that I want if the formula show "yes" I want it in a red color... been trying to format the cells but nothing work... could you tell me what should i do? many thanks

• dora says:

Sorry, no need to answer... I already got the solution, changing the if formula into a code of 1,0 then formatting the cell to translating the code into a text and coloring according to my condition

56. afsal says:

how can I show in a cell if I type 1 it shows me 1 no, if I type grater than 1 it should show me 2 nos

57. John says:

How to display 0.99 as 0 and not 1
Or 1.8897656788 as 1 and not 2
I need the value do i cant use int or trunc or round function. Its only a matter of how to display

58. virag says:

how to get 1,11,11,111.11 format?

59. Kathleen says:

hi
hope you can help.
i have a number, eg. 0602.10.00.3.
however this number has to have 10 digits as in 0602.10.00.03.
there has to be a zero before the final digit if it is less that 10.
i hope you can help
Kathleen

• John says:

Hi Kathleen,
You may try this formula =CONCATENATE(LEFT(A1,11),"0",RIGHT(A1,2)). Value in cell A1. Hope this can solve your problem.

60. akssixteen says:

I want to display 123° 23.5'E, when I type 12323.5E.
or
I want to disply 35° 02' N, when I type 3502N.
I mean any text follow by any number, display as earth point.
But I cannot use custom number format. Is it possible.

61. Pang says:

How to set the custom format that can be use in all my excel file and not only the file that I added the custom format? Thanks

Hi Svetlana,
My figures are all in thousands. I need to input 1 and it displays as 1,000. (or type 10, displays 10,000 ),type 1.5 shows 1,500 type 10.5 shows 10,500 .(So as to save myself typing the zeros). Then when I sum those figures, the total should be 23,000 (total of
1,000+10,000+1,500+10,500 (and not 23).

• DVS says:

In Alignment in excel go to number, in that select custom, in that delete General option and insert #, and press Ctrl+j and insert %%% and get the result please

63. Abu Shaheen says:

Hi
I wants the word MATHEMATICS in to this formats
MATHEMATICS
Separate it each letter in different cells
M A T H E M A T I C S
Now
M S A C T I H T E A M

Means take first letter from left side
M
Then Last letter of the spelling S
Then Second letter from left side
Then Second last letter
Means the first letters of the spelling and last letters of the spelling one by one

64. Joanna Hong says:

Hello, I'm currently using a formula to combine values from several cells into one cell. While doing that, I am hoping that all of the numbers that get merged into the final cells show up to two decimal points (e.g., 1.20), however, even when I custom format the cells using the 0.00 code, my merged number does not show the last decimal 0 value. So it shows as 1.2 instead of 1.20. Is there any way I can fix this? Just in case it might be helpful, the formula that I am currently using the following formula:
=IF(c9>.05," ", IF( c9<.001,"***" ,IF(c9<0.01,"**",IF(c9<0.05,"*"))))

• Joanna Hong says:

Sorry, that was the wrong formula. Below is the correct one!
=CONCATENATE(C6," (",D6,", ",E6,")","",G6)

65. jake says:

Wow this is an extremely informative page, which I will be saving for future reference. Unfortunately, I don't think it answers my current question, which is, can I format the number to be spelled out in letters? E.g, the number 5 is formatted as "five"? Thanks!

66. Lisa says:

How can I make it so that my histogram shows 10K-20K, 20-30K, 30-40K, etc?

I have \$471.4M. and \$8B
How do I convert this value so that M and B are replaced with their respective zeros.
for example \$471.4 --> \$471400000

68. Ricardo Mateus says:

Hello. Here's a challenge:
I'm looking for a solution to a problem that I have regarding regional settings.
Is it possible to have my regional setting in place [.(period) as Thousands separator and ,(comma) as Decimal point] and have specific cells in my spreadsheet with: [.(period) as Decimal point and ,(comma) as Thousands separator]?
I need to have charts and tables with two different settings (and language) in the same sheet...
THANK YOU!!!!

• Lauri says:

Ricardo Mateus, have you found a solution?

69. Mike says:

Is it possible to display a fractional value in a different color than the integer preceding the fractional part?

For example if I have the value in a cell of ... 42 15/16
Is there a way to display the "42" in black and the 15/16 in blue?

70. Philipp says:

Hi,
I am trying to copy tables with custom number formats from Excel to Powerpoint but when I do so, the ppt table will have what appears like doublespacing in each row. I have tried to format it in Powerpoint (cell margin, paragraph function, etc.) but it won't work unless I change the number format in excel. Only way i can copy-paste tables now is to embedd them or paste them as picture. I am using the newest Office 365, my colleague can do it with an older office version.
Do you have any solution for this?

71. jiro says:

i want to add word after number...like (1 day) if i click 1 it outo be 1 day

72. ramil says:

i want to enter the employee number wherever in column a and it will show the name of the employee. how can i code that using macros vba?

i have amount and i need format code for egyptian pound so i need cell with LE
like 60LE

74. des says:

Is there any way I could directly partially mask user input within the same input cell i?
Meaning if user will to key in S12345678Z within A1 cell, it will auto mask to S********Z within A1 cell itself. Can Custom format in Excel be able to do so ?

The only method I have successfuly done is 1 cell for User Input ( e.g A1) then masked result cell (E,g A2 using formula like =LEFT(A1,1)&"****"&RIGHT(A1,4)

75. Matthew H. says:

Thank you SO MUCH for this incredibly useful information!! It is so well presented and thorough!! I am glad that I have purchased your product and supported your efforts, keep up the great work!!

• Thank you, Matthew!

76. Mimay says:

Thanks a lot. How to navigate excel became clearer to me

77. Dayanand Sharma says:

Respected maim,
I am trying customise cell value depending upon excel formula. But I couldn't. Can you help me. Just look below
=IF(OR(ISBLANK(E11),ISBLANK(F11)),"NO","YES")
NO AS RED COLOUR
YES AS GREEN COLOUR

78. Joann says:

Hi there, what's the formula if i want the 206,508 to be shown as 2.06508? thanks!

79. Dvs says:

I want 123A as 1/23A in excel custom format. Pl help

80. Andrew says:

Hi, I have whole that I need to change to currency format.
For example, 10816, I need to convert to 108.16.
What Custom formula would I need to use to accomplish this?
Thanks,
AL

81. Sharon says:

Hello,
The excel formatting information that you provided on this site is amazing and so helpful.
Is there a formula to make 4 display as 0004000
Thank you very much.
Sharon

• Hello Sharon!
If you want to add three 0 to the left and to the right of number 4 and the number itself won't change, then you can try to use Custom Format.
Please go to Format Cells, choose Number -> Custom Format and set the followig format:

000###0"000"

82. Codru says:

Hey I added the following formula _(* #'##0_);_(* (#'##0);_(* "-"_);_(@_) but now it set's numbers like 333 like this '333 too. Can I somehow modify this so it doesn't include these?

• Hello Codru!
As a digit grouping symbol, Excel uses the symbol that is specified in Control Panel -> Clock and region -> Region -> Formats -> Additional Settings -> Digit Grouping Symbol.
Unfortunately, it is not possible to change that symbol using a custom number format. Please change it to ' via Control Panel.

83. Abhishek Kulkarni says:

If custom format consists of 4 sections of code separated by semicolons then how do I create a custom format with more than conditions 4 conditions as conditions are also separated by semicolons?

• Hello Abhishek!
Custom Format indeed consists of 4 sections separated by a semicolon. Each section has its own assignment: 1 – format for positive numbers, 2 – format for negative numbers, 3 – format for zeros, 4 – format for text values. A user-defined number format can also contain up to two conditions which are entered in square brackets, e.g. [> 100] or [<= 100].
When you use conditions in a user-defined number format, you redefine the first and the second sections.
For example, Custom Format [Red] [<100] 0; [Blue] [> = 100] 0 means that values less than 100 will be displayed in red, and more than 100 – in blue. However, there can be only two of such conditions.
To apply more than 2 conditions to the cell format, you may try to use Conditional Formatting which is well described in this blog post.

84. Christian N Sturt says:

I use a format such as:
[<1000000]0.0000," Kilo";[ 100milli
0.0001 -> 100micro
0.0000001 -> 100nano
etc?

85. Marcel Maes says:

I'm putting together a sheet with different number-formats and examples for myself.
A little question: there's a format #.##0,00 and a format #.##0,00;-#.##0,00 (XL2010)
What is the difference between those two? If a negative number is formatted with #.##0,00 it just gets a minus sign as expected. Why then would one need #.##0,00;-#.##0,00 ?

• Hello Marcel!
Each number format can have up to four sections, separated with semi-colons. This structure can make custom number formats look overwhelmingly complex. To read a custom number format, learn to spot the semi-colons and mentally parse the code into these sections:
1.Positive values
2.Negative values
3.Zero values
4.Text values

86. Ravi says:

if string is 20SCSE1398 i want 101398 , first two digit and last 4 digit, how to remove in between 4 characters or digit

87. Ravi says:

Sorry this is what i want to ask if string is 20SCSE171398 i want 101398 , first two digit and last 4 digit, how to remove in between 6 characters or digit

• Ravi says:

201398 i want

• Hello Ravi!
To delete and replace several characters in the text, the REPLACE function is used. If I understand your task correctly, the following formula should work for you:

=REPLACE(Q1,3,6,"")
=REPLACE("20SCSE171398",3,6,"")

Hope this is what you need.

• Roselle says:

Flash Fill could do the work without using any formula. I really dont know how to explain it right but you can search it for yourself if you are interested.

88. krish says:

Hi Team,
I'm currently working on converting different formate, but I didn't figure out, How do I convert 1467:1 into number formate.
Thanks

• Hello!
Please let me know in more detail what you were trying to find. Explain what you want to get - remove the colon, discard the characters after the colon, do the division, or something else?
Give an example of the source data and the expected result. It’ll help me understand it better and find a solution for you. Thank you.

89. Kanwaljit says:

I need a number format to show numbers like following
ABC/20/I/461
ABC/20/P/5420
ABC/20/K/489
Now the First 7 characters are fixed i.e., ABC/20/ , next character is an alphabet then / and then a number is to be written.
What should be the number format if we can avoid typing ABC/20/ portion and simply type like
I461
P5420
K489
and those are shown as
ABC/20/I/461
ABC/20/P/5420
ABC/20/K/489

• Hello!
It is impossible to separate the text from the number directly in the current cell and at the same time add additional text using the format. Use VBA. Using formulas, this can be done. But you will enter data in one cell, and you will receive the result in another cell.

90. Dheeraj jain says:

Hello !
I wanted to convert "a row consisting of numbers ending with either Dr or Cr at end " to row consisting numbers with -ve wherever Dr and +ve wherever Cr occurs.
ex row 23 Dr -23
52 Dr to be coverted as -52
72 Cr 72
thanks

• Hello!
I’m not sure I understood you correctly, because your abbreviations are not clear to everyone.
The following formula should work for you

=IFERROR(--SUBSTITUTE(A15,"Cr","",1),-SUBSTITUTE(A15,"Dr","",1))

I hope this will help

91. Naomi says:

Hi! I would like the fraction part of mixed fractions to be smaller font (ideally like the symbol). Example: 3¼ instead of 3 1/4. Is there any way to do this in Excel?

• Hello Naomi!
In Excel, the number format can be set only for the entire cell, but not for its part. If the cell contains text, then you can select part of the text and set a special format for it.

Got a special need whereby i need to format data that contains numbers in a special format

If the there are 3 numerical digits i need to display them as 3 digits
If more the structure would be 000/0000

I will be using them for formulas containing product codes

How would i do them baring in mind I need the content to be exactly as formatted above

Your problem can be solved by converting the number to text.

=IF(LEN(A3)>3,REPLACE(A3,3,1,MID(A3,3,1)&"/"),A3)

I hope this will help

93. Gus says:

Hi guys. I just wanted to say thank you for an incredibly informative page about excel formatting.
I am stunned to see that despite the quality and breadth of formatting info provided, so many desperate people still need to send you exotic requests for formatting help, and they are genuinely stuck. I think there is a general issue in need of a better solution than what excel offers. Food for thought!

94. Ram says:

Suppose i have a text "Apples" in cell A and a negative number (1,234,567.55)in cell B
Now iam clubbing these two cells using concatenate formula while converting the amount in to Millions.
the result iam getting is Apples \$ -1.23 M.
=CONCATENATE(A1," ","\$"," ",ROUND(B1/10^6,2)," ","M")= Apples \$ -1.23 M
My question is how can i get the negative number into a parenthesis like
Apples (\$ 1.23) M

• Hello Ram!
It is impossible to make a custom number format in the form you want. Therefore, you need to use text functions.

=IF(B1>0,CONCATENATE(A2," ","\$"," ",ROUND(B1/10^6,2)," ","M"), CONCATENATE(A2," ","(\$"," ",ROUND(-B1/10^6,2)," ",") M"))

95. shra1 says:

Cool!! Nice explanation, thanks

96. Francis Onyango says:

How can I use format cell custom in Likert Scale? My interest is that I should key in a number but the cell should read the scale. for example, 1=Totally Disagree, 2=Disagree, 3=Undecided, 4= Agree, and 5= Totally agree. My interest is to key in a number e.g. 4 but the cell should read AGREE.

97. Cris says:

Hi,
Is there a way to format numbers, so that the digit of the decimals appear with a smaller size or a different color than the integer part?
Thanks a lot!

98. Hello , i try find new principe says says:

A2 = x
A3 = \$
B2=2
B3=3
can i custom format with formula like B2= 2x, B3=3\$ B2 +B3 =5

99. Simon says:

Hi, I'd like to type in a MAC address (12 Charactors A-F 0-9) an have it display in the format 44-85-00-B2-5E-3B
So I'd type in 448500B25E3B and the cell would display 44-85-00-B2-5E-3B
How can this be done?

100. Frank Eubanks says:

So after some experimenting I discovered how to have multiple formats for Billions, Millions, and Thousands. Here is the solution:
[>999999] \$#0.0,,,”B” [>99999] \$#0.0,,”M” [>9999] \$#0.0,”K”
Note that a space is between each format condition (For example, ..."B" space [>99999]... allows for the millions to follow the billions labeling.). By having only this, then all others (negative) will follow the same format. I tried to get the negative but Excel limits the format code to 85 characters. A key piece of information to know.