Do you want to enter data in to cells in a specific format but Excel won’t let you?

In this tutorial we will look at formatting cells to accept data in specific formats without Excel mucking them up.

This tutorial is broken down in to two parts. The first details how to apply the formatting and the second part is a guide to the formatting codes that you can use.

Part 1 – Applying the formatting

Step 1 – Select the cell or cells that you want to apply your custom number formatting to.

Step 2 – Right click the cells and select “Format cells…”

Step 3 – Under the “Number” tab select the “Custom” option from the list

Note – At this point you will see a wide variety of pre-made custom formats and when you click on one a bit of sample data will be shown at the top.

You may find a pre-defined format is similar to the one that you want. Select the one that is nearest and try editing under the “Type:” option.

Step 4 – Type the formatting into the “Type” field and click on OK

Part 2 – Custom formatting code

Each custom format is made up of a code letters and symbols which can be a bit confusing to the first time user, so below is a handy guide to how the most common codes are used, along with some examples of their use.

Number codes

General General number format or no formatting
# Represents where the number should appear in a string of other characters. Example – entering ‘100’ into a cell formatted as ‘A##A’ would display as ‘A100A’
0 (Zero) – Place holder for 0’s needed to fill out the padding. Example – if you set the custom formatting to ‘0000’ and entered ‘1’ into the cell it would read ‘0001’, ‘10’ would read as ‘0010’ etc.

Alternatively, to ensure a number was always displayed to decimal places, you can format a cell as ‘#.00’ – Now when ‘8.9’ is entered it will be displayed as ‘8.90’
? Place holder for insignificant 0’s. Inserts a space instead of any insignificant 0’s. Example – format two cells above each over, as ‘0.0?’ and enter ‘1.10’ in the top one and ‘1.11’ in the lower one. If you look at the top cell, the 0 has been replaced with a space.

This is different from the normally rounding down process where the number just moves move to the right to eliminate the gap. Using this method the decimal place in both cells remains in line with each other.
. (Decimal place) Indicates where a decimal place should appear in a number. Example – formatting a cell as ‘#.’ and entering the number ‘111’ would be displayed as ‘111.’
% Converts the number to a percentage by multiplying by 100 and adding the ‘%’ character
, (Comma) Used to break numbers down in readable numbers. Example – Cell formatted as ‘#,###,###’ would display ‘1000000’ as ‘1,000,000’

Text Codes

$ – + / ( ) :
space		 These will be displayed inside the number. Note to display other characters either put them in quotation marks or put a backslash(\) before them.

Example, format a cell as ‘(#)’ and brackets will appear around anything entered in to that cell. Format it as ‘\A#’ and the letter ‘A’ will appear before anything entered in to that cell (‘1000’ would be displayed as ‘A1000’)
\ See above
“text” This will display any text entered between the quotation marks. Example, a cell formatted as ‘“Test “#’, will display ‘Test 1000’ when ‘1000’ is entered in to it
* This will repeat the character to the right of this code, to fill the column width. Example, if you format a cell as ‘#*>‘ and entered ‘100’ it would be displayed as ‘100>>>>>>>‘ with the number of >‘s displayed being dependent on the width of that column.
_ (Underscore) Creates a space in the cell the width of the character to the right. Example, a cell formatted as ‘#_)’ will display the number entered with a gap to the right of it, the width of the ‘)’ character. Great when trying to line up positive numbers with negative numbers enclosed in parentheses.
@ This is a text placeholder for the text entered in to a cell. Example, if you enter the text ‘is a’ into a cell that is formatted ‘”This “@”Test”’ it would be displayed as ‘This is a test’

Date codes

When a date is displayed in Excel it is broken down in to its component parts (days, months and years) and each of these can be displayed differently depending on how we format them

m Month as a number (1 to 12)
mm Month as a number but with leading zeros (01 to 12)
mmm Short version of month in text (Jan to Dec)
mmmm Full version of month in text (January to December)
d Day as a number (1 to 31)
dd Day as a number but with leading zeros (01 to 31)
ddd Short version of the day in text (Sun to Sat)
dddd Full version of the day in text (Sunday to Saturday)
yy Year in a 2 digit format (e.g. year 2010 would be 10)
yyyy Year in a 4 digit format

Time codes

As with dates, times are broken down in to their component parts (hours, minutes and seconds) and each of these can be formatted separately before being displayed.

h Hours as a number (0 to 23)
hh Hours as a number but with leading zeroes (00 to 23)
m Minutes as a number (0 to 59)
mm Minutes as a number but with leading zeros (00 to 59)
s Seconds as a number (0 to 59)
ss Seconds as a number but with leading zeros (00 to 59)
AM/PM (or am/pm) Displays if AM or PM time

Miscellaneous codes

Colors To display the cells in different colors you can use BLACK], [BLUE], [CYAN], [GREEN], [MEGENTA], [RED], [WHITE], [YELLOW]

Alternatively you can use [COLOR n] where n is 1 to 56 of the Excel color palette.
Conditions It is possible to use the conditions <, >, =, >=, <= and <> to set up conditional formatting. For example if you used the formatting [Red] [<=100] ; [Blue] [>100]

Numbers equal or less than 100 will be displayed in red, where as anything over 100 will be displayed in blue.

-

-

-

Share and Enjoy:
  • Digg
  • Twitter
  • del.icio.us
  • StumbleUpon
  • LinkedIn
  • PDF
  • Print
  • email