by 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:
- Select a cell for which you want to create custom formatting, and press Ctrl+1 to open the Format Cells dialog.
- Under Category, select Custom.
- Type the format code in the Type box.
- 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:
- Format for positive numbers (display 2 decimal places and a thousands separator).
- Format for negative numbers (the same as for positive numbers, but enclosed in parentheses).
- Format for zeros (display dashes instead of zeros).
- 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:
- 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.
- 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.
- 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.
- 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; "-"; GeneralNote. 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.
- 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. - 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 only displays significant digits, without 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 value | Display as |
| #.00 | Always display 2 decimal places. | 2 2.5 0.5556 |
2.00 2.50 .56 |
| #.## | Shows up to 2 decimal places, without insignificant zeros. | 2 2.5 0.5556 |
2. 2.5 0.56 |
| #.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:
0or#- display the nearest integer with no decimal places.0.0or#.0- display 1 decimal place.0.00or#.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 parentheses |
| : | 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 parentheses
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 parentheses, 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 parentheses, 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 parentheses 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_);_(@_)
To format financial data or other types of data where it's important to distinguish between positive and negative numbers, you can use the following format, which indents positive numbers and zeros from the right border. Additionally, it rounds all numbers to the nearest integer and displays them with a space as a thousand separator. Negative numbers are displayed in parentheses and in red font color:
# ##0_); [Red](# ##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:
- How to create a custom date format in Excel
- How to create a custom time format in Excel
- Get day of week from date using custom format
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!
628 comments
Is there a way for showing the data as 2^#?
I want to show 1024 as 2^10 and 1 as 2^0.
1024,00000000000 = 2^10
512,00000000000 = 2^9
256,00000000000 = 2^8
128,00000000000 = 2^7
64,00000000000 = 2^6
32,00000000000 = 2^5
16,00000000000 = 2^4
8,00000000000 = 2^3
4,00000000000 = 2^2
2,00000000000 = 2^1
1,00000000000 = 2^0
0,50000000000 = 2^-1
0,25000000000 = 2^-2
0,12500000000 = 2^-3
0,06250000000 = 2^-4
0,03125000000 = 2^-5
0,01562500000 = 2^-6
0,00781250000 = 2^-7
0,00390625000 = 2^-8
0,00195312500 = 2^-9
0,00097656250 = 2^-10
Hello!
Excel does not have such a custom number format. You can use the LOG function.
="2^"&LOG(A1,2)
Is there a way to key in, say, 327 and have it automatically convert to 0.327 without using formulas or conditional formatting? I am manually keying many numbers and don't want to have to type the decimal. Nor do I want to manage any other columns.
Hi,
I am looking for an opportunity to generate an AWB stock.
An AWB number consists of 3 numbers, a minus, and 8 numbers. a total of 12 characters
The first 3 numbers and minus (-) are always the same.
The next 7 mummers count up as usual
The last number counts up from 0 to 6, and starts at 0 again
111-12345670
111-12345681
111-12345692
111-12345703
111-12345714
111-12345725
111-12345736
111-12345740
111-12345751
111-12345762
111-12345773
Hi!
If I understand your task correctly, try the following formula:
="111-"&SEQUENCE(100,1,1234567,1)&TRUNC(MOD((SEQUENCE(100,1,1,1)-1)/1,7))
Thanks for your help, this works good
Hi,
I notes something wrong in the formula (maybe I was not clear in my fist question)
This do not always start with a 0 in the end.
It can be like all these 3 example
111-12345670 111-23456781 111-3456792
111-12345681 111-23456792 111-3456793
111-12345692 111-23456803 111-3456804
111-12345703 111-23456814 111-3456815
111-12345714 111-23456825 111-3456826
111-12345725 111-23456836 111-3456830
Hi!
Change -1 in the formula to the desired number.
Im sorry. I have one more question
How do I do it if the numbers start with 0?
like:
111-00005252
111-00005263
111-00005274
111-00005285
111-00005296
111-00005300
If I use the formel will it show:
111-5252
111-5263
111-5274
111-5285
111-5296
111-5300
Hello!
I recommend reading this guide: How to add leading zeros in Excel with the TEXT function.
=TEXT(A2,"00000000")
Hi,
Firstly, thank you so much for this useful information, and for the method of the display.
Please, I need to use commas to separate thousands for numbers with five or more digits (not four digits) in the picture, e.g., “10000” should be “10,000”. So, could you please tell me how to do this? Thanks in advance.
Hi!
Pay attention to the following paragraph of the article above - Display a thousand separator.
I want to format a tag entry cell so that it can auto correct the manual entry to the correct number of characters. All tags should be 3 numbers, a selection of letters, 4 numbers, Optional letters if there are multiple items doing the same job. For example if the manual entry was 13FT26 then the corrected would be 013FT0026. Or 7PSLCI8B would be 007PSLCI0008B / 007 is 3 numbers, PSLCI is a selection of letters, 0008 is 4 numbers, B is the optional letter.
If a cell format cannot do this then I can use a macro but I'm not too sure on how.
Hi!
Excel has the ability to validate data, but it does not have the ability to correct that data as it is entered. For more information, please visit: Custom Data Validation in Excel : formulas and rules and Excel Data Validation using regular expressions.
Thank you so much, I shall take a look.
Is there any option to automatically get numbers in an order when u press enter to the next cell
Hi!
Here is the article that may be helpful to you: How to use AutoFill in Excel - all fill handle options.
I have data like this
CS-1
CS-10
CS-125
P-2
P-23
P-134
BX-12
BX-1367
BY-1366
TKY-1
TKY-12
TKY-3
I want to format this data as below for Sort by number, please suggest me the format I need to do.
CS-0001
CS-0010
CS-0125
P-0002
P-0023
P-0134
BX-0012
BX-1367
BY-1366
TKY-0001
TKY-0012
TKY-0003
Hello!
Extract digits from the text, and add leading zeros to it using the TEXT function. See these links for detailed instructions.
=LEFT(A1,SEARCH("-",A1)) & TEXT(--MID(A1,SEARCH("-",A1)+1,50),"0000")
I have data like this
3A1B
1A1B
2A1B
I want to add space on custom format to:
3A 1B
1A 1B
2A 1B
Can anyone help me? Thanks
Hi!
You cannot format text as numbers. You can insert spaces in text using the substring functions.
For example,
=LEFT(A2,2)&" "&RIGHT(A2,2)
I have this number 81628215511. and i want in this format 81.62821.5511. kindly provide me the formula which would help me get rid of my half problem.
hopping you would help me
Hi!
You can use custom number format like 00\.00000\.0000\.
sorry and thank you for the information. sorry for the previous comment .that guy is bulshit guy.
no need we got the formula.you guys are the worst.
What formula/ custom format can I use to print -2 pts as (2 pts)?
using ##\,##\,## number format, if i type a number 123456789, it displays like 12345,67,89. But if i type three digit number like 123, it displays in the cell like ,,123 i.e. leading commas are displaying. Please let me know how to format a cell to curtail leading commas and show numbers in hundreds separator
Hi!
Excel can only use the thousands separator.
Is there any provision in Excel numbering system that - one can enter any number with any decimal places and insignificant zeros.
e.g. in cell A1 one can enter the numbers 0.110, 1.0000, 00.567, 0.001, 0.0000100 etc, without any restriction or rounding up the numbers.
I know this is possible if we format the cell A1 as TEXT, but then it can not be used in formula to use it for the calculation.
Hi!
Only one number can be entered in a cell. Insignificant zeros are ignored. As a text, you can enter any value.
Such an impressive document. Thanks a lot!
Thanks a lot Sir,
It is working as 500/00 or any other amount, it is possible it is working as 500/- (like forward slash and a dash)
Again thanks.
Reply
Alexander Trifuntov (Ablebits Team) says: September 21, 2022 at 7:51 am
Hello!
You can change the decimal delimiter.
Go to Excel Options.
Go to the "Settings" - "Advanced" section.
Uncheck the option to use system separators.
In the window that is now active, replace the dot with /.
After pressing the OK button, all numbers with a fractional part will be displayed in a new way.
sohailafzal says: September 20, 2022 at 7:19 pm
You and your team always help the others may God bless you.
I have question in our areas we often use 500/- in the place of .00 like as 500.00 but we use 500/- and also using this style in adding or substract or multiply so in excel any format of currency that used /- style like as decimal .00 and working as zero
You and your team always help the others may God bless you.
I have question in our areas we often use 500/- in the place of .00 like as 500.00 but we use 500/- and also using this style in adding or substract or multiply so in excel any format of currency that used /- style like as decimal .00 and working as zero
Hello!
You can change the decimal delimiter.
Go to Excel Options.
Go to the "Settings" - "Advanced" section.
Uncheck the option to use system separators.
In the window that is now active, replace the dot with /.
After pressing the OK button, all numbers with a fractional part will be displayed in a new way.
Using a custom format of #,###.## so that decimals will only appear if there is a need for them does however lead to this display showing say 5 as 5. which looks strange. Is there any way to omit the decimal point, if there are no decimals to be displayed?
Hi!
If you have selected a number format with decimal places, then the dot will always be displayed.
HI Alexander, thanks for this great article!
I'd like to use colors in Custom Format to highlight errors, ie, get a #N/A or #DIV/0 in Magenta. However, Excel does not recognize the error code as text, nor as a number. Is there any way to have the error codes in a different color?
Thanks so much!
Hello!
Create a conditional formatting rule with a formula
=ISERROR(A1)
This should solve your task.
Is it possible to specify different cell format for odd and even number? Thanks.
Hello!
Create two conditional formatting rules - one for even and one for odd numbers.
I hope I answered your question. If something is still unclear, please feel free to ask.
I have some numbers I am working with in a pivot table. I am wanting them in thousands. I have formatted them with "$#,##0,K" and they look great. For example 4234 shows up as $4K. However when I go to group these I get something like 0-5000. Is there any way to format this as 0K-5K? Thanks
Hello!
"0-5000" is text. You cannot format it as a number.
I have numbers formatted and customized with prefix numbers, but I want to copy it at a whole as shown including the preformatted numbers,
Example: Inputed as 10 but with a prefix number of "295500901000000" so the result becomes "2955009010000010"
but, I want to copy the whole result number to "2955009010000010" instead of "10"
Can help me fix them? Thank you in advance.
Hi!
Excel accepts and processes numbers only up to 15 digits, and displays no more than 11 digits for a numeric value. Anything greater is shown in exponential (scientific) format, such as 1.111E+14
You can automatically add a number as you type using a VBA macro
Thank you Sir Alexander for replying, I really appreciated it. Here is my concern:
How can I copy a cell with a value entered as 2340 (example only), but it was formatted with prefix the 2955009, so the result would become “29550092340” (joined the value entered).
Value entered: 2340
Result: 29550092340
Question: If I copied the result, the value showed just 2340, but I want to copy the whole result of 29550092340.
Please assist me how; thank you very much.
Hi!
Try to use Paste Special - Paste Values and Number Formatting.
The separators are not correctly separating the number. Not sure how to fix it. i need separator in hundred place. I tried formatting it but it kept on putting the separator in the thousand place. For example: 1234567.89 when I format this number to include separators it's showing as 12,34,567.89. How can I fix it? I checked the setup also (File>options>Advanced) but wasn't able to fix it. Please advise.
Hi!
In Excel's custom number format, only the thousands separator is provided, not the hundreds separator.
yes, you are right sir., but i need separator in hundred place. can Is it possible to have a hundred separator.
Hi,
I'm trying to understand if this is possible - K12343-4-AB1-F888 ? Can I force excel inputs to be this specific string of alphabets, numbers and hyphens?
Thanks, in advance!
Hello!
You can use regular expressions to validate data. I recommend reading this guide: Excel Data Validation using regular expressions (Regex). This should solve your task.
Hi,
Id like to appreviate currencies per the below:
$1,234,567 show as $1,235
$730,935 show as $731
$50.357 show as $50
can you please assist with correct format code?
Hi!
Pay attention to the following paragraph of the article above: Round numbers by thousand, million, etc.
It covers your case completely.
Try custom format:
$#,##0,
Hi, I have a cost sheet template that displays the cost of items with 2 decimal places, however, I am using the formula 0;-0;;@, to display any zero or null values as blank. However, I want to apply that formula to all fields, but where there is a value, I need it to display 2 decimal points, and I cant find a formula that can be applied to all cells to tell them to display zero if no value, but if there is a value, display 2 decimal places. thank you in advance
Hello!
Use conditional formatting to apply a 2-digit format to cells that have values. I hope this will help, otherwise don't hesitate to ask.
Hello,
I have a problem with prices.
In all tables, my prices are like this: 2290 and are supposed to be 22.90
Is there any way to fix them all together, not going one by one?
Hello!
Divide the numbers in your pricing columns by 100. The following tutorial should help: How to divide a column by a number.
Hi,
I have $5,775 and want to display this as US$ 5,775
Thank you in advance.
Hi!
Try this number format "US"$ #,##0;-"US"$ #,##0
Hope this is what you need.
What bothers me about Excel customizations is that Excel will not allow me to save them to use in other worksheets. I can make a template but that requires a lot of clunky cutting and pasting. Why not allow users a .ini file to save customizations of different types that could be used universally as if they were native?
How to remove text from cells with custom formatting like dilip.choudhury@ gmail.com will show as dilip.choudhury. I know this can be done with excel functions, vba..etc. Is it possible to do with excel custom feature option.
Hi!
The following tutorial should help: Excel substring functions to extract text from cell.
Please try the following formula:
=LEFT(A2,SEARCH("@",A2)-1)
Thanks for a great article!
Is it possible to display a percentage value without the percentage sign using custom number formatting? I want the chart labels for each data point to be displayed without %.
What I have: 65%
What I want to display: 65
I cannot change (like x*100) because it is used in the graphs and in other calculations. I also do not want to add an extra column with a new value.
Hi!
65% is the number 0.65
You can't show it as 65 using a custom format
Ah, too bad! Thanks
Hi,
Can someone help me make my excel cell that looks like 12345, look like 1ha23a45m?
That is the system for the area In my country.
Even something like 1,23,45 could help a lot.
I really appreciate any help you can provide.
Hi!
It is not possible to do this with a custom number format. You can turn your number into text with the Excel substring functions to extract text from a cell.
=LEFT(A1,1)&"ha"&MID(A1,2,2)&"a"&RIGHT(A1,2)&"m"
Try this on a cell containing 12345
#"ha"##"n"##
H,
I am using [>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0
to show the number as 1,11,11,111.
Subtracting two number using above format, I will get the result in same format as shown below.
A1=3,45,67,890
B1=2,34,56,789
C1=1,11,11,101 (A1-B1)
But, i need to have plus (+) and minus (-) based on operation.
Here C1 should show like +1,11,11,101 and D1 (b1-a1) = -11,111,101
Hello!
Tru this custom number format:
+###,###;-###,###
Hope this is what you need.
Hi,
Hope you are well.
i have data with 0, 10%, 30 (example) so when I do custom format to hide zero I have used 0;-0;;@ so it hides 0 but 10% became 0. Please advise what is the issue and how I can hide 0 and keep the rest number as it is.
Look forward to your reply
Thanks
Razib
Hi,
Hope you are well.
i have data with 0, 10%, 30 (example) so when I do custom format to hide zero I have used 0;-0;;@ so it hides 0 but 10% became 0. Please advise what is the issue and how I can hide 0 and keep the rest number as it is.
Look forward to your reply
Thanks
Razib
Hello!
Try this custom format:
#;-#;;@
I hope it’ll be helpful.
Hi Alexander,
Thank you for your prompt reply.
I have tried #;-#;;@ but its hiding 0 and 10% both. I want to hide only 0 and keep 10% sales and other numbers same.
Is there any other thought ?
Look forward to your reply.
Thanks
Razib
Hi!
Percentage format (10%) and number format (30) cannot be combined in the same custom format.
Hi,
But both are in different cell. 0% is one cell and 10% is another cell. So can’t do custom format to hide 0 and keep 10% as it is ?
Please advise
Hi!
Custom formats follow a structure with up to four sections separated by semicolons. Use the third section to hide null values: 0.0%;0.0%;""
Please re-read the paragraph above: Understanding Excel number format.
I have a currency format "#,##0.00" when the computer is configured the language of the ENG system separates by thousands and in ESP it does so by millions. how can the format be standardized
Hello! Fantastic article, I have used it a lot in the past year!
I have the following problem. I have a set of numbers in a column, some are integers, some are decimals. If I use something like #.##, then the integers appear with a dot at the end (ie. "42."), instead of just the number. Is there any way to make the decimal point conditional?
(I am sorry if this has been asked already, I scrolled for a while but there are a lot of comments...)
Thanks! Kalman
Ah never mind, I found the comment in the end where it is confirmed that it cannot be done :( It is weird though, one would expect to see whole numbers without a point in the end. I constantly need to explain this behavior to users which is annoying.
Hello!
Unfortunately, if you have selected a number format with a decimal separator, it will always be displayed.
In practical, I am using this method often (eg. #,##0,↑;[red]#,##0,↓;-). Unfortunately when ever I copy-paste into PowerPoint table, the Font Color format can't be copied into the PowerPoint.
I checked using vba script of the cell target using script "rng.Font.ColorIndex" the result seems is not match with the shown font color that perform by Number Format.
Is there any vba script to verify the color shown by Number Format?
Hello!
You may find the Excel User Defined Functions useful: GetCellFontColor and GetCellColor. They allow you to get the color code. You can find them in this blog post: How to count and sum cells by color in Excel.
I hope it’ll be helpful.
Hello Alexander,
Thank's a lot for your fast response.
I've been trial GetCellFontColor function to get the color code of a cell that use custome number format below,
number format : #,##0,↑;[red]#,##0,↓;-
trial number1 : 1000 (shown font color black). Validate using GetCellFontColor, Result = 0
trial number2 : -1000 (shown font color red), Validate using GetCellFontColor, Result = 0 (instead 255 for red color).
It seems the GetCellFontColor is using xlRange.Font.Color that work on cells, It can't detect the font color that produce by custome number format.
Do you have any alternate function that can validate font color that produce by custome number format?
I'll be greatfull for your advice.
Hello!
If you run the macro SumCountByConditionalFormat, which calculates the sum of cells by color with conditional formatting, you will see not only the sum, but also the code for the color you selected.
I hope my advice will help you solve your task.
Ok firstly thankyou for this detailed article on custom number formating. I have struggling with it specially because i am from india and use indian number system which consists of lakhs/crore and not million billion. Was searching everywhere right from youtube but no success.. finally read this article and now i can show values in crores or in lakh.
Great article - thanks. So much information here that you can't find elsewhere.
I'm having trouble with a custom format. I'm trying to show a number in 4 digit format with a letter at the end. For example "50A" would show at "0050A", or "180C" would show as "0180C".
Obviously if i remove the trailing letter from the cell i can get the 0000 custom type to work for the number alone, but when putting the trailing letter back in I don't know what to change my custom format too to also show the letter as well but also stay in the 4 digit format. Can you help?
Hello!
Your data is text, not a number. Therefore, the 0000 custom format cannot work here. You can get the value you need in another cell using text functions.
you can use custom format. just right click the cell and after your number format put "Aa".
I.e. I wanted to calculate hours worked but I wanted "hrs" to show up after the numerical part of the result, so in custom I put: 00.00"hrs". If I wanted a space between the number and hrs I would just include a space between the last 0 and the quation mark like: 00.00 "hrs"
Thank You.
Hi - Really informative article - appreciate all the details.
I'm wondering if there is a way to hide blanks for currency, but have it apply to multiple currency types. My document displays either $ or € based on some other conditional formatting I've got built-in, but I can't seem to find a way to hide cells with a value of €0.00... it only seems to work for $0.00.
Under cell formating, using custom formula, I'm using $#,##0.00;($#,##0.00); which I imagine only works for those cells with the $ currency type - is there a way to make this formula or something similar apply to both $ and €?
Any help would be appreciated.
Hello!
€0.00 values will not be shown with this custom format:
€#,##0.00;(€#,##0.00);
Hope this is what you need.
Hi - Thank you for the quick response!
This seems to work only if the cell is in €. In this use case, the cell this formula is applied to varies between either $ or € depending on other conditional formatting variables. Is there a single formula/format that supports hiding multiple currencies or is that not possible?
Thanks again.
Hello!
The formula cannot change the formatting of the cell. A VBA macro can do this. Use conditional formatting for different formats.
Hi! I have a series of fractions in 1 row that I would like Excel to convert to the percent format in the next row. How do I do this?
Thanks!
Hello!
On this row, make a reference to the original data and apply the percentage format.
Hi,
Looking for a custom number format that shows 450 as 0'5 and -450 as -0'5 (in red).
0.0,;[Red]-0.0, shows 0.5 and -0.5, but need to show an apostrophe.
Thanks,
Simon
Hello!
To replace the period separator with an apostrophe in a number, use the SUBSTITUTE function:
=SUBSTITUTE(TEXT(A1,"0.0,;[Red]-0.0,"),".","'")
i already made it as a conditional formatting for other non error cells and it keeps the data of the cell but it change it appearance to preview a "TEXT". however, i couldn't do the same to na() cells.
Hello,
I have an excel sheet that counts the number of data and reflects it on a pi-chart. however, i found it better to reflect zero cells as #N/A in order to not let it be visible in the chart until it gets a count.
is there a way to format #N/A (basically error cells) as "TEXT" instead?
Hi!
Try using the IFNA or ISNA function. You can find examples in this article.
I hope this will help, otherwise please do not hesitate to contact me anytime.
is it possible to use these functions in the formatting? because i would like to keep the cell value as #N/A
As I am using the cell value in a pie chart I would like to exclude the zero values cells from being shown in the chart and found out the best way is to have the zero cells as #N/A. however, I would like to change the cell appearance to show "ZERO" instead of #N/A while keeping their values.
I have tried using IFNA and ISNA functions in the Formatting and in the Conditional Formatting but it didn't work out for me, is there any other methods I can try to get this result?
Hello!
Conditional formatting cannot solve your problem. Formatting cannot replace the value in a cell. It can change its appearance. I can suggest using the IFERROR function in the formula.
Actually what I am trying to do is to change only the appearance of the cell to show "ZERO" while maintaining the value of the cell as #N/A.
Hi,
Thanks for the great page. Very through and structured.
One query, though.
Take the number 27618263177.12
How can I custom format the cell so that it is shown as 2761.83. Basically, what I mean is I wish to use the Crores format.
Hello!
In custom Excel formats, you can show the number in thousands or millions. Unfortunately, your task does not match these possibilities. You can apply to round of numbers as described in this tutorial.
Hi,
Very good blog. Thank you.
My problem is: I am dealing with a lab measurement. I need conditional decimal point length depending on another column. As follows
if A1 = 0.001 C1 = 6.000
A1= 0.1 C1= 6.0
A1= 0.010 C1=6.000
A1=0.02 C1=6.00
Kindly Help me. Thank you
Hello!
Unfortunately, a custom format in Excel cannot use data from another cell. Your problem can be solved with a VBA macro.
hey i want to get the number format where i can fix the number and change the values after decimal points .is this possible with custom format please reply me.for example i want to fix 12 then if i enter 75 then i want it as 12.75.
Hey, you can't do that with a custom number format. VBA macro can be used.
Hi, How are You,
I need to custom number format code in below format ( 12 Nuber )
123456789025 Change of 1XXX XXXX 9025
Pl. provide custom codeing
thanks for help
Hi!
You cannot solve your problem with a custom number format.
Hi Alex,
I need assistance in excel format cells. I want to enter a number with 5 or 6 digits that will be in the following form
9203 - 7 or 9203 - 77
4 digits fixed dash 1 or 2 digits depending on whether the number is 5 or 6 digits
When i enter number with 5 digits and it is formatted like this 0000 "-" 00 it is displayed as 0920 - 37 and should be 9203 - 7,
and if I format it as 0000 "-" 0 then it is ok, but when I have a 6 digit number then with this format it shows me 92037 - 7.
Thank you a lot, that was very helpful,
tariq
Saudi Arabia
I deal with measurement units. Displaying the unit tells the reader what the number means. For instance, the format #,##0,, "MHz" will display the number 100000000 as 100 MHz. So I know the number means frequency. Similarly, I can use the format #,##0, "km" to display distances. I would like a way to display time so that the number 0.0015 is displayed as 1.5 msec or 1500 microsec. Is that possible?
Hello!
I don't think this is possible within the same cell using formats. You need to use formulas.