Custom Cell Formatting in Excel – Few Tips & Tricks

Posted on February 25th, 2008 in Charts and Graphs , Featured , hacks , Learn Excel - 189 comments

The other day I had to make an excel sheet for tracking all errors across one of the applications we are doing for our customer. The format was something like this,

Error messages tracker sheet format

We wanted to use a consistent message id format [4 digits: 0001, 0002, ... , 1000 etc.]. Now I do not want to type “0001″ in excel, instead I wanted to type 1 and I want excel to convert that to 0001 for me. So I started looking for a custom cell format and dug a little deeper to understand those. I thought it would be nice to share them to you all.

First take a look at how the cell formatting dialog box – number tab looks like:

Microsoft Excel Cell Formatting Dialog box - numbers tab for custom cell formatting

Now apart from the built in types General (leave excel to guess the data format), number, currency, accounting (uses the separators, () notation etc.), date, time, percentage, fraction, scientific, text there are 2 interesting types of formating.

Special: Used for phone number, zipcode, social security number formats depending on the locale you select. For eg. for US they would be phone number [xxx-xxx-xxxx], ssn [xxx-xx-xxxx], zipcode[xxxxx, xxxxx-xxxx].

Custom: Used for creating your own cell formatting structure. This is a bit like regular expressions but in entire microsoftish way. Any cell custom format code will be divided in to 4 parts : positive numbers ; negative numbers ; zeros ; text. If your formatting codes have less number of parts (say 1 or 2 or 3) excel will use some common sense to find out which ones are for what.

Ok, without further confusion, this is probably how you can use the custom cell formatting feature in Microsoft excel.

how to use Microsoft excel custom cell formats - tips & tricks

Some explanation that you can skip if you already get it

  • For formatting a number [eg. 1] to fixed number of digits [eg. 0001] you have to use 0000 as the custom formatting code
  • For formatting a phone number [eg. 18003333333] to a standard phone number format [eg. 1 800-333-3333] you have to use 0 000-000-0000 as the custom formatting code
  • To fill rest of the cell with a character of your choice [eg. *] you have to use @**(this applies for text inputs)

What are your favorite data formatting tricks? [Also read : Creating cool dashboards in excel using conditional cell formatting]

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

