Custom Formats
Excel Training Level 3 Lesson 5-Excel 97-2003
Custom Formats
Included
in this lesson is some valuable information on Custom formats.
To access Excel’s Custom Formats go to Format>Cells-Number-Custom. Then all
we need to do is use any one of Excel’s existing functions as our starting point
and change the format to what we want. Although we are going over an existing
format, we will not be replacing it but rather adding to the list.
Excel offers us a very rich selection of formats that we can apply to our
cells in a Worksheet. But there are times we would like to add our own unique
type of format to a cell or range of cells. This is where
Custom Formats
can become very helpful. Before we start though, it is very important to
understand that altering the format of any cell, does not change its underlying value.
We can always see the underlying value of a cell by selecting it and looking in
the Formula bar or by selecting it and pushing F2. The reason we mention this is
because many people believe they can change a cell value by applying a different
format. For example try this
-
Type the number
5.6 in cell
A1
-
Type the number
5.6 in cell
A2
-
Select cells
A1 and
A2 and go to
Format>Cells-Number-Custom
-
Type the Format
0 and click
OK
-
Put the formula
=A1+A2 in cell A3
As you can see you have a result of 11. A result like this could be very confusing and cause many errors
in a spreadsheet. We have seen this many times where people have used Excel to
perform a calculation and when they cross check the result with a calculator
they believe that Excel has given them an erroneous result.
This would be a good time to mention Excel’s Option –
Precision as displayed. Make sure you are in a Workbook you do
not need. Go to Tools>Options-Calculation and under
Workbook options check the
Precision as displayed option button
and click OK. Say yes to the
warning, but take heed! Now double click in cell A3 (the one containing
A1+A2) and push enter. As you can see Excel will now give a result of 12. This operation cannot be undone! Close
the Workbook without saving.
The default formatting for all cells is the General format. Excel
will always try to make an educated guess on the type of formatting you want by
the way in which you enter a value into a cell.
If, for instance, you type a
date Excel will apply the default date format as set in the Control Panel of
Windows. If we type a currency symbol Excel will apply a currency format, type a
percentage sign and Excel applies a Percentage format and so on. We can alter
the default Time, Date, Currency and Number formats by going to Start>Settings>Control
Panel>Regional Settings. Be aware though that the settings apply to all
other applications, not just Excel.
Placeholders
When we use Custom Formats we can use what are called Placeholders to
tell Excel what format to apply. The type of placeholders that are used
are:
From Excels
Help
To format fractions or numbers with decimal points, include the following
digit placeholders in a section. If the number has more digits to the right of
the decimal point than there are placeholders in the format, the number is
rounded to as many decimal places as there are placeholders. If the number has
more digits to the left of the decimal point than there are placeholders in the
format, the extra digits are displayed. If the format contains only number signs
(#) to the left of the decimal point, numbers less than 1 begin with a decimal
point.
-
#
displays only
significant digits; does not display insignificant zeros.
-
0 (zero) displays insignificant zeros if a
number has fewer digits than there are zeros in the
format.
-
? adds spaces for
insignificant zeros on either side of the decimal point, so that decimal
points
align. You can also use this symbol for fractions that have varying
numbers of digits.
End of Excel's
help
We could possibly
tie ourselves in knots and confuse a relatively simple issue by trying to explain
this in words. What we believe is a simpler method is to apply a standard number
format to a cell, then select Custom and you will see how the Placeholders are
used.
Sections
Lets now look at the basis that Excel uses for number formats. The good
thing here is once we understand this we can use Custom Formats to inform users
of possible errors. We can do this by changing the font colour and/or the
display of the number entered. However, the colour display is not really
applicable any longer as since Excel 97 we can use Conditional Formatting
We will use a small example to explain how number formats work in Excel.
Just like an Excel Function has a syntax, so to do Number Formats! In
this context though the correct term is
Sections
and each section must be separated by semicolons.
Positive;Negative;Zero
We can elaborate on this by using:
Do
this if Positive; Do this if Negative; Do this if Zero
So we could use a Custom format like this:
"1 or higher";"less than 1";"Zero"
Then
when we type a number in the cell that has the formatting it will tell us what
the number is!
Try this.
-
Click in any blank cell on an Excel worksheet.
-
Go to Format>Cells-General-Custom and under Type enter the format
"1 or higher";"less than 1";"Zero"
-
Click OK, then go to any blank cell and then type in a number
and the Custom Format will be displayed.
Colours
We could now take this one more step by
using a Custom Format like
[Blue]"1 or higher";[Red]"less than 1";[Green]"Zero":
This will not only display the appropriate text, but also a different
coloured font. So while Conditional Formatting has made the Custom Format
redundant as far as font colour goes, we can still use it for applying other
format types. The only colours we can use with Custom Formats are: [BLACK]; [BLUE];[CYAN] [GREEN];[MAGENTA] [RED];[WHITE];[YELLOW]
We mention above that the Sections for a Number format are Positive;Negative;Zero. While this is
true for numbers there is one more Section that follows after Zero. This is Text, so the full syntax (Sections) for formatting is
Positive;Negative;Zero;Text. By using this we can tell Excel do to
another Format type if text is typed into a cell. Using the same Custom Format
we applied to our number, change it to this:
[Blue]"1 or higher";[Red]"less than 1";[Green]"Zero";[Cyan]"No text please"
Now type any text in the cell and we will see "No text please" with a
font colour of Cyan in the cell.
Suppressing
Values
Ok, so we now know that the set order, segment, syntax (whichever) is
Positive;Negative;Zero lets use this
for a cell or range of cells so that we suppress certain values, eg; We do not
want to see zeros in these cells, in this case we could use a format like:
General;-0;;@
In this example we have told Excel to not display a value of Zero. We
have achieved this by omitting the Zero segment in the Custom Format. We could
of course tell Excel to not display certain values by omitting a format in the
appropriate segment. This one will not display text
0;-0;0;
We can use this one to suppress error values in a cell that should return
a number. This is because an error value is text. However be cautious when doing
this as the error value is for a reason. To hide ALL entries in a cell we can
apply the format;
;;;
But again be cautious when using this.
Comparison
Operators
We can also use comparison operators in Custom Formats and apply a format
accordingly. Lets say we wanted to
show decimal places for all values above 10, but no decimals for values below.
To achieve this we would use the Custom Format
[>10]0.00;0
This is saying: If
the cell value is greater than ten apply the format 0.00 otherwise use the format
0
Leading
Zeros
Possibly one of the most frequent questions we get is how can we have cells
display leading zeros without
applying a Text format to the cells. Well the answer is very simple. Lets
assume we have a thousand rows of numbers in Column A of a Worksheet. The
numbers range from 1 to 5000 and we want to have all numbers be displayed as six
digits with leading zeros, eg;
000254
000012
004587
000001
To
have our cells displayed like this we would apply the Custom Format of 000000
Displaying
Symbols
We will now look at using symbols in cells automatically. Lets say we
have a certain column in our spreadsheet where we want to collect temperatures.
Rather than having to use the Character map we can apply a Custom Format. This
is how we could do this;
-
Select the cells
or the column
-
Go to
Format>Cells-Number-Custom
-
Type 0.00 then
hold down the Alt key and push 0176 on the numeric keypad and then
release the Alt key.
All our cells will now automatically display the degree symbol after the
number entered.
There many symbols we can use in Custom Formats and we can get the
numeric code via the Character map or in Excel by going into the VBE (Visual
Basic Editor) and typing "ASCII" into the help.
-
Push Alt+F11 and then push F1
-
Type ASCII into the help
wizard.
-
Then select Character Set (0–127) and/or Character Set (128 –
255)
-
It is worth
taking a print out of these, as you will be surprised how handy they
are!
-
Push Alt+Q to return to Excel.
When you use these you must always hold down the Alt key, type a zero
followed by the Symbols ASCII number.
Currency
Formats
The last one we will look at is a very handy currency format we can apply
to cells. Select a range of cells and type this format in the Custom format box;
0 "dollars and" .00 "cents"
Now type any dollar amount in the cells.
Custom
Format Summary
So as you can see Excels Custom Format feature can be used in a lot more
ways than first meets the eye. The good part of it all is that no matter what
format we apply, the cells underlying value will not change. This is great
because even if our cell is displaying a format like
22
dollars and .25 cents
We can still use this in calculations. This means we can make our
spreadsheets a lot easier to read and have certain formats applied depending on
a cells value.
Be very careful if you apply the "precision as displayed" option
as it cannot be undone and could ruin a perfectly good spreadsheet, I would
strongly suggest NOT using it at all.
