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
I want to display Acre and Guntas to be converted and displayed as 11-03 A-G or 11A-03G instead of 11.08 in number format. what is the formate code for it.
Hi! Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
Is it possible to show decimals if they exists? I mean without separator.
Example:
1.1 as 1.1
1 as 1 (without dot)
#,##0.### keep dot after integer values and it looks ugly.
Hello! Yes, it is possible to achieve the formatting you described in Excel using conditional formatting. Here's how:
1. Select the cells you want to format.
2. Use Conditional Formatting and select New rule.
3. In the New Formatting Rule window, choose Use a formula to determine which cells to format.
4. Enter the following conditional formatting formula:
=MOD(A1,1)>0
Make sure to replace A1 with the actual cell reference in your selection. This formula checks if there is a remainder after dividing the cell value by 1. If there is a remainder (meaning there's a decimal part), the formula evaluates to TRUE.
5. Click the Format button. In the Format Cells window, go to the Number tab.
6. Select the Custom format type. In the Type box, enter the format code 0.##. You can adjust the number of # signs to control the maximum number of decimal places displayed.
The MOD function calculates the remainder of dividing a number by another number. In this case, we're dividing the cell value by 1.
If the remainder is greater than 0, it means there's a decimal part, and the formula evaluates to TRUE.
The conditional formatting rule applies the custom number format only to cells with decimals.
The custom number format 0.## displays a decimal point and up to two decimal places (you can adjust the number of # signs). For cells with no decimal part (e.g., integers), the format code rounds the number to zero decimal places, effectively hiding the decimal point.
This approach ensures that cells with decimals display them as you want (e.g., 1.1), while cells without decimals appear without a decimal point (e.g., 1).
Thank you. Where is the original source of these codes? Is this code a specific programming language?
Hey, first let me thank you for all these excellent ideas and suggestions!
and I apologize if this has already been asked...
I'd like to customize a text field using the double quotation marks (" ") in the format, which is easy enough. (I'm using the format "Invoice #: "@ )
(I'll use Invoice #: P240200109 as an example)
Using that format, the field appears as it should.
My next step is to copy that field and paste it into another database.
However, I'd also prefer to copy the field *without* copying the "Invoice #: " part, (i.e., only copying the 'P240200109' portion)
I'm unable to find a way to do this, other than editing the required cell, and copying the data through the cell/formula bar.
I hope what I'm asking above makes sense.
Any suggestions?
Thank you!
Michael
Hi! If you are using the custom cell format "Invoice #:"@ , the value P240200109 is written in the cell. If you copy this value using a cell reference (e.g. =A2), change the format to Text in the cell with the formula. Or change the format using the TEXT function:
=TEXT(A2,"@")
I hope that the advice I have given you will help you solve your problem.
Is it possible using Format Cell to only show 4 digits on the right?
What I have now is: Y1/1234 and I need only the "1234".
I know it would be easier to create a new column with formula but I can't have more columns on my file.
Hi! Unfortunately, the Excel cell format does not allow you to display only part of the text string in a cell.
Hopefully I did not miss a reply that answers my question as I read through this thread. If I did, I do apologize.
I make reports in one currency and also must provide them in a second currency. I am hoping I can find a way to do this simply thru formatting without having to create separate cells in which to display the results as I currently do. So the desire is to display a number in a given cell after dividing by a second fixed number (ideally pulled from a named cell "Exchange rate").
It seems like it should be doable, but I have been unsuccessful so far.
Thank you and Ablebits for all you do to help us for whom Excel is a necessary tool!
Hi! You cannot use the number format to perform a mathematical calculation.
Thank you for dashing my hopes! :) I will waste not one more brain cell on this and will continue on as I currently do. :)
Great post!
Is there a custom formatting way to present only the first 4 characters of the text value of a cell using @ with some syntax?
WBDK Worlds Best Desktop Keyboard -> WBDK
Hi! With custom formatting, you cannot show only part of the text in a cell. You can extract the first 4 characters as described in this article: Excel substring functions to extract text from cell.
Hi, I´ve trying to format the below but i can´t seem to do it. Could you help me, please?
I want to show:
590000 as 0'59 or 1375000 as 1'38
with the "upper" comma and 2 decimals after it (rounded)
thanks a lot-
Hi! To show a number in millions, you can use this custom format
#,##0.00,, or #,##0.00,, "M"
However, you cannot replace the decimal separator in a number (.), which is set in the system settings of your computer, with a different one (').
I want to convert decimals in to crores
Hi! What is "crores"?
One "Crore" is written as 1,00,00,000; it is equal to 10 million.
Hi! With Excel's numeric format, you can show a number in thousands or millions. For example: #,##0,," M" - in millions. You cannot show a number in tens, hundreds, tens of thousands, or tens of millions.
[>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0
I want to display only after decimal values only. Is it possible in excel custom formating
It is not possible to show only the decimal part of a number using a custom number format.
Wow, what a fantastically-written piece!
I landed on this page as a result of trying to intuit an answer to this:
I’ve been handed a spreadsheet that’s utilizing the $#.00,, \M format. I’m dealing with hundreds of millions of dollars, so this is fine.
[I’ll add here that the formula is being used for a bar chart axis.]
My problem is that my data range has crept from the hundreds of millions into the billions, and I don’t like my axis max (based on my data set) displaying “$1200 M” instead of “$1.2 B.” (Or $1000 M instead of $1 B, either.)
Can I customize the canned format by slipping in somewhere a qualifier such as [>1000000000]… or [>1.0E9]…., such that my axis can ascend, “$100 M, $200 M,…$900 M, $1 B [I would like $1.0 B, actually], $1.1 B, etc.?
Thank you for any help you or anyone can provide.
Now it’s on to your Conditional Formatting page! (This is great stuff here, Svetlana!)
Thank you!
Hi! You cannot use a cell reference in a format template.
I want to insert +1 in a cell. While using add formula/ multiply formula , only number has to be taken but not symbol. Please furnish a solution
Hi! If I understand your task correctly, this article may be helpful: Excel Paste Special: copy values, comments, column's width and more.
##0% _P;(0%)_P;
#,##0_P;(#,##0)_P;;
I prepare Management Accounts, and have just looked behind the formatting of Variance values in a table, and was surprised that I had used a letter P in the formatting of my numbers.
I typically use _) so that my numbers don't abut the right hand column edge but have no idea why sometimes I have deliberately used a P.
Do you have any idea what the purpose of the P is for please ? I must have seen it in a You-Tube video about three years ago !
Thank you !
Hi! The "_" character adds a space after the number. The letter "P" or any other letter in your custom format does not change anything, as it is replaced by a space.
Hi
I would like to insert an "Up" or "Down" arrow in the same column where I get stock market data, data changes every minute.
The number of rows can go upto 300 by the close of market
Thanks
Hi! You can add arrows to a cell using conditional formatting. Use these instructions: Excel Icon Sets conditional formatting. I hope it’ll be helpful. If something is still unclear, please feel free to ask.
Hi;
How can i set a cell to accept just 10 digit number?
)For example, if I enter a 6- or 14-digit number, I get an error that the number of digits entered is wrong!(
Hi! The following tutorial should help: Data validation in Excel: how to add, use and remove. Use settings: Text length - Equal to - 10. Or use data validation formula: =LEN(A2)=10
Hi there,
How do i format a cell to display PRE122, CP8, PP11 as PRE122, CP008, PP011?
Thanks!
Hi! If I understand your task correctly, try the following formula:
=LEFT(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(A1:A10),1)),0)-1) & TEXT(MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(A1:A10),1)),0),10),"000")
For more information, please visit: Extract number from text string. You can also find useful information in this article: How to add leading zeros in Excel.
Hi Alex, Thanks for this information. Very helpful.
I was wondering if you knew any way to display part of a cell via the number format (I can do it via formulas and a helper column, but would rather not). So I have a series of numbers formatted like so:
017.2022.00000096.001
017.2022.00000097.001
017.2022.00000098.001
017.2022.00000099.001
017.2022.00000100.001
017.2022.00000101.001
017.2022.00000102.001
017.2022.00000103.001
017.2022.00000104.001 etc.
Is there any way to have the cell just display the main number? So in the above data, the cell would display:
96.001
97.001
98.001
99.001
100.001
101.001
102.001
103.001
104.001 etc.
Thank you
Hi! Use substring functions to extract a part of text from a cell. Try this formula:
=--MID(A1,SEARCH("000",A1),20)
Hope this is what you need.
Hi!! In excel, I want to place the axes of the graphs with exponentials, such as: 10^3. But I also want the number 3 to appear as an exponential and for the symbol "^" not to appear. Is this possible?
Hi! Use different cells for calculation and for showing.
This is the number 2854-951-2023 & 2854-1231-2023
How may i make 2854/000951/2023 & 2854/001231/2023
Hi! You can insert characters into the text using the REPLACE function. Determine the position where you want to insert these characters using the SEARCH function. For example:
=REPLACE(A1,SEARCH("-",A1)+1,0,"000")
Hi! I have a question regarding using dashes for zeroes. Is it possible to format these dashes? I like using them but they seem a little short. I'd like to make a longer dash or at the very least bold it. Is this possible to do in the number format code?
Hi! Specify the character you want in section 3 format code. See the instructions in the article above. Here is an example:
#,##0;-#,##0;"–"
Hiya, I have a format I need help with, I have cells that I need to enter a 4 digit number and a letter either at the start or end i.e. T4015 or 4015T.
I need a custom format that will allow the cell to display the number but hide the letter. I need the letter in the cell but don't want it to be seen.
Is this possible?
Hi! With formatting, you cannot hide or change the color of part of a cell.
I am using #.##,, "M" to represent numbers in millions but I don't want the decimal point shown on exact millions. For example, 3,000,000 shows up as 3. M instead of just 3 M. Everything else behaves as I want. Is there any way to do this?
Hi! If you want the decimal point not to be displayed for exact millions and displayed for other numbers, use conditional formatting. Use a separate number format for exact millions.
The conditional formatting formula might look like this:
=MOD(A1,1000000)=0
Hello: I would like to apply a format to a cell in which when entering a value it adds the mm suffix and express it in inches, example: 63.5 mm (2.5 in). Is this possible?
Hi! You can use the format to add symbols to a number (e.g., "mm" or "in"). But you cannot use the format to do math operations on numbers.
Hi,
I'm having an issue and I hope you can help me. I'm writing a paper and the reviewers asked me to write the numbers in the charts as "1000" and "10,000".
This means a comma-separated value for a 5-digit number or more and none for less.
Do you have any suggestions?
Thank you
Hi! Read the article above carefully and pay attention to the paragraph: Display a thousand separator.
what is the format crore value with 2 decimal
input
57761870
Output required
5.78 CR
Hi! You can use number formatting to show a number in thousands or millions.
For example,
#,##0.00,," M"
But you cannot show the number in tens of millions as you want.
Hi, Alex! Awesome article, awesome knowledge of Excel. Thank you for posting and answering questions! Kurt
Hi, I am using Excel for mac. I am trying to custom format a list of fractions, such as:
1 1/4
2 3/8
11/16
3 5/8
etc.
When I apply the custom code of # #/# it is not working, the info still comes in as either dates or decimals.
How can I force excel to treat all of these the same and have the same formatting?
Thanks-
Hi! Go to Format Cells - Fraction, and select fraction format that suits you. For example, #" "?/?
How can I see the following format DVO-000. For example it should show as DVO-001 or DVO-010 or DVO-100.
Hi! If I understand your task correctly, try the following custom format:
"DVO-"000
Cell value is DVO123
I want to custom format to DVO-123.
It seemed custom format is not working text string followed by a number
Hi! Your data is text. Therefore, you cannot use the number format. Use the REPLACE function to insert "-" after the third letter.
=REPLACE(C1,4,0,"-")
Hi. How about showing letter and number characters?
How to change the number format from Dr and Cr to + and -
General number is 15
How I found 45 by custom format cell ???
Mohon bantuannya saya ingin membuat format angka
1-2
1-22
Saya mencoba #-##
Tapi hasilnya
-12
1-22
In Excel I have a formula that equals ($3,55,12,059). I want to just show ($35,512,059 )
$3,55,12,059 it's a text, not a number. You cannot format text as a number.
Excellent tutorial. I have a question on the example of text added to custom format. On the example Apples 1 day Shipped in 1 day
I would like to know if it is possible a custom format that refer to the cell location (H2) where is "Apples" such as "Apples are shipped in 1 day" .
Thank you for your help,
Roberto
Hi! You can use conditional formatting with the formula ISNUMBER(SEARCH("Apple",A1)). The custom format cannot contain Excel functions.
In Excel I have a formula that equals ($35,512,059). I want to just show ($35512) so I changed the number formatting to custom $#,##0,_);($#,);--_) now I just want to add a comma so it shows ($35,512)
What's the number formatting that I should use?
Hi! Try to use this custom number format:
$#,##0,;"("$#,##0,")";--_)
I want to center aligned when cell value is 0 (zero), else right aligned. How can it be done?
Hi! The custom number format in Excel does not allow you to use center alignment. Only left or right alignment is possible. To align zeros to the center of a cell, try using conditional formatting.
How to format a number with specific colored text (like red) e.g 2.695 format to 2.7a - the letter a is specific and color red. Thanks
Hi! Custom format for number or text in Excel cannot highlight part of a cell with color. You can select the color of part of the text in a cell either manually or by using a VBA macro. Here is an example of such a solution: How to highlight duplicate text strings or words in Excel cell.
Hello I am trying to change the format of a date on a csv file.
the format I want is yyyy-mm-dd hh:mm:ss.000
I change it through custom type and save csv again, but when I reopen the csv the format of the date is the previous one.
How can I permanently change the type of date to this format "yyyy-mm-dd hh:mm:ss.000" ?
Hi! CSV is a simple text file that does not save any formats.
Save the file as an Excel file, do the formatting, then resave as a csv.
Is it possible to create number formats in a start up file and then use them in any other excel file that i open?
Hi!
Simple solution: just copy your format with the Format by Pattern tool (brush) from book to book.
Difficult solution: create a new book, add your format to it, save the book as a template (xltx extension) to the Custom Office Templates folder. When you create a new file, you will see this template in the Personal section. Create a new file from this template.
how can i set a costum format to have a number like this IR-06-2541-2451-6582-4895-5842-01??
Hi! What you wrote is text, as there are 2 letters. There is no possibility of separating each 4 digits with the number format in Excel. Use the REPLACE function to insert "-" at the exact position in the text string.
How about this:
"IR-"00-0000-0000-0000-0000-0000-00"??"
If you want to ask a question, explain in detail.
Great Explanation of custom number formats. However, there is something I am confused by that may have been overlooked in this explanation. Does Custom conditions make negative formatting irrelevant? For example:
[=1]?" mile";# ?/?" miles"
My mind is still stuck on the fact that anything that comes after the first semi colon is reserved for negative numbers. Is there a hierarchy thing that cancels that rule out.
Also, If you set a conditional format for a positive number does it automatically apply to negative numbers by treating the value as an absolute?
Example: [>=1000000]_($* #,,"M";_($* #,,"M"
This seems to apply to both positive 1 million as well as negative 1 million
Hi! The first and second sections of the default formatting pattern are used to format positive and negative numbers. You can use other conditions in these sections. If you specify one condition, the second section is used for all other values. If you specify two conditions, the third section is used for all other values. For example,
[=1]?" mile"; [<0]-# ?/?" mil";# ?/?" miles"
Thank you so much for your explanation!
Hi
I want to display the letter "k" as the unit of kilograms, and its done with below format without backslash
format" 0 K"
It seems that the use of backslash is not mandatory isn't it?
Hi! Backslash must be placed before characters that are used in the number format pattern. In other cases, you may not use it.
how to format this 117610600000 its 117 billion to millions numbers only? Anyone can help and will appreciate it much. Thanks.
Hi! Please read the above article carefully. Use custom number format #,##0,," M"
I was wondering how to create a column where text is right aligned with positive and negative numbers. I have used this custom format to align the numbers, _*#,##0_);_(* (#,##0);_(* " "-"??_);_(@_) but the text will not align and if I use the indent feature for the text, it does not align properly.
Hi!
Align text to the right as described in this guide: How to align text in Excel.
I could not found this in all the comments but maybe I messed some.
I need do format from a CONCATENATE function with embedded line breaks, 2 numbers (or one of them if the other doesn't exists)
The only part I'm having trouble is the TEXT function format part within IF clauses, as the numbers vary in size.
the numbers should have the following formats for each variable number
9
99
9 99
99 99
9 99 99
99 99 99
9 99 99 99
How can I add multiple conditions to format these #\ ## or ##\ ## or #\ ##\ ##, etc.?
I can set a condition to divide it in two, but I can't divide it into multiple conditions of the same nature [99] [<=999] #\ ##; etc.
Is it possible? Or a work around?
Thanks in advance for any help. I haven't found it any reference or tutorials yet.
Sorry for the typos *missed, etc.
gosh... [>99] [<=999] #\ ##, etc...
Wrong ID of format numbers, sorry
The one I wanti is this one:
9
99
99 9
99 99
99 99 9
99 99 99
99 99 99 9
99 99 99 99
99 99 99 99 9
The other version is easy to format in TEXT function with "#\ ##\ ##\ ##\ ##\ #0" Just have to be care to let the single digit option #\ and enough repeats of ##\ for the highest length number...
Hi!
If I understand your task correctly, to split a number into hundreds (2 digits each) try this custom number format
## ## ## ## ## ##
Thanks for your input. That format provides this (after not the desired format):
9
99
9 99 not 99 9
99 99
9 99 99 not 99 99 9
99 99 99
9 99 99 99 not 99 99 99 9
99 99 99 99
9 99 99 99 99 not 99 99 99 99 9
Is there a workaround this? I tried to split intervals with conditions to aggregate intervals, but It appears one cannot conjugate in the same format filter more than 1 condition of the same type [>x] with [<=xx] etc. tried the & operator also.
I think that your problem can only be solved with a VBA macro
found out a workaround (conceptually, but one could translate it to excel formulas quickly):
evaluate char number of text string, IF uneven format text string "##\ ##\ ##\ ##\ #" ELSE [even] format text string "##\ ##\ ##\ ##\ ##"
Sorry fort not posting the formulas but I don't have to time to search for the english functions names of those I don't know from memory (I'm using PT-PT version).
It followed from your questions that you want to change the custom format of the cell in which the number is written. If you were to say that the number needs to be shown in another cell, this completely obvious solution would be offered right away.
Hi,
I'd format a single excel file (not all mine ) a different international format (decimal separator). I do not want change operating system seetings nor excel settings in the advanced menu . thanks
Hi! If you are not satisfied with these two methods, you can convert numbers to text and use any decimal separator with the TEXT function.
Hello,
I have a large digit of numbers that always starts with 55, and I need help creating a Format Cell that would color in blue (or bold) any numbers after 55 and before the last 7 digits (the numbers in the seven digits sometimes remain the same). Here is my example 5512345673806327, and the numbers to color in blue are 1234567. Thank you!!
Hi!
Unfortunately, this is not possible. You can only select part of the cell with color manually.
I want to add a minus sign to the beginning of a number and have one decimal place. If I enter 12, I'd like it to show as -12.0. All I've been able to accomplish is:
"-"@
It gives me the minus sign, but turns the number into text and I haven't been able to figure out how to add the decimal point.
Hi! Try this custom number format:
-##.0
Awesome thank you!! How was I supposed to infer this?
Hi,
I want to know how can I use auto numbering in my packing list. Such as my packing list should show.
Serial No of Carton
1-2 1
3-4 1
5-7 3
Can I automate the process? it took me while to type the data into the 'serial number' column.
Please help
Hi!
Unfortunately, I don't see a logical sequence in your numbers. If there is such a sequence, you can use the SEQUENCE function. For more information, read: SEQUENCE function in Excel - auto generate number series.
I want my table to out put the standard error with +/- the error, is this possible?
Hi Alex,
This article gives me new insights to do custom format in excel that l think impossible before. Thanks a lot for that.
I am trying to make this format showed in the cells
3 digits number, forward slash, fixed character lNV, forward slash, fixed character DJE, forward slash, character, forward slash, 2 digits number
Example: 123/lNV/DJE/VII/23
Can l write them like these Alex:
###/INV/DJE/General/##
Hi!
Use quotes for characters and try this format:
###"/INV/DJE/General/"##
I hope it’ll be helpful.