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 2007 doesn't support brackets in any of the built-in or pre-defined 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 2007. 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. Zeros mean 'display significant zeros'. 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: 0. This means: don't display any trailing zeros. 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 Hash (#) 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 Hash doesn't really do very much, but it does round to a whole number. In this case, because it's: 123.45, the .45 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 has been 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 Hash 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 zeros? 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 and 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 theory, let's now go on and do the same thing in Excel. For this lesson, we want to open: Sales Week Ended 14th March 2008-2. And this will already be open, and already be in this state, if you're working through the course sequentially. We want to format columns E and G so that they have brackets around any negative values. And you can see the negative values there, in row 8. To select columns E and G, you need to use the skills that you learned in Lesson 2-6. Select column E, hold down the Ctrl key on the keyboard, and then click the column header for column G. We've now selected the non-contiguous range of everything in column E, along with everything in column G. We now need to right-click anywhere in that selected area, and choose Format Cells from the Shortcut menu. Now, this is going to be a custom format, so make sure you have the Number tab clicked, and also the Custom category. And you can see a lot of the built-in number formats in Excel, many of which will, perhaps, be starting to make sense to you a little bit more now. I'm going to create my own custom string from scratch, and the custom string is going to be: #,#0.00. That's the positive part of the string. And now, for the negative part, I need to add a Semicolon, and then an opening bracket, because negative values will be in brackets. And, once again, #,#0.00. And, this time, I'll add a closing bracket, so that the negative number will be completely encased in brackets. When I click the OK button, you'll see that the values in row 8 now have brackets around them, clearly identifying which numbers are negative. This was an introduction to custom number formats, but with the information you now have, you're ready to go on and explore the Excel Help, and find out everything you want to know about custom formats, if you run into a format that you need that isn't already pre-defined in Excel. We'll now save this workbook, and we'll save it as: Week Ending 14th March 2008-3. And we'll put this into our Practice folder, as usual. Click the Save button, and we're done.