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.

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 Formats	Text Before Formatting	Custom Format	Formatted 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 full	27/03/2010	dddd	Saturday
Day, date, month and year	27/03/2010	ddd dd mmm yyyy	Sat 27 Mar 2010
Month	27/03/2010	mmmm	March
Phone Numbers	755551234	00 0000 0000	07 5555 1234
Phone Numbers with Brackets	755551234	(00) 0000 0000	(07) 5555 1234
Fractions	10.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 @*.

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.

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.

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.