 |
|
|
|
|
Download the associated Workbook for this lesson
To access Excel’s Custom Formats go to Format>Cells-Number-Custom. Then all you need to do is use any one of Excel’s existing functions as your starting point and change the format to what you 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. However, there are times you may like to add your own unique type of format to a cell or range of cells. This is where Custom Formats can become very helpful.
Before we start 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 to it. 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, the result could be very confusing and cause many errors in a spreadsheet. This is very common and we have seen many times where people have used Excel to perform a calculation, but 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
Under Workbook options check the Precision as displayed option button
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>Control Panel>Regional and Language Options. 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 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 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.
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 syntax, so to do Number Formats! In this context though the correct term is Sections and each section must be separated by a semicolon.
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"
Try applying the above format 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 mentioned 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:
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.
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
Possibly one of the most frequent questions we are asked is "How can I 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
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. Try it:
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! Now 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
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, We would strongly suggest NOT using it at all.