Custom Cell Formatting in Excel – Few Tips & Tricks
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 share them to you all.
First take a look at how the cell formatting dialog box – number tab looks like:

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.

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]
|
|
Posts & Navigation
Tags: cell, formatting, hacks, how to, information, Learn Excel, microsoft, spreadsheet, technology, tips, tricks |
Trackbacks & Pingbacks
- Pingback by Custom Cell Formatting in Excel - Few Tips & Tricks on February 25, 2008 @ 10:08 pm
- Pingback by Formatting numbers in excel - few tips | Pointy Haired Dilbert - Chandoo.org on June 16, 2008 @ 2:35 pm
- Pingback by Beyond If and Sum: 15 very useful microsoft excel formulas for everyone | Pointy Haired Dilbert - Chandoo.org on August 13, 2008 @ 11:52 am
- Pingback by Date with my sheet - 10 tips on using date / time in microsoft excel | Pointy Haired Dilbert - Chandoo.org on August 26, 2008 @ 10:41 am
- Pingback by Custom Cell Formatting in Excel - Few Tips & Tricks | CompuFiciency = Applied CompuTelligence on October 27, 2008 @ 2:58 am
- Pingback by Using Credit Card Numbers in Excel | Excel Howtos | Pointy Haired Dilbert - Chandoo.org on April 8, 2009 @ 10:26 am
- Pingback by How to Get Colors in Excel Chart Data Lables - Formatting Trick | Charts & Graphs | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on May 27, 2009 @ 4:49 pm
- Pingback by Use Alt+Enter to get multiple lines in a cell | Learn Excel | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on May 27, 2009 @ 4:50 pm
- Pingback by Display decimals only when the number is less than 1 [Excel number formatting tip] | Learn Excel | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on May 27, 2009 @ 4:51 pm
- Pingback by How to Hide a Cell’s Contents in Excel? [Quick Tip] | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on June 5, 2009 @ 7:39 am
- Pingback by How to get tick marks / check marks in Excel? | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on September 15, 2009 @ 9:32 am
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums


At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 




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.
@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
Thanks. So in case, I want my program to pick the value the way it appears on the UI, how can that be done?
@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
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.
Dear Sir,
I also have some problem in excel, can you solve my problem. I gave you my email ID .
Thanking you
@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…
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!
@ 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
tour
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.
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.
@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.
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
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.
@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.
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.
@James,
checkout the post: http://chandoo.org/wp/2009/04/07/using-credit-card-numbers-in-excel/ to understand how this can be solved
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
@SReid: welcome to PHD. I will try this and get back to you. But I guess the custom code 00″-”00000 should work?
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
@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
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!
rewrite:
This creates a little problem with the dashboard formula whereby it will show icon “^” instead of “o”.
cybpsych,
use ROUND(2851/3.00,4) and ROUND(2851/3.01,4) instead and the two values will be equal.
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!
@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.
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…?).
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!
@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…
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
thanks robert for your help
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!
@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.
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”.
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.
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 !
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 !!
@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.
Many Thanks !!
I got a solution myself as well.
The same can be done using a function in excel called “SUBSTITUTE”.
Aires: I could not understand the solution you provided. Can you please explain?!?!
@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
re: John M
I am having the exact same problem as you! Did anyone ever find a solution??
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.
@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.
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?