How to Use Excel Custom Number Formatting

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

custom_number_formatting0

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.

custom_number_formatting1

Making this adjustment is especially helpful for charts, where a long number can add unnecessary space to your vertical axis.

custom_number_formatting2

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.

custom_number_formatting3

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.

custom_number_formatting4

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.

custom_number_formatting5

Turn Zeroes into Dashes or Blanks

“-“

The accounting format already turns all zeros into dashes.

custom_number_formatting6

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.

custom_number_formatting7

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.

custom_number_formatting8

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.

custom_number_formatting9

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.

custom_number_formatting10

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.

custom_number_formatting11

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.

81 thoughts on “How to Use Excel Custom Number Formatting”

  1. Pingback: How to Learn Excel
  2. 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”.

    Reply
    • 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!

      Reply
  3. 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

    Reply
  4. 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.

    Reply
  5. 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

    Reply
  6. 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,

    Reply
    • 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,”$ ###”))))

      Reply
  7. 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

    Reply
  8. 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?

    Reply
  9. 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

    Reply
  10. 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

    Reply
  11. 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

    Reply
  12. Thanks for the article.
    I encountered a Excel number format like the following

    [$-10C09]#,##0,;(#,##0,);-;

    What’s the [$-10C09] for?

    Reply
  13. 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?

    Reply
    • 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!

      Reply
  14. 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.

    Reply
  15. 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

    Reply
  16. 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?

    Reply
  17. 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?

    Reply
    • @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

      Reply
  18. 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

    Reply
  19. 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!

    Reply
  20. 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.

    Reply
  21. 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

    Reply
  22. 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?

    Reply
      • 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

        Reply
  23. 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.

    Reply
    • Hi Rob,

      To show the underscore character, you need to put quotes around it, like this

      “_”

      Let me know if that works.

      Best,
      Matthew

      Reply
  24. 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

    Reply
  25. 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!

    Reply
    • 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

      Reply
  26. 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?

    Reply
    • 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

      Reply
  27. 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

    Reply
  28. 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

    Reply
    • 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 “-“

      Reply
  29. 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.

    Reply
  30. 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!

    Reply
  31. 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!

    Reply
  32. 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

    Reply
  33. 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

    Reply
  34. 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!

    Reply
  35. 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?

    Reply
  36. 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.

    Reply
  37. 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

    Reply
  38. 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 .

    Reply
  39. 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.

    Reply
    • 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.

      Reply
  40. 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 .

    Reply
    • ################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.

      Reply
  41. 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

    Reply
  42. 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.

    Reply
  43. 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.

    Reply
  44. 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

    Reply
  45. 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

    Reply

Leave a Reply to Lieng Cancel reply