189 Responses to “Custom Cell Formatting in Excel – Few Tips & Tricks”

  1. [...] Krunker | Technology Around the World wrote an interesting post today onHere’s a quick excerpt The other day I had to make an excel sheet for tracking all errors across one of the applications we are doing for our customer. The format was something like this, We wanted to use a consistent message id format [4 digits: 0001, 0002, … , 1000 etc.]. Now I do not want to type “0001? in excel, instead I wanted to type 1 and I want excel to convert that to 0001 for me. So I started looking for a custom cell format and dug a little deeper to understand those. I thought it would be nice to shar [...]

  2. Swati says:

    Many Thanks. This is really informative. I want to know how a program can read”contents” of a Custom cell. WRT the eg. above, if the Value After is 0001, when a program reads the value, it reads it as 1 instead of 0001.

  3. Chandoo says:

    @Swati – you are welcome. Yeah, you are right, the cell format changes the value at UI level alone, when you access the value from formulas / vba / scripts the value is still what you have entered (ie 1 in your case)

    Welcome to PHD, enjoy :)

  4. Swati says:

    Thanks. So in case, I want my program to pick the value the way it appears on the UI, how can that be done?

  5. Chandoo says:

    @Swati … wow, thats a tricky one.

    You can use the following function:

    Function fetchValue(incell As Range) As String
    fetchValue = incell.Text
    End Function

    Just add it to your workbook’s VBA code in a module and start accessing it from code. I havent tested it extensively, but it seem to be working fine.

    Just curious, why would you need the value of the cell as it is formatted against its actual value in code? Chalo, let me know if this is working for you :)

  6. Karan says:

    I found your blog via Google while searching for free personal finance tips and your post regarding use custom cell formats in Microsoft Excel – tips | Pointy Haired Dilbert – Chandoo.org looks very interesting to me. I always enjoy coming to this site because you offer great tips and advice for people like me who can always use a few good pointers. I will be getting my friends to pop around fairly soon.

  7. [...] read Using Custom Cell Formats in Excel – Tips & Tricks to findout how to format dates, currencies, special formats etc. Tags: excel, formatting, howto, [...]

  8. JITENDER says:

    Dear Sir,
    I also have some problem in excel, can you solve my problem. I gave you my email ID .

    Thanking you

    • sandeep says:

      I want to sort/filter data by their different custom format is it possible to do so. ex.
      5.660/Pcs
      0.258/Sqm
      5.213/Rmts
      5/nos

      these kind of custom formatted details are downloaded from Tally ERP -9 I want to sort data by Sqm/Pcs/Rmts and other UOM . all data are in custom number format

  9. Chandoo says:

    @Jitender … surething, you are welcome to write a comment explaining the problem, that way, even if I dont know the solution, there will be one of our excellent readers who would know how to solve it for you, and they may come forward and post it in replies. Otherwise, feel free to write me an email… :)

  10. kc says:

    I’ve been browsing this thread for awhile and there is one format I can’t seem to create a custom format for. If I want to go from 1234-5678 to 12345678 how can I do that?
    Thanks!

  11. Chandoo says:

    @ Kc… thanks,

    I dont know any format that can 12345678 from 1234-5678, you can use substitute(“-”,”",1234-5678) though… let me know if you findout how to do this.. I will sponsor you a donut 2.0 :D

  12. [...] thus making the 01001 as 1001. Worry not, you can use rept() the extra needed zeros. You can also custom format cell contents to display zip codes, phone numbers, ssn etc. Example: Use zipcode & REPT(“0″,5-LEN(zipcode)) to convert zipcode 1001 to [...]

  13. [...] Try these other date formats as well. Learn more about custom cell formatting. [...]

  14. [...] How to use custom cell formats in Microsoft Excel – tips | Pointy Haired Dilbert – Chandoo.org Custom Cell Formatting in Excel – Few Tips & Tricks [...]

  15. asghar says:

    i want to calculate formula for example: 500PCS X 12 = 6000. but when i mention PCS or doz or set after any digit as above, formula says #VALUE. It can not give result.
    suppose 500PCS IN A CELL x 12 IS IN B CELL FORMULA IS =SUM(A1*B1). kindly help me abt.

    • Chandoo says:

      @Asghar: Welcome to Chandoo.org.

      You can correct the formula in the following ways:
      (Simple) do not enter PCS, Doz etc in the cell A1, but use another cell like A2 maintain the units.
      (slightly complex) use custom formats to display “PCS” or “Doz” next to the value so that formulas still work while you can maintain the desired look. You can do this by defining a custom formatting code like 0 “PCS” or 0 “doz”

      See this image.

      Let us know if you still have any trouble. :)

    • AnnaK says:

      Asghar, I have another way to fix your solution
      Select the cells you want to put the number, Go to Number formatting/ Customer and type ####”PCS”
      This will add PCS to the cells you choose, you just only type 500 or any number you want. Then do your calculations, hope that helps. Now choose the easiest way you want

  16. Somnath says:

    Asghar,

    You can try using the left function. Using your example:
    =(LEFT(A1,3)*B1) ought to give you 6000.

    Thus said, you need to customize this formula in case the number of units run into thousands. :-)

  17. Chandoo says:

    Image URL:
    http://i287.photobucket.com/albums/ll133/pointy-haired-dilbert/excel-formula-help-asghar.png

    Also, you dont need to write sum(a1*b1), you can directly write =a1*b1 and it works.

    Btw, Somnath: good suggestion :)

  18. Somnath says:

    Chandoo,
    I am enjoying my first visit to your site. Looking at the thread posted by KC, you are absolutely right. Substitute function is the way to go about it.

    Considering 1234-5678 is in cell A1, the substitute function will be:
    =SUBSTITUTE(A1,”-”,”",1)

    Lots of good stuff in here!!! Makes me feel nostalgic about my good old college days. :-)

    • Chandoo says:

      @Somnath: thank you, I am very happy you liked the site and actively contributing to it. I have learned so much from readers like you and I really enjoy interacting with new people through this site. Keep visiting and share your knowledge with all of us. :)

    • DIN says:

      HOW ABOUT THIS …12345-12 => 12345-13 ( THE LAST DIGIT )
                                     12345-19=> 12345-20  ( PO WITH RUNNING NO.)
      Any tips.
       

      • Suril says:

        @Din: Assuming the value 12345-12 is in cell A1, using the formula =LEFT(A1,6)&RIGHT(A1,2)+1 will give you the desired result. A more refined formula is =LEFT(A1,SEARCH(“-”,A1,1))&RIGHT(A1,LEN(A1)-SEARCH(“-”,A1,1))+1 which will work will any number of digits.

      • Suril says:

        assuming the value to be changed is in cell A1, ‘=LEFT(A1,SEARCH(“-”,A1,1))&RIGHT(A1,LEN(A1)-SEARCH(“-”,A1,1))+1′ should work:-)

        • DIN says:

          2581-14 #NAME? A1, ‘=LEFT(A1,SEARCH(“-”,A1,1))&RIGHT(A1,LEN(A1)-SEARCH(“-”,A1,1))+1
          I TRY RESULT SHOWN . CAN YOU HELP !

  19. James says:

    I have a problem when formatting a cell that has 16 or more digits, using the Custom format. We are working with Credit Card numbers and want to be able to enter them as a 16 digit number like this: 1234123412341234 but want them to look like this: 1234 1234 1234 1234
    The problem we’re having is that the last digit always turns into a 0 (zero) and the number ends up looking like this: 1234 1234 1234 1230
    The custom format type we created is: 0000 0000 0000 0000 but we’ve also tried: #### #### #### #### with the same results.
    This only happens when using 16 or more digits. If we use 15 or less digits, the Custom format works fine. Unfortunately, credit cards have 16 digits so we’re stuck with the problem.
    We’ve tried this on Excel 2007 with service pack 1 and on Excel 2000 with service pack 3 with the same results. The Operating Systems we’ve tried it on are Windows XP with Service Pack 3 (and all other Microsoft Updates installed) and on Windows 2000 with Service Pack 4.
    Any help would be greatly appreciated.

  20. Chandoo says:

    @James,

    checkout the post: http://chandoo.org/wp/2009/04/07/using-credit-card-numbers-in-excel/ to understand how this can be solved

  21. [...] asks in Custom Cell Formatting in Excel Post, I have a problem when formatting a cell that has 16 or more digits … We are working [...]

  22. SReid says:

    Hi Chandoo –
    Awesome suggestions…. big question for ya! We have P.O. numbers that come from a system with the standard format of XX-XXXXX. The first 2 XX are the year – the very last X could be there or not be there depending on whether we are at the beginning of the year or the end of the year. When we dump data from this system into excel, 95% of the PO numbers format correctly in the column: 09-1234 or 09-12345 for example.

    The other 5% show as a date in the field: Sep-67. If you place your cursor in the formula bar it says 9/1/8267. The REAL PO number for that one is 09-8267. I’ve tried a ton of different ways to try and make a custom format for the user and am coming up blank every time.

    Any help would be of tremendous help! Thanks!
    SReid

  23. SReid says:

    Hi Chandoo – Sorry no luck on that format. The correct PO number is 08-8979. When dumped into Excel, and reviewing it the cell – it changed to a date format of Aug-79. If looking at it in the formula bar, it is 8/1/8979. If I apply the custom format you suggested, it changes the PO number in the cell to 25-85766 and in the formula bar to 2585766 (without the dash). Crazy I tell you! Crazy!

    Thanks! SReid

  24. Chandoo says:

    @SReid: when you dump data, excel assumes the formats of the data it is pasting. so even before pasting the data, you need to set the cell formats. Also, I see that your data comes with hyphen (-), in which case, you need not use any format codes, but just set the cell formats to TEXT from GENERAL. If you paste a lot of data, select the entire sheet and press ctrl+1 and set the formats to text.

    Let me know if you have some problems

  25. cybpsych says:

    hi chandoo,

    hope you can help me with this problem; it’s something about number formatting and “Creating dash boards using excel conditional formatting” @ http://snipurl.com/g2rwb

    consider these data:

    set 1: 2851/3.00 = 0.001052262…… = 0.11% (% with 2 decimal)
    set 2: 2851/3.01 = 0.00105576….. = 0.11% (% with 2 decimal)

    when i do a IF comparison for both 0.11%, it’s NOT EQUAL. This creates a little problem with the dashboard formula whereby it will show icon ? instead of ?.

    The only way to ‘solve’ this is to force format each cell with this: =text(2851/3.00,”0.00%”) and =text(2851/3.01,”0.00%”). Only then that bot sets are EQUAL.

    As there a simpler way to TRULY HARD FIX the decimal numbers in a percentage format? 0.11% == 0.11% :)

    Thanks!

  26. cybpsych says:

    rewrite:

    This creates a little problem with the dashboard formula whereby it will show icon “^” instead of “o”.

  27. Robert says:

    cybpsych,

    use ROUND(2851/3.00,4) and ROUND(2851/3.01,4) instead and the two values will be equal.

  28. SReid says:

    Hi CHandoo….
    I am dumping the data from SQL. It is a varchar field. I have tried setting the column to TEXT. This does not work. SQL has the field (correctly) as 08-8979. When dumped to Excel, it changes it to: Aug-79. If I try to change the format to TEXT, the data then CHANGES to: 25-85766.

    The crazy part of this is that it only happens on about 5% of the data in that column. All of the rest of the data comes over in the correct XX-XXXXX format.

    Any other ideas?

    Thanks!

    • Chandoo says:

      @SReid: Hmm… do you have some automated program that is dumping the data from SQL? What do you mean when you say you have set the column to text? Is it in excel or sql. You should set it in excel. Another thing that comes to my mind is to use text import wizard and select the column type as text in the wizard. I know this could take more time, but if you import once in a blue moon it might be worth the effort.

      In the worst case, you may need to get some vba that would take the sql data and properly dump it.

      • Chandoo says:

        Also, when I try to paste the value 08-8979 in to excel, even though cell format is “TEXT” it is still displayed as Aug-79, but when I select the little paste icon beneath the pasted cell and say “match destination format” it is correctly displayed as 08-8979. May be excel is forcing itself to use source formatting (weird how it decided that source format is in date…?).

        • LINDSEY says:

          I know this was posted 3 years ago but maybe someone else will have a similar issue so here is the answer: I’m not sure why exactly but if you use 00″-”0000 it works. When you put in the 5th 0 it changes it to 00-88979? Strange! But it worked. :)

  29. cybpsych says:

    hi chandoo, i’m curious on the following:

    how to custom format this number: 883509615 –> 883.509615 Million or 0.883509615 Billion?

    don’t bother on the decimal numbers; I can round them up as per Robert’s suggestion above ;)

    thanks!

    • Chandoo says:

      @Cybpsych: hmm, I am not aware of custom format codes that can do this. I can suggest a formula version though, but I guess it should be obvious. Let me know if you come across any custom codes that can do this. btw, if you use the numbers in charts, excel has a facility to show them in millions, billions etc. but when they are used in cells… I am not sure which option would convert them to million etc. at format level while keeping the value intact…

  30. Robert says:

    cybpsych,

    use the thousand operator in custom number format:

    Assumed you have the cell value 12345678.

    Custom format #,###.00, will display 12,345.68
    Custom format #,###.00,, will display 12.35
    Custom format #,###.00,,, will display 0.01

    • Ramesh says:

      this format is very good but my problem is not resolved i am entering cell s value is 1,10,000 this value covet to 1.10 Plz help and send me the anwer to my id

  31. cybpsych says:

    thanks robert for your help ;)

  32. JohnM says:

    Hey Chandoo,

    I’ve tried relentlessly to figure this one out, but I’ve given up at this point. I have a sheet full of millions of entries that I input onto a worksheet, which are then pulled into a more intricate report using vlookup. Within one of the columns is a UPC code that i’m trying to transfer, and it should contain 12 digits (000000000000 custom number format). I have changed the columns format to suit that, so that some of the columns that had 11 digits will now have 12. However, even though I have changed the format, the 11 digit numbers remain unchanged until i open the formula cell and click into another cell. I obviously can not do this to the 100′s of entries every week….do you have any suggestions?! thanks!

  33. Chandoo says:

    @Robert: Very cool tip, I didnt know we can do that…

    @JohnM: Welcome to PHD. I have tried to replicate your problem in excel by entering dummy values in 10000 odd rows. The values had a mix of 11 digits and 12 digits among them. But when format them using the custom format code 000000000000, they are instantly changed. So I assume it should work the same way for you. May be because there are a million values, excel is choking under the pressure and not running the formats until someone navigates to the cell. But I would imagine the formats working perfectly if you ever need to print them.

  34. cybpsych says:

    hi chandoo/john,

    i believe this is happening because the data is imported from other sources?

    if so, Excel doesn’t treat the “numbers” as numbers. You will need to convert them into proper numbers in Excel.

    If AutoCorrect is on, select the range and hover aroudn the GREEN corner arrow. Select the “Covert to Number”.

  35. [...] to the data label. It is one of my favorite tricks in excel and I have written about it in introduction to custom cell formatting in excel, number formatting in excel using custom codes, showing decimal values only when needed [...]

  36. [...] when you do this, you need to enable wrap-text feature for that cell from cell formatting dailog (ctrl+1) to ensure proper [...]

  37. [...] lines in a cell | Learn Excel | Pointy Haired Dilbert: Charting & Excel Tips – Chandoo.org on Custom Cell Formatting in Excel – Few Tips & TricksHow to Get Colors in Excel Chart Data Lables – Formatting Trick | Charts & Graphs | Pointy [...]

  38. Wes Gibbs says:

    If I wanted all cells within column A to only have 7 digit numbers how can this be done? If a user wanted to enter 12, that 12 would be converted to: 0000012. I basically only want values: 0, 1, 2, 3, 4, 5, 6, 7, 8, and 9 and locations 1, 2, 3, 4, 5, 6, and 7 of the 7 digit string. I’ve been fooling with some time with no lock. I first converted the entire column A to text and gave it a format of 0000000 so that it got preceding zeros when values that are less than 7 digits are given. Then I started fooling with the Data Validaion putting: =LEN($A$1)=7

    but it doesn’t seem to work quite right.

  39. Messam says:

    Hello !! I am facing a problem in displaying value in this format “1/1″ (Without inverted commas).

    Currently I have information in this form 1 / 1. When I remove spaces from the cell, the value is automatically converted to Date Format as 1-Jan. Can anyone of you help me getting the required format after removing spaces.

    Many Thanks in advance !

  40. Messam says:

    Making the problem more clear !

    Current Value of the cell: 1 / 1

    After removal of space from the cell

    New Value of the cell: 1-Jan

    Required Format: 1/1 (without space)

    An urgent response will be highly appreciated !! Thanks !!

  41. Aires says:

    @Messam there’s a method that comes from Lotus 1-2-3. When you’re typing the value, start with a ‘ (apostrophe). So, in your example, typing ’1/1 should work properly.

    Hope that helps.

  42. Messam says:

    Many Thanks !!

    I got a solution myself as well.

    The same can be done using a function in excel called “SUBSTITUTE”.

  43. Messam says:

    Aires: I could not understand the solution you provided. Can you please explain?!?!

  44. Chandoo says:

    @Messam.. you can also force excel to consider a cell’s contents as text by setting the formatting type to “text” from “general” this will leave 1/1 as 1/1 instead of making it jan-1

  45. [...] and “n”, user should type “1″ and “0″. Then we can use custom number formatting to conditionally display the tick mark [...]

  46. Elisha says:

    re: John M

    I am having the exact same problem as you! Did anyone ever find a solution??

  47. Elisha says:

    I had tried everything I could think of for the problem John described that I was also having.
    I had this issue with phone numbers and zip codes.
    I decided to use text to columns to split up my zip+4 column and accidentally clicked finish on the first step (with delimited selected) and magically my entire column of data updated to display the correct format. I repeated my “mistake” on the phone number column, which was also not updating to the correct format, and it worked there too!
    It’s a silly way to have to do it, but hey, whatever works…!
    I would love to know what is causing this issue though.

  48. Chandoo says:

    @Elisha… welcome to PHD and thanks for your comments.

    Did you try using the TEXT formula. It forces the formats by changing the values to text. For eg. =text(a1,”00000-0000″) would force the value A1 to appear as zip+4 format.

  49. Kim says:

    I have a spreadsheet of X’s and I want to add the number of Xs in each column. I’d like to custom fomat X so that it reads as 1 and then I can add all the 1s. How would I do that?

  50. JAY says:

    Hi chandoo
    i want to convert to figures to text . how can do it
    it means 5000/- :- Five Thousand Only.

  51. Chandoo says:

    @Jay.. I am not aware of any custom formatting code that would do this. But some UDFs can do it. See here: http://support.microsoft.com/kb/213360 and http://www.ozgrid.com/VBA/ValueToWords.htm

  52. Prashant says:

    Hi chandoo

    Custom Cell Formatting in Excel-

    Select column – RAM CHANDRA
    Value before- RAM CHANDRA
    Costume Format- “MR.” @
    Value After- MR. RAM CHANDRA

  53. Jay says:

    Hi
    chandu

    I want to no. tricks but in below this format

    ****12,345.00 this format .
    please send me some tricks for this format

  54. Hui... says:

    Jay, try
    “****”#,###.00

  55. plz help me says:

    dear sir.
    i have two problems that are :

    1) in a work sheet, list of names, automatically added the email domain [eg: snarif @honda.com,munni@honda.com; tazman@honda.com]
    2) few numbers are available, i want those numbers in words [5000 will show Five thousand, 100 will show One Hundrate]

  56. jay says:

    hi chandoo
    make a telephone directory in excel using visual basic and create the derectory
    please give me idia’s

  57. Leo says:

    Dear Chandoo,
    I want to display codes of 5 digits, but sometimes it has 3 decimal places after those 5 digits. I don’t want to show the decimal unless it is followed by the 3 decimal places. If I use ?????.??? or #####.### it always shows the decimal after the 5 digit numbers without any decimals following them. For example 12345 would show up as 12345., and 12345.678 would show as that. The latter is fine, but the first example needs to drop the decimal point. Any ideas will be appreciated. Thank you.

  58. Ensav says:

    Dear Chandoo,
    I used the following custom format (found in a forum) to display a hyphen instead of zero… But the problem is that after changing to this, I can not align the content to center or left.
    _(* #,##0_);_(* (#,##0);_(* “-”_);_(@_). Could you please check and advise. Thanks

  59. Hui... says:

    @Ensav
    try
    _(* #,##0_);_(* (#,##0);_( -_);_(@_)
    No need for the quotes around the – unless you want them

  60. Ensav says:

    I tried. It works only with the cells of ‘zero’ values and not with the remaining cells having other values. Normally the format applies to all sheets / area. Any other way…. Thanks

  61. Fortunate says:

    Hi Chandu,

    I need to format the values as follows and have no idea how to do it.

    00:17:42 –> 00d 17h 42m

    Please help.

  62. Hui... says:

    @Fortunate
    Use the Custon Number Format
    Ctrl 1, Custom
    d”d” [h]“h” mm”m”
    Make sure all the “‘s are “, retype if necessary

  63. Fortunate says:

    Hi Hui…,

    Thanks for your quick reply..
    It is not working :( It is giving the same output 00:17:42

    I think we need something to remove the colons. Please help!

  64. ltg says:

    Someone asked me about this today…

    Enter 1-2 and have it convert to 1′-02″. 1-10 goes to 1′-10″, etc. Actually, it might be more important to have 1-6 convert to 1.5, 1-2 convert to 2.1666667, etc because what it really needs to do is multiply/add correctly. So maybe the solution is not so much in the formatting, as much as it is in the formula? I guess the user could use TEXT for input so it would stay as 1-10, then have the formula parse the “-”, and convert it to 1 + xx/12? Or any better ideas?

  65. amol says:

    send me excel notes

  66. Sam says:

    Thanks for this great tip, was really helpful

  67. Raj Kiran says:

    Hi Chandoo,

    I have always been a frequent visitor and reader of your posts. Inspired by the same, I have done a project performance dashboard for my organization.

    Currently am revisiting the dashboard to be more dynamic. In the process I am facing a slight problem. I am trying extract data from the database to a single cell using vlookup. Now the data is of different types like dates, percentages, numbers and text. But I am unable to make the cell receiving these data to have multiple formats. Can you please suggest on how to make a cell recieve different data types.

    This will be of real great help.

    Thank you in advance
    Raj Kiran

  68. naveen says:

    Hi chandu,
    this is my first visit to your site. Can you tell me that if I have the data with comma as seperators and decimal 2 places , how to convert it to general format.

  69. Lisa says:

    Hello all and please help!
    One unit is six minutes which I need to display as 00:06. Ten units is one hour which needs to be displayed as 01:00 and so on.
    Then there is an hourly rate, say £60 per hour. The final column needs to show the total amount.
    How do I show the time in minutes but multiply by the number of units??
    TIA
    Lisa

  70. Hui... says:

    @Lisa
    If cell F1 has your units
    In G1 put =F1*(6/(24*60))
    Apply Custom Format as hh:mm
    in H1 =F1*60
    Apply £ format to H1

  71. Brian says:

    Is there a way to have a cell value always show a “+” if it is a positive number, and an “-” if the value is negative?

    Example: 0.6% would show as +0.6%.
    -0.8% would show as is.

  72. Sam says:

    Hi..

    I was trying to format numbers in the Indian format of Lakhs & crores..

    I went to custom and typed the following 00,00,000

    But excel keeps showing the default of millions & billions 000,000,000

    Pls help

  73. Sam says:

    Dear Naveen (#65),

    you can try this shortcut Ctrl+Shift+~

  74. zemane says:

    On the -12 and 12 example, where did the Rs. came from?

  75. Roopa says:

    Hi,
    I am trying to custom format a particular cell in excel to display the figure in lakhs
    Original number is 11111111. This should convert to 111 lakhs and the cell value should be shown as 111
    Can you please let me know if it is possible in custom format. I tried
    0″.”0,, which returns in crores (1.1) and 0″.”0, which returns 1111

    • Hui... says:

      @Roopa
      It is only possible to divide numbers by 10^3, 10^6, 10^9 etc
      This is done by adding a comma to the custom format code
      so
      123456 displayed as “#,.000″ will display 123.456
      123456789 displayed as “#,,.000″ will display 123.457
      .
      The only other small modification you could use is to use a helper cell with
      =TEXT(10*B4,”0,,”)
      so that 11111111
      will display as 111, except that internally it will hold 111111110

      • Roopa says:

        Hi Hui,
        Thanks for your quick reply although I do have to admit that the solution cannot be adopted since this particular cell is only a format template cell and does not accept any links or formulas. Also the internal value of the cell cannot change since the same base has to be used for all the conversions( thousands, millions, crores etc)
        I needed some expert confirmation before calling quits :-)

        Thanks a lot
        Roopa

    • Hui... says:

      @Roopa
      Thanx to Kyle below, You can use a Custom Format of
      #,,,” Lakhs” %%
      To display what you want
      Use the above Custom Format and insert a Ctrl J before the first %
      Then set your cell to word wrap and top/centre alignment

  76. A.Mac says:

    Hello,

    I’m wondering if there is a way to have Excel display a value 1000 times greater than its actual value using custom number formats.

    For example, if I give a cell the value 0.00943 how can I get it to display as 9.43 mA, but still have the value 0.00943 V so I can perform other operations on it. I realize that I can simply multiply the cell value by 1000, but then if I want to use that value in another calculation I need to divide by 1000 to get it back to its original value

    • Kyle McGhee says:

      Hi A.Mac,

      Select the cells you want to format and enter this in the custom format:
      .# “mA”%%

      before you press enter, put the cursor between the ” and the % and press ctrl+j which will insert a carriage return.

      And then on the Alignment tab of the Format Cells dialog, select word wrap. .000943 becomes 94.3 mA.

      The rows have to be normal height, if they are larger you will see the two %% symbols. Change the number of # after the decimal if you want to increase/decrease the number of digits after the decimal

      Kyle

      • Kyle McGhee says:

        sorry just realized that mine was 10,000 and gave 94.3 and you wanted 9.43, so same deal except
        .##, “mA”%%% – this is multiplying by 1,000,000 with the three % and also dividing by 1,000 with the comma

        plus the ctrl+j between ” and % and word wrap will give 9.43

    • Hui... says:

      Neat trick Kyle
      But to get from 0.00943 to 9.43 mA you need the following format
      0.#, "mA"%%%
      The , divides it my 1000 and the 3 %’s multiply by 100 each

      Also the cell needs to be Top Aligned as well as have word wrap

      • Kyle McGhee says:

        Thanks Hui. I noticed that, as you can probably see now. Are you sure about the top alignment? I just tried it with all vertical and horizontal alignment combinations and the %% symbols stayed out of sight. Row height is the 15 (20 pixels).

    • A.Mac says:

      Thanks for your help gentlemen.

      It’s too bad there wasn’t a more elegant solution, but that did the trick.

  77. Wayne says:

    How to use my own text wording in a cell from the results from another cell in excel 2010. Example When H17 is a negative dollar value I would like the word in E17 to read Disqualified in the colour red. When H17 is a positive dollar value I would like to have the word in E17 to read QUALIFIED in the colour blue.
    Thank you

  78. [...] thanks to a tip I picked up from Kyle who responded to a post here at Chandoo.org they are all [...]

  79. Veena says:

    Hi Chandoo,

    I have a scenraio here, where the cell needs to hold leading zero’s for example Cell A1 will have the 0123456, cells A2 can have the data as 0012345, Cell A3 can have the data as 1234567.

    I am unable to fix this issue with custome format. Can this issue be fixed with custom format.

    Thank You.

  80. [...] in the new box, and towards the bottom are some more time formats to choose from. There are some tips here on how to use the custom [...]

  81. Amit Gogna says:

    Hi,

    I too am stuck with a peculiar issue.

    I have a sheet with seconds (from 0 to 10,000) against each person stating how much they used the phone on each day, I have converted the seconds to time by using <>, where c2 is the cell (column) for seconds. I wanted to display this as <>. So I tried using the custom formatting of the cell by putting <>. Worked fine till the usage goes over 30 days. If the person uses the phone for (lets say) 35 days, 2 hours and 31 minutes, it displays <>. Is there ant setting to get the absolute number of days stated?

    Thanks.

  82. Scott says:

    Chandoo,
    I am trying to custom format a cell in Microsoft Excel. I want to set up a custom format that will add a specified % to any number I input into a cell. What type of format/formula must be included for this to work?…………
    Thanks

    • Hui... says:

      @Scott
      any code just add an % to the end
      eg:
      #.00%
      0.0%
      etc

      • Scott says:

        I did not explain myself well. Sorry.
        I want to add 2% to any number I input into the cell by using a custom format.
        For example in the cell: when I input 3, I want the cell to multiply it by 2%, giving me the output of 3.06.
        I am not sure this is even possible.
        Thank you.
        Scott

  83. Debbie M says:

    Thank you for your help!! :)

    Can you format cells to always have a negative number no matter what you type in. I need like 15 cells in one column and 35 cells in another column?

    Thank you!
    Debbie

  84. Hiren says:

    can someone tell me how to write number 1234567 as 1,234,567.00

    Thanks

  85. Hiren says:

    can someone tell me how to write number 1234567 as 1,234,567.00
    in Excel 2010
    Thanks

  86. Prasad says:

    Hi,
    Can anyone help me with the below issue,
    I have a report in excel in which all the amounts are in custom format. I wanted to paste the same in Text format. (For e.g. amount in custom format is shown as 100 Dr however when we formula bar we could see only 100; I want 100 Dr to be shownn on formula bar) Could you help me on this.
    Thanks,
    Prasad

    • Hui... says:

      @Prasad

      Custom Formats only change the displayed format of the cells contents in the Cell itself
      In your case the cell contains the number 100
      but the cell displays it as 100 Dr
      The Formula Bar always displays the cells contents wether it is Text, a Value or a Formula

  87. Kevin says:

    Need some help I have a group of numbers that have a letter in it and I am trying to get the format to match but coming up with nothing.

    Example:  24A102

    This is what I am wanting to get: 24-A1-02     

  88. heena says:

    hello can any one tell me how to use formula for this particular format in custom
    Format – first 5 character, then 4 digit, then one character
    eg. AAAA12345A
    if user doeas not follow above formate error should display.

    I just want how to put formula 

  89. HP says:

    Hi Chandoo,
    I am trying to format the following number in Excel -10 can you tell me how can i do it. Example is below, when i import the data it comes with 1,25M and so on, how can i convert them with 1,250,000 etc.

    1,25M = 1,250,000
    5,00M = 5,000,000

  90. Ravi says:

    dear sir
    how to split date month ,year time in one word example :20120621121201

  91. Cat says:

    Hey, i have been having a problem formatting a cell. Since everyone here seems like experts compared to me, i hope you can help me solve this.
    In a cell the data is input as  “abc00120120101001″ where I need it to be formatted into “abc001-20120101-001″ and then in a different cell I need “abc001″.
    Any suggestions would be wonderful.

  92. MaxiM says:

    Chandoo,

    Kindly assist how to format/customize the Arrow, the arrow pointing up is color Red while the arrow pointing down is Color Green.

    Tnx alot in advance

    mxm

  93. Lasapa says:

    Hi!

    Is there a simple way to customise a cell with units that I have specified in another cell (or more)?

    e.g.
    I have two cells: 
    A1 – where I specify a unit.
    A2 – where I enter a value.

    I would like cell A2 to have as units the one specified in A1.
    So if A1 contains “m” and I enter “25″ in A2, 
    A2 should display “25 m”.
    (I do not want to use an ‘if’ formulation here because I would like to allow for A1 to take any possible value … It could be ‘Apples’, in which case A2 should display “25 Apples” …  etc).

    Thanks,
    LS. 

    • Lasapa says:

      And if possible, I would like to avoid having to define a name for A1 values. 
      Thanks,
      LS. 

    • Chandoo says:

      Hi LS.. are you still looking for help on this.

      Since you just want 25 m or 25 apples for display (but in background the value remains 25, why not readjust the columns so that you enter value first, then pick the unit in next cell. Then you can remove grid lines, adjust alignment so that it looks natural.

  94. Marg says:

    I need to format a cell that would take health card number, sometimes there is a alpha verison code (not always).

    Example> 1234 567 890

    Example> 1234 567 890 nm or 1234 567 890 ab

    I have tried several different ways to custom format a cell but I was unsuccessful.

    • Chandoo says:

      The formatting codes work very well for numbers, not so much for text values like above. So I suggest this:

      Select the cell(s) and format them as TEXT.

      Now you can enter whatever you want.

      When displaying you can easily add a space after 4th and 7th characters like this:

      =LEFT(A1,4)&” “&MID(A1,5,3)&” “&MID(A1,8,3)

      & IF(LEN(A1)>10,” “&RIGHT(A1,2),”")

  95. bilynoz says:

    Hello, I received a file with prices in txt format and after opening with Excel 2010 some cells have ‘general’ format and show prices e.g. 114.00 but others have ‘custom’ format and when I click on them, they actually show date in the formula bar, e.g. when I click on price 2.23 it shows 1.2.2023 in the formula bar….I need 2.23 to be the contents of that cell and in a number format, please help!

  96. Solomon says:

    Hi ,
    thanks a lot for your excel formating content. i have a phone field some time it will have 10 digit value (e.g.7877988334) some time it may have phone field with the country code ( e.g. (+91) 7877988334 ). how to create the format to support both of them.
    waiting for your reply,
    - Solomon

  97. s9y says:

    Hi,
    I want to know that how can i force a user to enter this particular number in the sheet. 000-000-000 is the format and number will vary. In addition if there are any alphabets in the entry a typo maybe then they should highlight with some color. Please let me know it its possible.
     
    Thanks in advance.
    Sunny

    • Suril says:

      @Sunny: Assuming that the value is in cell A1.
      Go to data – data validation – data validation - allow (custom) and formula should be: 
       AND(CODE(MID(A1,{1,2,3,4,5,6,7,8,9},1))>=48,CODE(MID(A1,{1,2,3,4,5,6,7,8,9},1))<=57,LEN(A1)=9)
       
      the above formula should be without an “=” sign at the start.
       
      Now under format cells, keep the formatting as 000-000-000. Doing the above will restrict the user from typing anything other than numbers and that too of 9 digits. Now simply copy+paste cell A1 to all the cells wherein this criteria is to be followed. You may delete the data in the cells but the data validation will remain.
       
      Hope this solves it. Let me know.
       
      Regards,
      Suril

  98. TARUN KUMAR says:

    hey
    I’ve a prblm,
    tarunkumar
    anilsingh
    here are different range names, I want to separate them,
    i couldn’t find the way to separate them(text to column is not working in this because it doesn’t have any space,commas,etc.)guys do u have some for this….

    • Suril says:

      @Tarun, if I understand your question right, you want to tarunkumar to appear as tarun kumar. If so, then there cant be any solution to this! If I got the question wrong then do let me know..

  99. mohd says:

    PO WITH RUNNING NO.
    EG  12345- 01( CELL A1 ) . I WANT TO  CHANGE  B1 TO 12345-02.
    ANY FORMULA WITHOUT USING DRAG FILL.
    MANY THANKS

  100. How to set Default character against input character
    eg:
    I input Vijay but cell shows Mr. Vijay
    How to prefix against text
     
     
     

  101. YOGESHWAR says:

    Hi everyone. Please help with this. I have been tried to find this entirely on Net. Bt i did not fine that.
    Actually i have a spreadsheet attched with other sheet.. and i enter value in 1st sheet and its bring the result in 2nd sheet. bt something results are negative number. in cell let say in Cell a1 has some formula and some time result will be change in negative numbers. Once the results automatic change. i want pop-up message instantly. please help me wtih that. Mr. chandoo. please..
    Thank you

    • Suril says:

      @YOGESHWAR
       
      Lets say value you enter is in A1 (Sheet1) and result in A1 (Sheet2). Goto A1 (Sheet1), data – data validation – settings – allow custom and formula should be Sheet2!(A1)<0; goto error message, style – warning and write your warning. Done!

      • YOGESHWAR says:

        Suril. Thank you very much to reply me back.
         
        actually i have formula in Cell A1 has formula and after entering anything A1 result automatic goes up and down and sometime its in Negative numbers. So once Forumla cells goes in Negative  i want to have pop-up error message. instantly.please help me with that.
         
        Thanks again

      • YOGESHWAR says:

        @ Suril. Dear. i tried with that.. its not working.. its now alowing me to enter any value.on there. please help me with that..
         

        • Suril says:

          can u let me know what error is coming? If you have changed the style to ‘warning’ then only a warming message should be displayed. If you still are unable to enter any value then you may mail me the sheet on suril88@gmail.com.

          • YOGESHWAR says:

            Dear Suril. Please accept my invitation that. i just send you on Gtalk.
             
            Thank you very much for you kind help .
             
             

  102. Luke says:

    Is there anyway to force a cell to take a certain format and give an error otherwise? I know it can be done in Access, but I can’t figure it out for excell.  I have an ID that should be in the format ‘A1234.’ Any ideas?

  103. Suril says:

    Assuming that the value is in cell A1, you can use the below formula:
    =IFERROR(AND(LEN(A1)=5,CODE(MID(A1,{2,3,4,5},2))=0)),”")

    You can use the above formula for different formats by modifying it appropriately. To ensure adherance to the format, “validation” should be used.

  104. Presto says:

    This is truly an awesome site. I have used your thought process(es) to come up with many Excel solutions. I’m stumped on this one, even though it seems simple enough.

    I have many rows and columns of data, but for this example, I’ll talk about the first three rows and first two columns. In Column A I have numbers, and in Column B there may or may not be a letter. I am trying to use Custom Format to put the letter (if any) from Column B into Column A (after the number), but have had no success. I realize that since I’m referencing the cell next to it, I’ll probably need to use offset, but perhaps VBA is the only answer. I also have data in Column D/E, F/G, etc. with each series of two columns having a number and then the following column that may or may not have a letter for the formatting.

    The reason for this is that I want to still be able to add/calculate Column A for charts and graphs, so if I just concatenate it, I lose that ability.

    I’ve tried code that includes (where fullRng is Col B):

    For Each curCell In fullRng.Cells
    If curCell.Value = “” Then
    cfStr = “#%”
    Else
    cfStr = “#%”"/” & curCell.Value & “”"”
    End If
    curCell.Offset(0, -1).NumberFormat = cfStr
    Next curCell

    I’m stuck. :-(

    • Hui... says:

      @Presto

      It looks like your trying to setup Custom number formats
      But there is no need to include the Cell’s value in a Number Format

      Can you tell us what your trying to achieve ?

      • Presto says:

        Thank you so much for responding. Trying to get the letter in the cell to the right (Col B) to show up at the end of the number in Col A without losing the ability to calculate Col A. So if I have 20% in Col A and ‘C’ in Col B, I want Col A to show 20%C but still be able to calc and chart the 20%. Bearing in mind I have hundreds of rows and about 20 columns of data with odd colums (A,C,E,etc) housing percentage and even columns (B,D,F,etc) either blank or housing a letter that I want to show in the same cell as the number. I currently do it manually, cell by cell, but it takes forever, and i keep getting this request from other departments.

        • Hui... says:

          @Presto

          Can you post a sample file with your data and example of what the outcome should be?
          Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

          • Presto says:

            Of course. Thank you.

            https://docs.google.com/file/d/0B0o-dyb6UCKkRXh4d0RpTG1JdlU/edit?usp=sharing

            Notice that even though the letter appears in the cell when I manually set custom formatting, I can still do calculations on the cell.

            My thought would be that in the actual document, the cells with the letters on them would be hidden, and the cells with the % would just use the hidden cells as part of their custom format. I’ve done this before with conditional formatting (colors, etc.), but it has a place to reference other cells. I’m struggling with how to reference a cells contents in Custom Number Format if it is possible. I’ll use VBA if needed…either way, if I can get it to work, it’ll save me from having to do it manually for every letter.

        • Hui... says:

          @Presto

          Try this code:

          You may need to retype the ” marks as WordPress seems to scramble them


          Dim fullRng As Range
          Dim curCell As Range

          Set fullRng = Range("B2:G4")

          For Each curCell In fullRng
          If curCell.Value = "" Then
          cfStr = "#%"
          ElseIf curCell.Value >= 0 And curCell.Value < = 1 Then
          cfStr = "#%" & curCell.Offset(0, 1)
          Else
          cfStr = "General"
          End If
          curCell.NumberFormat = cfStr
          Next

          • Presto says:

            Thank you very much! Closer to the goal. I’m getting an error that says I’m unable to set the NumberFormat property of the range class.

            After some testing, it rolls through the sequence, but when it hits an empty cell it throws the error. I’m investigating how to have it ignore that…probably just more if…then. If I get final code that works through the error, I will post it, or if you have an idea (and time to share it), I’m open.

            Either way, you’ve given me some great code and a direction…thank you!

          • Presto says:

            Aha!

            Getting rid of the “\” seemed to do the trick. I updated the code to check the cell’s format rather than content, but kudos to you in putting me on the trail of the solution!!

            Code that worked…

            ‘Dim fullRng As Range
            ‘Dim curCell As Range
            ‘Dim acf As String

            ‘Set fullRng = Range(“B2:G4″)

            ‘For Each curCell In fullRng
            ‘acf = curCell.NumberFormat
            ‘If acf = “0%” Then
            ‘ If curCell.Offset(0, 1).Value = “” Then
            ‘ cfStr = “#%”
            ‘ Else
            ‘ cfStr = “#%” & Chr(34) & curCell.Offset(0, 1) & Chr(34)
            ‘ End If
            ‘Else
            ‘cfStr = “General”
            ‘End If
            ‘If curCell.Value = “” Then
            ‘ cfStr = “General”
            ‘End If
            ‘curCell.NumberFormat = cfStr
            ‘Next

  105. Gayla says:

    Once you have entered a Custom choice in the Formatting, is there a way to save it as a permanent choice for any Excel spreads I do in the future?

  106. Diann Buckland says:

    It is rare to find somone who really knows their stuff. Good job.

  107. Alok says:

    my problem is that ,i have to keep cell as text but the format shoud be DD/MM/YYYY. Means i need custom format for the cell.
    please help.

    Regards
    Alok

    • Hui... says:

      @Alok

      Dates are always numbers, but they can be displayed as Text
      You can use a Custom Number format of dd/mm/yyyy

      What do you want to do with it as text ?

  108. Sandeep says:

    Hi Chandoo,
    I want to remove below character. I have around 50k rows data which belong some of one character is there. I want remove those.
    > ? / {} [] \ | # @ $ % ^ & * ( ) _- = + and so on.
    So I used
    =TRIM(A2)
    =TRIM(SUBSTITUTE(B2,CHAR(160),CHAR(32)))

    but after using these function also all characters are not removing.
    Please suggest. (? & *) these are the critical character to remove.

  109. Bill says:

    can a number 1234567 or12345678 always be displayed as either 12-34567 or 12-345678 so that there are always 2 digits to the left of the “-” and the remaining digits to the right of the “-” regardless of how long the number is by using custom formatting only without the use of a helper cell?

  110. Bill says:

    Thanks anyway, I got it.

  111. Don Jones says:

    When using excel and performing calculations, when the calculation results in -, 0, #DIV0 is there a way to format the cell to not show that? I want it to show nothing.

  112. Donny says:

    can you help me for this?

    i have value (text) in cell :
    190050300000

    i want that value be :
    1.90.05.03.0.0.000

    what i must be do?

    • Hui... says:

      If it is text, I don’t think there is much you can do

      If it is a Number you can use a Custom Number format of
      #”.”##”.”##”.”##”.”#”.”#”.”###

  113. Robin says:

    I’m curious if it is possible for a single cell to show a dollar amount (ie. $5,000) or a percentage amount (ie. 5%). I’m using “if” formulas in this cell as well. Thanks in advance for your help!

  114. Que says:

    I would like to format a column to not put hyphens or dashes in. For example, if I want to type say a phone number 123-456-7890, I want the result to be 1234567890 or a social security number 123-45-6789 to become 123456789. I know how to do Find & replace, or write formula to remove/substitute. What I’m looking for is a way to format that column so that when user enter a number with dash or hyphen, the end result is still in the same column but without the dash or hyphen.

    • Hui... says:

      @Que
      Try a custom number format

      Select the cells
      Ctrl 1
      Number, Custom
      In the Type use: 0″,”000″,”000
      Apply

      You may have to re-type the ” manually as the Blog software sometimes stuffs them up

  115. aknsms says:

    Dear Chandoo,
    I am creating an excel invoicing worksheet. The total amount cell i want format in Lakh/crore format (ex.1,23,45.678.90) Also i want that cell should be blank if calculated value is 0. right now i am having 0.00 value in that cell. I have applied the following string in custom format:-
    [>=10000000]##\,##\,##\,##0.00;[>=100000]##\,##\,##0.00;##,##0.00

    Kindly help me which string i need to add after that so i can have blank cell in case of zero value.
    Regards,
    aknsms

  116. aknsms says:

    Dear Chandoo,
    I have a cell formatted as DATE and whenever i type 31/12/2012 it becomes 12/31/2012. I have selected English (UK) format and changed it to required format.
    But
    What i want is if i input 31-12, it should aouto complete to 31/12/13 or 31/12/2013
    Can it be possible?
    Regards

  117. Donna says:

    I would like to format .256482454864 to show 256482454. Basically the number converts to nanomolar.

    Thanks.

  118. Jean Louis says:

    Hey guys ,
    i need to convert the following :
    Example: 4P9874 to 4P-9874

    Thank your in advance

  119. ruhhan says:

    hi, i want to format the number with two digits and 100 separator only once
    e.g.

    1) 1234,56.00
    2) 123,45.00
    3) 12,34.00
    4) 1,23.00
    5) 12.00

    I am using following custom format “##”,”##.00;(##”,”##.00);-”

    The problem i m facing is in case of 5th example above where I get displayed number as “,12.00″. i.e. comma is displayed before number which i dont want. Please help. Its urgent.

Leave a Reply