The built-in number formats are very quick and convenient but are also quite limited. For example, there was a credit note on the 11th of March 2008 for £637.49 plus tax. You could easily miss the little minus sign to the left of the amount. Old School accountants like to show brackets around negative values: you can't miss the brackets.
So here's what we want to see.
There's one little problem with this requirement: Excel 2013 doesn't support brackets in any of the Built-In or predefined custom formats.
Fortunately it's possible to create your own custom format when none of the built-in styles fit your requirement. There's plenty of documentation in the Help System and on the Internet about the rather cryptic formatting codes provided with Excel 2013. Just about everything's possible once you've got to grips with the basic concepts.
To communicate the custom format to Excel you must construct a custom format string. Zeroes mean: display significant zeroes. You tell Excel how many you want within the format string. For example, "0.00" means: "Display one leading zero and two decimal places."
The following examples should make things clear.
In the first example the Custom Format String is simply zero. This means: "Don't display any trailing zeroes". In other words: "Round the number to a whole number"; so 1234.56 is being rounded up to 1235.
The second example, "0.0" means "Display one decimal place." So you can see the .56 being rounded up to .6.
The third example "0.00" means: "Two decimal places". So in the case of 1234.5 we're getting .50. A trailing zero is being added to make sure that we always have two decimal places.
And in the fourth example you can see this happening with both a leading and a trailing zero. We're asking for two numbers before the decimal point and three numbers after the decimal point, changing 4.56 into 04.560.
And in the last example I'm not asking for any leading numbers but three decimal places 0.000. This will change 1234.56 into 1234.560.
As well as zeros we can also use # symbols in Custom Strings. The # symbol is mainly used to add comma separators to thousands and millions.
Here are some examples.
In the first example a single # doesn't really do very much, but it does round to a whole number. In this case, because it's 123.45, the .5 is being rounded down to 123.
#.## is similar to the 0.00 but it will not add a trailing zero, so 123.50 changes to 123.5 when it's displayed.
The real use of the hash comes in the third example: "#,#". This will add commas in front of thousands and millions. In this case we have 1234 and the comma's being added after the thousand but, because there's no .##, the .56 is being rounded to 1234.
In the last example we've got #,#.##. In this case 1234.56 is showing the .56 and also adding the comma after the thousands.
And in the last example you can also see the comma being added after the millions in the 12 million.
In the third example, ".50", of course there's no trailing zero because we're using a # symbol and not a zero symbol. So what do you do when you want to have the thousand and million separators, but you also want trailing zeroes? Well in that case you need to combine the two: hashes and zeros.
Here's an example: "#,# 0.00". The #,# part of the format string tells Excel you want commas after the millions and the thousands. And the 0.00 part of the string tells it that you want a trailing zero so that you always have two numbers after the decimal point. And you can see that happening here: 12,341,234. The commas are there separating the millions of the thousands and also we've added a trailing zero on the 50.
Let's also look at how we can set a different string for positive and negative values. And this is going to be the key to adding those brackets that we were talking about earlier.
Here's an example of a custom format string that formats both positive and negative values. And the semicolon is used to delineate the positive and negative part of the format string. So "#,# 0.00" means: "Add the commas after the thousand and millions and two decimal places, adding a trailing zero if there's only one decimal place". Then we have the semicolon and then exactly the same thing, but this time enclosed in brackets, meaning that when the number's negative, we want Excel to put brackets around the numbers. And you can see in the examples exactly how that's working.
Well that's enough for the theory, let's now go on and do the same thing in Excel. For this lesson you need to open the sample file: Sales Week Ended 14th March 2008-2, from your Sample Files Folder.
Look at the values in cells E8 and G8. They're both negative values, but you could easily miss the leading minus sign.
In this lesson we're going to use a custom format to put brackets around any negative value in columns E and G.
I'll begin by selecting columns E and G using the skills that you learned in Lesson 2-6. So I'll click the Column Header of Column E to select the entire column, hold down the Control key on the keyboard, and then click the Column Header of Column G.
Now that I've selected both columns I need to bring up the Format Cells dialog. To do that I right click in any of the selected cells and select Format Cells from the Shortcut Menu.
In the Format Cells dialog I want to select the Custom category.
In the Custom category you can see all of Excel's predefined custom formats. And many of these should be making a little more sense to you by now. But the task for this lesson isn't to use a predefined Custom Format, but to create our own from scratch.
So I'll click in the Type box, remove anything that's currently there, and let's begin with the thousands separator. So I simply type #,#. I don't need to worry about the decimal places because I'll set those in the second part of the Custom Format by typing 0.00. This means that Excel will always display two, and only two, decimal places. This is the positive side of the custom number format.
I'm now going to define the format for negative numbers, and to do that I type a semicolon. The negative side will be exactly the same as the positive side except that I want negative numbers to be enclosed in brackets. So I'll type an opening bracket and then exactly the same #,# 0.00 format. And then I'll type a closing bracket.
All negative numbers should now be enclosed in brackets, have a thousand separator and have two, and only two, decimal places.
So I'll now click OK to see how well my custom number format has been applied.
I click OK and let's look at those values again in cells E8 and G8. And you can see that this has worked perfectly, And brackets will now be displayed around any negative number that's entered into any cell in columns E and G.
Custom Formats are a huge and involved subject, and in this single lesson it's only been possible to give you a broad overview. But this should be all that you need to discover more using Excel's help system or the internet.
I should say that custom formats aren't something that you tend to use in everyday business use of Excel, but occasionally you'll have a requirement to format a number in a very specific way. A way that isn't catered for by Excel's built-in formats or predefined custom number formats. But now that you understand custom number formats you'll be able to cater for any numerical formatting requirement in the future.
All that remains now is to save your work. And I'm going to save with the new name: Sales Week Ended 14th March 2003-3. And, as usual, I'll save in the folder above my Sample Files Folder.
I click the Save button and you've now completed: Lesson 4-4 Create Custom Number Formats.