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!
614 comments
Thanks for your comprehensive article.
Is there a way to extract a useful cell format from a referred to cell? If i do a =CELL("FORMAT",A1) and A1 is formatted Accounting with 2 decimal places, AKA (almost) #,##0.00 the value returned is ",2"
",2" is not useful in a =TEXT(A1,CELL("FORMAT",A1)) formula. I have to use a xref table to keep the formatting.
So I want Excel to return #,##0.00 instead of ",2" so I can ditch the xref table...
Thanks!
Hello!
Unfortunately, you can only extract a custom format from a cell using VBA.
Hi Alex,
I need assistance in excel format. I have variance column which is column A minus column B. The result is rounded to zero decimal point using excel format.
Now I dont want the variance to appear as Zero. If I have variance as 0.123 or 0.56 it should appear as dash (-) insted of Zero. Negative numbers should appear in red under bracket (15) or (-).
Do we have any format option here.
Hello!
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. [> 1] or [<= 1].
When you use conditions in a user-defined number format, you redefine the first, second and third sections. For example, Custom Format for you -
[>1] 0;[Red] -## ; "-"
Hi Alex, thank you for the format. It works however i just want to understand what can be changed in above format so that if my number is -0.234, that also should appear as dash under bracket (-) insted of (0). The above format is working fine for positive & negative number and even if I have positive 0.234 its giving me dash insted of zero. But I need to keep an eye everytime when the output is in negative 0.234 so that it can be formatted as (-).
Hi!
Carefully read the article above and simply add brackets into a negative number format
[>1] 0;[Red] (-##) ; "-"
Hi Alex, Currently i am using below Combination of format which is giving me the result as
[>0.5]$#,###0;[Red]$(#,###); "-"
First portion of format
1000.23= $1,000
0.234= $- i dont want dollar
0.534= $1
Second portion of format
-1000.23 = $(1,000)
-0.523 =$(1)
-0.234 =$(-) i dont want this, i need only dash here no dollar no bracket
Third portion of format
0= -
Hello!
To apply more than two conditions to negative numbers, use conditional formatting.
I have an Excel column with over 70,000 entries in this format: 123456789A12345. All of them are nine numbers followed by a letter, followed by five more numbers.
I'm trying to get it into the a format like this: 1-23-456-78-9A-12345. I have typed in a custom format of #-##-###-##-#-##### but it only works if there were no letters and every cell has the letter in the tenth position. Is there a symbol, like # that will allow the letter to be used in my custom format? Thanks.
Hello!
You cannot apply number format to text. Try this formula:
=LEFT(A1,1)&"-"&MID(A1,2,2)&"-"&MID(A1,4,3)&"-"&MID(A1,7,2)&"-"&MID(A1,9,2)&"-"&MID(A1,11,5)
HI Dear expert community,
I'd like to get your advise, is it possible to add more that 1 conditional under Format Cell /Custom, I have this:
[=1799.99]"Door";[=1200]"Box"
but would like 2 add another to variables, I tried this, but didn´t work:
[=1799.99]"Door";([=1200]"Box";([=1800]"Side";[=2400]"Top")
[=1799.99]"Door";[=1200]"Box";[=1800]"Side";[=2400]"Top"
Could anybody give a hint?
I appreciate your kind support :D
Hi!
Unfortunately, this is not possible.
Very Detailed Article. No Words to appreciate....
Very Helpful...
Thanks for such a long article...
Hi i am aware of the , and ,, rounding to thousands an millions in number formatting.
However is there a way to round to the nearest tenth or hundreds in number formatting? I would like to preserve the original value in the cell (instead of round/up/down)
e.g. value 2431 display as 2430 (to nearest 10）
Display as 2400 ( to nearest 100)
Thx.
Hi!
The format can show the number in tens. But it cannot round to 10.
So which format code should I use to SHOW in tens?
Hi!
If you are using this custom format
#0"."0
then the number 158 will be shown as 15.8
how can I split MP53HA2219 to MP 53 HA 2219 (needs to insert the space) {format will be same always}
I solved the problem partially by holding the Alt key and entering 010, however, if I reduce the wide column, I have XXXXXXXXXXXXXXXX instead of the value...
hello Alex
I need to wrap the text in custom formatting as the column becomes too wide and spoils the excel sheet, is there a way to do that.
this is what I have:
"TOTAL PICK AISLE LOAD ("#,##0"kg/m2)"
The column width is for the full text, I need to some how wrap it with in the custom formatting not from the tab as it does not work...
Hi,
I am using Excel with the region set to India. The numbers display as 1,00,000 by default. I want to change a few cells in this to display in the million 1,000,000 format.
The default setting of #,##0 does not work and it displays as 1,00,000. Looks like putting in this format defaults to the default setting.
Any suggestions? I do not want to change the region.
Thanks.
Hi!
The default format uses the regional settings.
Thank you so mush for this complete and useful tutorial. What is the difference between #,##0 and #,# for thousand separator?
Thanks, this info really helpful :)
Hi, I'm trying to format my columns to where I type.25 and it turns to 0.25% but I keep getting 25.0% can anyone help please?
Hello!
Use custom number format
0.00"%"
I hope it’ll be helpful.
Hi Alexander,
I need to transfer this format 1.234-567.8 to a formula that I can use in Format cells Custom type. I then want to apply the custom formula to several cells with 8 digit numbers (e.g. 12345678) at once.
Can you help me provide me with such a formula?
/ Robin
Hi!
Digit grouping symbol is set in Local Settings in the Control Panel. However, it is not possible to use several different separators. Also, it cannot be the same as the Decimal symbol. I'm really sorry, we cannot help you with this.
Hello, great article. I want to have different currency formats, sometimes it needs to be comma for thousand separator and sometimes period, how do I do these number formats?
Thanks!
Hello!
The thousands separator is set in the Windows Control Panel under Local Settings. There can only be one.
how do we add colors with conditional formatting for more than 2 conditions. As i tried only 2 conditions are allowed in format axis eg.[Red][<=200]General;[Magenta][<500]General; how do we add more colors for different intervals?
Hello!
You can add any number of colors and conditions using conditional formatting.
I recommend reading this guide: Apply several conditional formatting rules to one cell
This should solve your task.
How do you convert custom format results to text? Can it be done in any simple way?
Hello!
Please have a look at this article Excel TEXT function.
I hope it’ll be helpful.
Hi,
Great tutorial.
Is there a way to write a custom number format that removes decimal places while preserving the remaining syntax of a cell's accounting number format (including currency symbol and alignment, where the currency type is unknown?
I am trying to create a conditional formatting formula that removes decimal places after a number exceeds a certain value, independent of whatever accounting currency style is in effect. I can't use VBA for this application.
Thanks,
Lloyd
Hello!
This format does not show the decimal part of the number if the number is> 99.
[>99] $#,##0;$#,##0.00
Pay attention to the following paragraph of the article above — Custom number formats based on conditions.
I hope I answered your question.
It looks like there is no way to re-format decimal place while preserving currency symbol without knowing the active currency symbol. I wish nested conditionals was allowed.
I'll restrict to default currency symbol.
Thanks for the help.
Hi!
I have a number consisting of nine to eleven digits, for example 12345678000.
The first two digits have one “meaning”, digit number 3 and 4 have one "meaning" and digit number 5 to 8 have one "meaning". To improve the readability of the number I would like to group the numbers, with the groups separated by blanks/a space.
I would like to show the number as follows: 12 34 5678 000
I have tried number format ## ## #### ###, that works perfect as long as there are exactly three digits after digit number 8, for example 000 as above
It there are two digits after the eights digit – the number is for example 1234567811 it is grouped like this: 1 234 567 811
If there is one digit after the eights digit –the number is for example 123456781 it is grouped like this: 123 456 781
What can I do in order for it to start counting the characters from the left and always grope the two first digits together, then the next two and then the next four, followed by the rest?
Hello!
You can apply this format if you convert a number to text:
=LEFT(A1,2)&" "&MID(A1,3,2)&" "&MID(A1,5,4)&" "&MID(A1,9,20)
I hope it’ll be helpful.
Just wanted to let you know that while you haven't responded to my question (see above, March 9), this response to Anders provided a viable solution. So thank you!
Hi,
The thousands separator is set in the regional settings of Windows. But you cannot make the first separator "/" and the second " ". To do this, you need to convert your number to text and insert the necessary symbols into this text.
Can a cell be referenced in custom formats? I want to combine the number in cell A4 with text when a date is entered in that cell.
Hello!
Unfortunately this is not possible.
I didn't think so. Thank you!
45 x 48
i want to do = 45 x 48 ft
Is there a way to format using slashes and commas?
Basically, I'm trying to format something with a 2 digit code, then a slash, then a 6 serial number separated by a comma.
Here are some examples:
16/123,456
11/955,416
22/546,000
10/000,000
I tried "00\/000,000" but that gave me a comma before the slash: "16,/123,456"
Any thoughts?
Thank you
I need excel number format which shows fraction if there is any fraction value. If fraction value zero value should be appear without fraction.
For eg: value is 25.50 it shows 25.50. if value 25.00 it shows 25
Hi,
Read this comment.
Hi,
Thank you for this. However I am stuck on something.
I have numbers in the millions in a range of cells:
Ex: 3,704,268.16 or -4,440,823.49
I am using a format to round to the 000's
#,###,; (#,###, )
Which gives me a result of
3,704 & (4,441)
which is perfect
but what's driving me crazy is when I have a formula within the cell and it nets a very low negative number I get this as an result: ( ).
Is there any way of removing those brackets? when I run into a situation like that?
Thank you in advance. I love the article its super useful!!!
Hello!
Try this conditional format:
[>-1000] #,; [<=-1000] (#,###, )
Hope this is what you need.
I want to write 22 and excel to show 0.22
Hello!
Try a custom format
0","00
I hope it’ll be helpful.
Thanks for sharing. This is super useful. Related to the "Indent" section, as it relates to text, this really isn't an "indent". At at least in doesn't behave (in 365/v16) the way manually adding indents does. It just adds space on the 1st line of wrapping text. Not to all lines.
Hi
I have a question about custom format cell , Which codes or characters in custom format cell should I use that when I copy a formula from another cell and past in formatted cell then the result of formatted cell (displayed number) does not change and main as before pasting?
Hello!
The information you provided is not enough to understand your case and give you any advice. Please describe your problem in more detail. Are you copying just a formula or an entire cell? To preserve the formatting, you need to use Paste Special - Formula or Value.
hello, thanks for your help
Suppose we have a table where the third column is the product of the first column multiplied by the second.
a b a*b
2 3 6
6 3 18
5 4 20
3 5 15
3 6 18
2 7 14
Then we change one of the cells of the third column using the custom format cell and the character “apple” as follows.
a b a*b
2 3 6
6 3 18
5 4 20
3 5 apple
3 6 18
2 7 14
Now if we copy the second row cell of the third column and paste it in the fifth row cell of the third column, the word apple changes to the number 15.
My question is what character instead of “apple “ to use in the custom format cell that does not change the word apple after copying the second row of the third column and pasting in the fifth row of the third column?
Hello!
Your explanations are not very clear to me. But I can assume that you want to print the word before the number written in the cell.
Use the format
"apple " ##.##
Pay attention to the following paragraph of the article above Add text to number formats
Hope this is what you need.
Thank you for your guidance.
But that was not what I meant. In fact, I might ask the question, is it possible to define a format for a cell that contains a number that when we paste a formula into it , the number does not change in the cell ?
Hi,
A cell can contain either a formula or a number. The formula calculates the number to be displayed in the cell.
Hi,
In my country its not thousands(3 decimal places) , Mill (6 decimal places) and Bill(9 decimal places).
Its thousands(3 decimal places), Lakhs (5 decimal places) and Crores (1 decimal places).
So how to place the decimal place through custom formatting to convert it into Lakhs or Crores (Not at the same time obviously.
I want to display like below
10 to be displayed as 000000000000010
-55 to be displayed as 000000000000-55
12.22 to be displayed as 000000000012.22
-23.33 to be displayed as 000000000-23.33
Hello!
You can use custom number format
0000000000000##.##;0000000000000##.##
Hope this is what you need.
How do I display 4 left-most digits? I.e.
123456789 should be displayed as 123400000
0.123456789 should be displayed as 0.1234
0.0001234567 should be displayed as 0.0001234
Is this possible? Thanks.
Hello!
To extract the first 4 digits (other than 0) from a number, you can use the formula
=LEFT(A1,MIN(IFERROR(FIND({1;2;3;4;5;6;7;8;9},A1,1),99999))+3)
I hope my advice will help you solve your task.
Good afternoon
I have been studying custom number format but I would like to know what I am doing wrong in this exercise, please.
A number greater than 1000, such as 52411, will be shown as $52 and a number less than 1000 will be shown as $0, also I need to have a custom format for negative numbers between parentheses.
I tried to apply this format: [>=1000]$0, ;[<1000]$ "0" and it works for positive numbers, but when I try to format negative numbers Excels displays me an error message. What can I do to apply this format to negative numbers?
Thanks
Hello!
To display numbers in thousands, use the custom format:
[>=1000]$#,##0, ;[<1000]$"0"
But in this formula, you cannot set a special format for negative numbers. Excel only allows one condition. You already have two.
To show negative numbers in parentheses, you can apply the format
#,##0;(#,##0;0
I hope it’ll be helpful.
I have whole numbers and some with a single decimal points. If I use say ##0.# if get "6." and "6.5", but would like "6" not "6." Is this possible to remove the "." with just formatting?
Hello!
If you want to show the fractional part of a number using formatting, then you need to use the Decimal point. Unfortunately, it will always be displayed.
00, what it's means?
Is it possible to use some mathematical operations to format a number? For example, I want to display a year (e.g. 2012) as both the year and the number of years since a certain event (e.g. birthdate). So a date in 2012 would display as something like "2012 (27 years)", where the 27 is calculated as 2012–1985. I know how to do this using Excel formulas, but I'm trying to display this on a graph and want to maintain the numbers as dates, not text, so Excel knows how to display them properly.
Hi,
Great guide! Now that I have the format I desire, I want to apply it to excel charts. However I don’t get that to work, neither by pointing at already formatted values nor pointing at regular values and applying the format on the axis/label. Any idea why this doesn’t work?
Thanks!
//Patrik
Hello!
First of all, great guide, thank you!
I encountered with the problem with my custom format #,##0.00 and numbers that have decimal places in format x.0x. Meaning that once I have number e.g. 1234.05 it fails and displays number e.g 962.587.00
Do you have any idea what caused it and maybe how could I fix my custom format?
Thanks!
="Total value A+B+C+D = Rs. "&L55&"/-"
How to add comma in amount in sentence
04/01/5021
How i can change this into 04-01-5021 format
In a column is a large number of numbers
How do I color each number with a bold font, a different font color, and a different border from the other, with repeating the format if the number is repeated
Note that the numbers from 1 to 3000?
Thank you
Hello!
Please check out this article to learn how to highlight duplicate cells in Excel.
I hope it’ll be helpful.
Hello!
how to create format cell by refer other cell
example, product "apple" code "####.##" if the product is different "banana" code "#.####" in the same cell that need to key in
Hello!
You can use conditional formatting with the formula
=A1="apple"
When setting the cell format conditionally, use the "Number" tab. Specify there “####.##”.
I hope it’ll be helpful.
The separators are not correctly separating the number. Not sure how to fix it. I tried formatting it but it kept on putting the separator in the hundreds place. For example: 1234567.89 when I format this number to include separators it's showing as 123,45,67.89. How can I fix it? I checked the setup also (File>options>Advanced) but wasn't able to fix it. Please advise.
Hello!
Use custom number format
# ### ###.00
I hope my advice will help you solve your task.
how can i write "ABCDE-1234Z" when I type 1st five letter is word then hyfan then four letter is digit and again last letter is word??
Hi,
If the cell contains at least one letter, then it means text. The value in the cell is left-aligned.
I have data in IT2019111515691204569 and need to change into text format as
IT-####-#####-##########
Please help..
Hello!
It is impossible to solve your problem by formatting. You can use the formula
=LEFT(H1,2)&"-"&MID(H1,3,4)&"-"&MID(H1,7,5)&"-"&MID(H1,12,10)
Thanks A Lot.........
I'm trying to add and "*" before and after a number "*"####"*" which works (ie *1234*) but my number has a - in it like 1234-6 when I add the - the * goes away. How do I format this? This is what I'm looking for *1234-5* with no spaces. I don't want to format without the - because the number after the - could be a 2 digit number and the format would only take the last number, like this "*"####-#"*". My number already has the - in it.
Hello!
You should understand that 1234 is a number and 1234-5 is text. They need to use different formats.
For text —
\* @ \*
or
\*@\*
For number - replace the @ symbol with #.
You can also convert your numbers to text.
I hope my advice will help you solve your task.
Hi,
My actual text is "Scheme85" but wanted to appear as "Old Scheme85" without changing actual content of the cell, just for visual purpose...
Is there any format which can convert my actual text into customized display without changing actual context of the cell?
Hello!
If I understand your task correctly, you can use custom text format
"Old" @
I hope my advice will help you solve your task.
Hi to all,
I need to use this kind of format for example: (12.5 = 12 + 500 ) it is possible?
Hello!
You have written a mathematical expression. Moreover, this expression is mathematically incorrect. What does the cell format have to do with it?
We do something similar with surveying and station numbers. For example, a location 13,758 feet from the beginning would be labeled as "13 + 758"
To make a format like that in Excel, use 00 + 000
For the question you had above, just write the 12.5 as 12500 and it'll display as 12 + 500
The biggest issue with what you wrote out is the decimal point. I don't know of any way to make that disappear. You could do 00. + 000 which would display your 12.5 as 12. + 500 That "." is annoying though.
If you have a long line of numbers formatted as the 12.5, you could put a column that multiplies that column by 1000 and then copy that and paste the value into the original column to overwrite the 12.5 with 12500. (To paste the value hit control + alt + V and select "Values" (or press v) and it'll paste the value rather than the formula)
Hope it helps!
i have a doubt if i want a variable preceding a constant such as ###/20-2021 in this case ### is a variable like 001 or 002 or ....... 1999 , etc and " /20-2021 " is a contant . when in drag the cell only variable should change.Kindly Help me.
Hello!
If I understand your problem correctly, then you need to combine the variable and the text "/ 20-2021". I recommend that you check out the tutorial on how to combine text in a cell.
I should have done more experimenting. You can take out the blanks between format conditions. This Code allows for the minus sign up down to Millions:
[>999999] $#0.0,,,”B”[>99999]$#0.0,,”M” ;[<999999]$-#0.0,,,”B”[<99999]$-#0.0,,”M”
And it is 81 characters in length. If your number range stays below Billions, then simply change out the 'B' to 'M' and 'M' to 'K' etc. In other words, you can handle two different 1,000 multiple ranges of values with this Format Code structure.
CORRECTION. Excel limits the Format Code to 83 characters, not the number of 85 I posted earlier.