Excel Custom Cell Formats


Learning how to customise a cell format in Excel allows you to not only format your data the way you want, but in some instances it can save you time.
Before we dive in you need to know that despite how the text appears after you’ve set your custom cell format, the underlying value is unchanged for the purpose of formulas and calculations.

How to enter a custom cell format

Select the cell/s you want to format then open the Format Cells window.
  • The quick way just press CTRL+1
  • Or the way most people do it is to right click and select ‘Format Cells’.
  • On the Number Tab select Custom from the Category list.
Format Cells dialog box
Note: It’s handy to have the text you want to format in the cell before you press CTRL+1 because Excel will give you a sample view of what the text is going to look like in the Format Cells window, so you can see before pressing OK, if it’s what you want.
How to make your cell formats look the way you want
Custom Cell FormatsText Before FormattingCustom FormatFormatted Text
Brackets for negative values-500#,##0;(#,##0)(500)
Red and brackets for negative values-500#,##0.00;[Red](#,##0.00)(500.00)
Day of the week in full27/03/2010ddddSaturday
Day, date, month and year27/03/2010ddd dd mmm yyyySat 27 Mar 2010
Month27/03/2010mmmmMarch
Phone Numbers75555123400 0000 000007 5555 1234
Phone Numbers with Brackets755551234(00) 0000 0000(07) 5555 1234
Fractions10.5# ??/??10  1/2


How to save time with Custom Cell Formats
1)      From time to time I create a reference sheet like a contacts list, an index or even just a list of items like the one below using the custom cell format @*.
Excel custom cell formatting
Because the text in the first column is often different lengths it can be hard for the eye to follow across.  In these cases I like to use trailing dots to help the reader.
I wouldn’t dream of manually entering the dots but since I can create a custom format it’s worth it, plus I think it looks more elegant that using borders for this purpose as they can get a bit busy.
The custom cell format for trailing dots is @*. When you type in your text Excel will automatically enter the dots to fill to the end of the cell.
Tip: You’re not just limited to dots.  You can have —- or **** or ____ or almost anything you want.  Just replace the dot in the custom format with the character of your choice.
2)      The other custom format I use regularly is prefixing my data with text.  For example, I keep a record of our invoices and instead of typing ‘INV’ before each number I enter I use a custom cell format like this: “INV” 0000
Then when I type in 597 Excel converts it to INV 0597.
Tip: Replace INV with different text to suit your needs.  It might be PO for purchase order, or any other text you can think of.  Or make the text a suffix by changing the custom format to 0000 “INV”.
Remember that even though the text appears to be INV 0597, for the purpose of formulas it’s still just a number 597.
excel custom number formats
Formatting cells for credit card numbers
You might be thinking you can use a custom format of 0000 0000 0000 0000 for credit card numbers, but you’ll find that it will only work for cards where the last number of the card is a zero! Try it out and see for yourself.
The workaround is to use a formula.  This requires entering the number in one cell, and then in another cell you need to enter the following formula (assuming our credit card number is in cell A1):
=LEFT(A1,4) & ” ” & MID(A1,5,4) & ” ” & MID(A1,9,4) & ” ” & RIGHT(A1,4)
Note: I’ve added spaces in the formula for clarity.
Some explanation:
  • The LEFT, MID and RIGHT returns text from a specified position in a cell.
  • The ampersands ‘&’ join text together
  • The “ “ adds a space between each group of text
Download the quick reference table below and print it off or save it for future use.
Excel custom number and text formats


Did you like this tutorial or could it have been better? Let me know your thoughts in the comments below.
Share this with your friends and colleagues on Twitter, and Facebook etc. using the shortcuts below.

Comments

Popular posts from this blog

50 Excel VBA Oral Interview Questions