Because it’s buried deep in the cell formatting menu and has very little documentation, Excel’s custom number formatting is probably its most underutilized feature. Understanding and mastering this feature can greatly enhance the Visual Design of your Excel workbooks. The key benefit of custom number formatting is:
It allows you to change the appearance of your data without actually changing the data value
Please keep in mind that this is different than conditional formatting which, while allowing you to change the font, font size, text color, and highlighting, does not allow you to change the actual data that appears.
People who don’t understand custom number formatting will often go through the process of changing their data values rather than just the presentation of those values.
For example, consider a user who wants to show 1,000,000 as 1.0 M. Instead of using formatting, the user decides to divide his value by 1,000,000 and concatenates an “M” to the end of it. In doing so, not only does he add an additional step in his model, but such a step is also rarely documented, which can create confusion if the work needs to be handed off to another person.
An even worse case if is the user decides to remove the link between the display value and the actual value altogether. He or she simply hardcodes the value by typing in 1.0 M. At that point, any changes made to the original 1,000,000 value will not translate through to what is displayed. Once you master the custom number formatting syntax, you can avoid both of these issues. Custom formatting is like having your cake and eating it too; it allows you to improve the appearance of your outputs while still maintaining the integrity of your data inputs.
To get a sense of how custom number formatting works:
Select a cell and make it “Accounting” format using the home section of the Excel ribbon
Hit CTRL + 1 to open up the Format Cells menu
Now in the column of formatting options, Select – Custom
The Custom Number Formatting Syntax
The first thing you should notice is that all custom number formats follow a basic syntax:
<Positive Values> ; <Negative Values> ; <Zero Values> ; <Text Values>
Between each of the semicolons is where you define the formatting for each of the conditions specified. Please note that in some special cases, these conditions are not relevant (ie. date and time formats) and that you don’t necessarily have to specify the condition for zeroes or text values.
Accounting Format Adjustments
In this view, you can also see all the custom formatting adjustments that go into creating the Accounting format. (This is also a good exercise if you like the Accounting format, as I usually start with Accounting as a base before I make custom formatting adjustments.)
Now to explain what each of these adjustments do:
| Format | Description |
| _( | Adds a left indent to the cell, so it does not ride up against the edge; the underscore value tells Excel to create a space equal to the character following the underscore |
| $ | Special character that will show up in your output without the need to add quotation marks within the syntax |
| * | (Please also note that the code indicated here is both an asterisk and a blank space) The asterisk tells Excel to repeat the subsequent character until the width of the cell is filled; this is why the accounting format has the dollar sign aligned to the left, the number aligned to the right, with as many spaces as necessary in between |
| #,##0 | Displays commas in between every three digits, for all values exceeding three digits |
| 0 | Digit placeholder that displays insignificant digits (ie. digits that are zero) |
| 0 | The zeros trailing after the first zero indicate how many decimals places you want to show; even if the final digit is zero, it will be displayed in this format |
| _) | Adds a right indent to the cell, so it does not ride up against the edge; the underscore value tells Excel to create a space equal to the character following the underscore |
| “-“ | Displays a text value; anything between the quotation marks will be shown |
| ? | Digit placeholder similar to 0, but does not display insignificant digits; if the value is zero, it will just be a blank space; in accounting format, because it is in the Zero section of the syntax, it will always appear as a blank space |
| @ | Placeholder for text |
Theoretically, there are an infinite number of adjustments you can make the using custom number formatting. Rather than trying to include all of them, I will focus on explaining the most common and most useful implementations of this feature. For the most part, I will try to leave out scenarios that can either be typed in manually or created through one of the default number format categories.
Round by Thousands
0.000,,
The most common use of custom number formatting is to round large numbers greater than 1,000. For example, assume we want to display 1,562,000 as 1.562.
Each comma rounds three decimal places; therefore “,” puts your number in thousands “,,” is in millions and “,,,” is in billions, etc. In front of the comma, we indicate how many decimal places we want to show. Note that you typically want to replicate any adjustments you make on the positive side with the negative one, in case your value switches signs. Don’t forget to include negative notation for the negative number syntax – either a negative sign or a set of enclosed parentheses.
Making this adjustment is especially helpful for charts, where a long number can add unnecessary space to your vertical axis.
Add a Unit Value
“M”
In the example above, after you’ve rounded your value, you may also need to add a unit indicator to the end of your new value. To do this, we simply add the text of the new value within quotation marks in the custom number formatting syntax.
Please note, that in this newly formatted cell, I can type whatever numeric value I want without typing “M” at the end of my entry. It will still show up in the same format as 1.562 M.
Positive Numbers Green / Negative Numbers Red
[Green] ; [Red]
This format is useful when you have a dynamic model that will spit out both positive and negative output numbers. In this situation, because we’re only changing the color of the text, not the values that show up, this change can also be made using Excel’s basic conditional formatting.
Add an Indent
_)
The accounting format already adds indents on both sides of the cell for you. Many people like to indent their cells so that their values don’t ride up right against the cell border.
Turn Zeroes into Dashes or Blanks
“-“
The accounting format already turns all zeros into dashes.
Making this adjustment is very helpful when you have a large table of data with both zero and non-zero numbers. Turning the zeroes into dashes makes it much easier to see where your non-zero data points are.
Using blanks for zeros is also helpful when labeling dynamic graphs. You could delete the values that are zero, but if the graph values were to change, you’d have to add those labels back every time. Custom number formatting allows you to automate this process.
Add Parentheses to Negative Percentages
[Red](0.0%)
While Excel 2010 has a format for negative values with parentheses in both the default “Number” and “Currency” categories, it does not have one specifically for negative percentage values. We can use custom number formatting to create our own. Anytime you show negative values with parentheses, you should also add an indent to the positive value. That way, positive and negative numbers line up when stacked on top of each other.
Show an Error Message
“ERROR”
Since custom number formatting allows you to display pretty much whatever text you want, you can leverage it to display error messages when the situation applies. For example, assume you have a model that should only output positive numbers. Anytime a negative value appears, you can have it just display an error message rather than the negative number.
Show Leading Zeroes
000000
Excel’s default formatting cuts off leading zeroes. Leading zeros are usually necessary for any primary key codes that use them.
Display a Date or Time
When choosing a date format, you should always start by picking the standard date format that’s closest to what you want and then make adjustments in the custom formatting option. Below are common formatting designations for dates and times.
| Format | Description |
| m | month as a number with no leading zeroes |
| mm | month as a number with leading zeroes |
| mmm | three letter month abbreviation |
| mmmm | full name of month |
| d | day as number with no leading zeroes |
| dd | day as number with leading zeroes |
| ddd | three letter day of week abbreviation (Wed) |
| dddd | unabbreviated day of week text (Wednesday) |
| yy | two digit year |
| yyyy | four digit year |
| h | hour as number with no leading zeroes |
| hh | hour as number with leading zeroes |
| m | minutes as number with no leading zeroes |
| mm | minutes as number with leading zeroes |
| s | seconds as number with no leading zeroes |
| ss | seconds as number with leading zeroes |
A Note on Conditions
Custom number formatting also allows you to add conditions to your formatting. However, you’re probably better off doing this using Excel’s conditional formatting feature, which is better designed to handle the task.
Excellent article!
I’ve been searching for articles that explain the number formatting feature in a very easy to understand format (including examples too).
This should be high up in the google search whenever someone find about “number formatting”.
Greetings!
Great article!
Can i get a little help when it comes to this problem please? I have in a cell the fallowing formula =G3-G3*E3 that shows as 999.99 for example i want the number to be 31 font size and the . and the decimals to be 16 font size. I swear to god i searched all internet for this and cud not find a solution.. help please!
Tank you!
Hey,
thanks for your great article to number formatting.
But I can’t accomplish what I want to do.
In germany the “,” is used as decimal separator and “.” used as separator for thousands.
I try to define a number format, which will display a “.” instead of the “,” for a number like “1,253”.
Thanks for your help.
Tobias
Thank you! You have finally demystified custom number formatting for me. I needed the typical accounting format, only in thousands with the letter K. It works now! This is a huge timesaver.
Many thanks, this is what I was looking for, and very helpful.
How do you enter a double quotation character (“) into a format? (Inches)
Thanks for the info.
is there a way to custom format a cell that will create a single bar charts with the % trailed by a letter or group of letters (different for each bar).
(significance)
Example: 45ABC 37CDE 32CD
Thank you! Very helpful – exactly what I needed. 🙂
Great article and very useful, however, I need to expand on this and format for Thousand (“K”), Million (“M”) and Billion (“B”). Is there a way for excel to do this for me?
Thanks,
asume the numer is in A1
=IF(A1>=1000000000,TEXT(A1,”$ #.,,,”)&”B”,IF(A1>=1000000,TEXT(A1,”$ #.,,”)&”M”,IF(A1>=1000,TEXT(A1,”$ #.,”)&”K”,TEXT(A1,”$ ###”))))
Great article, thanks. I’ve been struggling with a formatting issue and I’m hoping you can help me. I get info in formats below (letters -2 to 4- and numbers). I need to format this so that there is always a space after the letters and the numbers are always in 000.0 format. Any ideas? Below an example of the data I need formatted. Thanks in advance for any suggestions!
OH976.0
LMR 652.0
LMR652
OH 479.1
ARK 445.0
AR448
CW80
LMR 230.5
LMR113
CW277
GIWW 485.1
I have a line graph and need to multiply the value by 1000. For example I have label of 4.8. I need it to be 4800. I tried typing *1000 in the custom number box when that did not work I searched on the internet and found your page.
Is it possible to perform a calculation on data labels or will I need to type them all in manually?
Is there any way to format a number to have 4 digits between commas (instead of the default three digits)? For example: 1234,5678,9012
Also, is there a way to override the E on very long numbers? I need Excel to recognize a number having 11 sets of three digits… For example:
111,222,333,444,555,666,777,888,999,000,111 without turning it into 1.11E+32 or something like that.
Thank you in advance for your help
go to format cell, select custom then in type box put this
####\,####\,####\,####
Thanks for the very helpful tips!
For the number format, can it be set in 10,000 instead of 1,000 (in China number in 10k is more popular)?
For example, 1,234,400 presents as 123.4?
Thanks
Alvin
Is there a way of making a word have a numerical value so that on a spreadsheet it is shown as a word, however in a sum it acts as a number.
eg:
on the spreadsheet it shows as “amber” but in a sum it shows as “0.20” if printed or viewed
Thanks for the article.
I encountered a Excel number format like the following
[$-10C09]#,##0,;(#,##0,);-;
What’s the [$-10C09] for?
Hi all:
I am trying to delete ‘Custom’ number formats for cells in 1 specific excel workbook.
When I do so – the following error message shows up.
“You cannot use this command on a protected sheet. To use this command, you must first unprotect the worksheet (Review tab, changes groups, unprotect sheet button) You may be prompted for a password”.
This workbook isn’t protected to begin with.
When we navigate to the ‘Review’ tab within it – there is an option to ‘Protect’ the workbook, but not unprotect it as indicated by th error.
So, this workbook must already be unprotected.
Any clues about why this is happening and how I can fix this?
Hi Uday,
Sometimes when that happens to me, I find that the “Design Mode” button is pushed in. I have to take it out of Design Mode (under the Developer tab), then go to the Review tab and work with the Protect/Unprotect buttons.
I hope this helps!
Hi…any suggestions how I format a cell for the Massachusetts learner’s permit number? The format is:
s55-55-5555
That’s a small “s” in front. I hope someone is out there to help! Many thanks.
Thanks, wonderful info. Very clear en succinct explanation. Without tedious irrelevancy.
The highlight was the infi about how to suppress the thousands in the formatting.
Pete
Great information. Thanks!
I just remembered that I once ran into the trouble of aligning a row of number range which were separated by an en-dash. For example: 2–32 in row 1; 0–12 in row 2; and so on. I wanted to aligned them in a way that they were all centered at the en-dash, i.e. “–”. I was never able to figure how to do that.
Any insights?
hello, i have a excel char
In my Y-axis i have range numbers 0,1 to 1,0 and i want to change the , to a .
how do i do that?
@Tiago: You can go into the Excel Options to do this. Follow the steps mentioned below and you should be good:
File >> Options >> Advanced >> Editing Options >> Use System Separators
You can change them to whatever you like in there. Hope this helps.
Cheers
Arijit
Great information. Given that custom number formats are pretty hard to assign, I created a number format macro that allows a quick keyboard shortcut to assign your most common number formats. It also allows you to quickly create some of these custom number formats such as millions with “M”, negative percentages with parenthesis, and ppts or bps (percentage points or basis points). It’s the first macro in Dan’s Macros Productivity Suite. Hope you find it helpful!
Dan
I frequently have to input 22-digit numeric strings into Excel. The string is a combination of 7 fields. I would like to separate the string into the 7 fields using dashes via a custom format. For example:
1001015951525100330000 to 10-010-15-9515-2510-0330-000
I run into the following problems:
1) If I make the cell format NUMBER and use a custom format of 00-000-00-0000-0000-0000-000 or ##-###-##-####-####-####-###, excel changes my input of 1001015951525100330000 to scientific notation (1.0010159515251E+21) which changes the value of any number after the 14th to 0 thus rendering the last 8 digits of my 22-digit string meaningless. Well…they have meaning…so, no good.
2) If I make the cell format TEXT to correct for the scientific notation problem, the custom format options in Excel for text are extremely limited. I haven’t been able to find how to add dashes to text using a custom format.
It seems like my scenario runs into two competing issues within Excel. First, Excel’s automatic conversion of long numbers to scientific notation (can’t turn off). Second, Excel’s limited custom formatting of text.
Any ideas?
Thanks in advance!
Have a look at this KB entry and see if there’s a solution in later versions of Excel. Excel can handle custom formatting with numbers up to only 5 digits. Formatting as text will let you insert up to 1024 digits in a cell, but then custom formatting doesn’t apply, as you’ve discovered.
https://support.microsoft.com/en-us/kb/269370
good luck.
sorry, up to 15 digits.
A very basic yet effective guide! I have enjoyed learning from this article… and just what I needed exactly.
Thank you.
I have a spreadsheet with columns that display latitude and longitude that I need to import into ArcMap. In order to do that I need them to have up to three whole numbers with eight decimal places. The numbers as they appear now are single ten digit numbers with no decimal places. Is there a way to format the existing numbers to a XX.XXXXXXXX format? I’ll also need to make all the longitude values negative.
11,111,000,000,000.00
As per the above example shown requesting you to pl guide how to format the numbers by putting comma after 3 numbers as shown above since the same is not getting formatted in my excel sheet and shows as 11111000,000,000.00. Please guide
I m using Excel 2010
how to 000,000.00 format
help
Hi,
When I use a formula, =CONCATENATE(DAY(MID(A33,5,LEN(A33)-4)),MONTH(MID(A33,5,LEN(A33)-4)),RIGHT(YEAR(NOW()),2))
the result, in my example, is 91115
I used 000000, as the special format, hoping to see 091115. It didn’t work
I had to copy the cell and paste it back into the same cell using the “value” paste option, and then press “Enter” for the special format to work.
Is there a better way to do this?
How Do I Format A Number To Cut Off The Thousands but not round?
4.157.682
4.157.000
Hi Matej,
I assume when you say not round, you mean you don’t want it to round up? If that is the case, you can just use Excel’s ROUNDDOWN function to prevent Excel from rounding. I’m not sure you can do this in the formatting interface, so you may need to just divide your number by 1000 first, then use the ROUNDDOWN function over that value.
Best,
Matthew
Hi,
Is it possible to round off a number still keep the decimals using Custom Format. Like when i input numbers 51,595.20 or 88,626.89 it should automatically change to 51,595.00 or 88,627.00
Please help.
i need to make a custom format that shows an underscore character, when i use underscore it leaves a blank.
Hi Rob,
To show the underscore character, you need to put quotes around it, like this
“_”
Let me know if that works.
Best,
Matthew
I have following requirement:
15 should be formatted as 15, NOT 15.00
14.3453453 should be formatted as 14.35
12.1 should be formatted as 12.1
0 should be formatted as blank
please help and thanks in advance
I was looking for some help on how to paste special from the formula =Today() to a correlating pivot table that is separated by days of the week (DDD) but when the =today() formula is recorded it messes up my pivot table. When I try and past just the values It pastes a strange number (42389) instead of the Wed it ? HELP ME PLEASE!
Hi Brad,
Would need a little more detail to help you with this, but I believe you should be able to format values in a pivot table. Just click the 42389 and change the format of it to a date, after you’ve done the paste special values. It should flip to 1/20/2016 once you do that.
Let me know if that works.
Best,
Matthew
I have a spreadsheet with data exported from SAP. In the same column, I have a bunch of time values either custom formatted in hours or custom formatted in minutes. So in the same column, I could have 7 H (7 hours) or 5 MIN (5 minutes). The problem is I want to add all these time values but need to convert either the hours to minutes or the minutes to hours but can’t do it because the value of the cell is just a number. Is there a way to incorporate the custom format for hours or mnutes in a formula so Excel knows a number is either an hours value or a minutes value?
Hi George,
I’m pretty sure you can’t do this with formatting alone. I think your best bet is to create a separate cell, with IF logic that converts value into the lowest common denomination, which I think in your case is minutes. For example, if the rightmost value of the cell is an H, take the leftmost value and multiply it by 60.
Let me know if that helps.
Best,
Matthew
Hi,
Does anyone know of a way to make cell appear as a “-” a range of values. So if the number is between -0.499 and 0.499, the cell will appear as a “-“? Similar to the turning zeros in to dashes section above but for this range.
Thanks!
Matt
Does anyone know of a way to format that would make any number that is between 0.499 and -0.499 appear as a “-“. Similar to the Turn Zeroes in to Dashes section above but for this specific range. I tried conditional formatting for the range but it still treats the number as a negative and thus appears as 2 dashes “–”
Thanks in advance!
Matt
Dear Matt you have to simply do conditional formatting by using a simple formula.
Suppose the value is entered into cell d2 then type the formula in “use a formula to determine which cells to format” in conditional formatting options
=AND(D2-0.499) and customs format the number as “-“
Hi,
I dont want to round my number.
I have 5,123456789 but it seems 5,000000000
how can I do that?
please help me
Nice post. I learn something totally new and challenging on sites
I stumbleupon on a daily basis. It’s always exciting to read through content
from other writers and practice something from their websites.
Thanks for finally writing about >How to Use
Excel Custom Number Formatting <Liked it!
My brother suggested I might like this website.
He was totally right. This publish truly made my day.
You can not consider simply how so much time I had spent for this information! Thanks!
Hello, everything is going perfectly here and ofcourse every one
is sharing information, that’s actually fine, keep up writing.
Haviong read this I thought it was rather enlightening.
I appreciate you spending some time and eneegy to put this article together.
I once again find myself spending a signnificant
amount oof time both readong and posting comments. But so what, it wass still worth it!
It’s awesome in favor of me to have a site, which is valuable for my know-how.
thanks admin
Thanks a lot for sharing this with all people
you actually knlw what you’re talking about! Bookmarked.
Please additionally talk over with my site =). We can have a hyperlink trade agreement between us
Thanks ffor finally talking about >How to Use Excel Custom Number Formatting <Loved it!
3112706314419387
3112703714503478
3112706314419385
3112712814357331
for the above numbers , i want landing 0s 23, but last digit changes once i make formating
for example it is showing. 00000003112706314419380 (last digit become 0 insted of 7)
someone plz help me out
I am actually thankful to the owner of this web site who has
shared this wonderful post at at this time.
I want replace 0+000 are 0 in excell
I just like the valuable info you provide on your articles.
I will bookmark your blog and test again here regularly.
I’m fairly sure I will be told a lot of new stuff right right here!
Good luck for the next!
Does any one know how to format a cell so that only a portion of the text is shown, eg only the first three letters? can I put this info in quotation marks or brackets and condition the formatting of the cell to only show the text between?
I like reading through an article that can make people think.
Also, thank you for allowing for me to comment!
I am using a drop down box
what i am trying to achieve is if the value 1 is selected specific text will appear if the vale 2 is entered specific text will appear and so on. so far i go to the drop down box select format cells custom
and use this formula [=1]”octagonal”;[=2]”round”;”not selected”;
this works so far but i can not add options for if 3,4,or 5 are entered
please please help or offer another solution.
Hi
What if vertical axis contains small values. e.g. 0.000002
how to show the numbers in micron or nano scale and then enter a label beside axis? something like “display unit” in axis option.
Many thanks
Does Excel have an automatic number format setting as does “.numbers” on Macs? With that setting it shows decimal AS ENTERED, e.g. 1 shows as 1 & 1.0 in another cell shows as 1.0 .
Hello my name is George. I am dealing with an issue regarding larger numbers in a Vlookup formula. Numbers with more than 12 digits are shown in scientific format and it won’t let me to use as a reference cell for the Vlookup formula. I know I can select ‘0’ in Custom format or use the Trim function so all digits will be displayed, but still it won’t let me to use as a reference cell in a Vlookup formula. So here is the question:
How to use numbers with more than 12 digits as reference cells in a Vlookup formula?
Any help regarding the above would be really appreciated. Thanks.
George:
Wrap your reference value source in the TEXT() function apcifying the “0” format. For instance, A1 has your lookup value and TEXT(A1,”0″) is your lookup reference. Excel will treat it as a number when doing the lookup. If you somehow have a version that doesn’t (pretty sure even Excel 98 will do so), then wrap all that in the VALUE() function.
hi folks,
i just want that cell should lead with zero.
192275315510021043
192275315510031817
For the above numbers , I want to put as same, but last digit changes once i.e. 1.92275E+17 . After custom changes it makes 192275315510021000 (last 3 digit become 000 insted of 043) , 192275315510031000 (last 3 digit become 000 insted of 817)
Please help .
################0 works but the issue I found was that the number was converted upon paste. If I paste the number inside the formula bar, the problem goes away.
Great post.
Hi, thanks for all the help, great tips. I am hoping you might help me with the custom format, where i want the content of the cell to display some TXT code, depending on the value of the cell.
So, for example, I am using : [=1]”SI”;[=2]”SO”;” ”
But in case i want to add third criteria, so : [=1]”SI”;[=2]”SO”;[=3]”R”;” ” gives back and error… I can’t figure out what’s the reason, must be some restriction?
thanks
It’s actually a cool and helpful piece of information. I am happy that you just shared this useful info with us.
Please keep us informed like this. Thanks for sharing.
Ask a Question.
I wan to display numbers in an Excel cell in format 999,99,999.99
The middle 2-digit 99, is not a typo. This is how currency is written in India.
999,99,999.00 in words is 999 Crore, 99 Lakhs, 999 Thousand and zero paisa.
Use
###”,”##”,”###.00
Hi, i have a numbers in column AU & AV with %, M & B visible in the cells but not is formula bar.Now i wants to change
% as ” pct”
B as ” bln”
M as ” mln”
can you please help me to do it. I tried replace, substitute, text, left, right functions but no use.
Hi there,
This is my question (help!): when typed in a cell the text (ABC121234), it should format to (ABC-12-1234). If i put (123121234) it shows (123-12-1234), which is correct. but with the first 3 letters, the format doesn’t show correctly.
How come?
Keep the good work. Thanks
Hi,
I need below solution –
If cell value is <=5 then icon should be DARK RED
If cell value is 3 then icon color should be RED
If cell value is 2 then icon color should be ORANGE
If cell value is 1 then icon color should be YELLOW
If cell value is <=1 then icon color should be GREEN
I am trying to format Canadian Postal Codes for example if it is written R0M0X0 I need to have it written as R0M 0X0
Is there a format I can use to do this?