Custom Cell Formatting in Excel - Few Tips & Tricks
By Chandoo at 25 February, 2008, 9:55 pm
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]
Tagged with: cell | Excel Tips | formatting | hacks | how to | information | microsoft | spreadsheet | technology | tips | tricks
Did you enjoy reading this post? If so, give me some love
Consider subscribing to my newsletter every weekday I will send you one email with hot excel tips, technology tidbits or business insights that can make you more productive. Each mail will have unsubscribe link so you can stop receiving the mails at any time you wish.
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
Comments
RSS feed for comments on this post. TrackBack URI
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