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
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.
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
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
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
align. You can also use this symbol for fractions that have varying
numbers of digits.
? 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
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.
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
and each section must be separated by semicolons.
We can elaborate on this by using:
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!
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.
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.
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:
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
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.
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
This is saying: If the cell value is greater than ten apply the format 0.00 otherwise use the format 0
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;
To have our cells displayed like this we would apply the Custom Format of 000000
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
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.
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.
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
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.