I need to begin this lesson with some words of caution, because in this lesson we're going to format some numbers. And when you format numbers in Excel there's a huge pitfall you can fall into that will cause your worksheets to produce inaccurate results.

Let me demonstrate. I'll type a number into a cell. Let me type 123.4567.

If I now round this number to two decimal places it will round to 123.46. Let's do that now. I'll click the number, then Home tab on the Ribbon and, in the Number group, I'll click the Decrease Decimal button twice. And you can see that the rounding has occurred, but you can also see the true value in cell G2 on the Formula Bar. And the value is still 123.4567.

Now let me show you how this can cause inaccuracies in mathematical calculations. In cell G4 I'll type the value 1.4, and in cell G5 I'll also type 1.4. And in G6 I'll add an AutoSum function that will add the two numbers together. To do that it's Home tab on the Ribbon and, in the Editing group, I click the AutoSum button once and once again. And you can see that the answer is correctly reported as 2.8.

But now consider what would happen if I rounded all of these values to 0 decimal places. 1.4 would round to the whole number 1, but 2.8 would round up to the whole number 3.

Let's see that happen now. I'll select cells G4 to G6. Once again the Home tab on the Ribbon and, in the Number group, I'll click the Decrease Decimal button once.

And I now have the impossible situation where 1 plus 1 equals 3. This is sometimes called a penny rounding error problem. And I've seen this problem occur many times in commercial worksheets. We're going to discover a solution to this problem at the end of this lesson.

So now let's open the sample worksheet for this lesson. You'll find it in the: Session 4 Sample Files folder, and the sample file you need is: Sales Week Ended 14th March 2008-1.

Let's first look at the values in Column E. You can see that some of them don't look so good.

Look at the value in cell E6. It's simply 500, but wouldn't it be nicer if it was shown as 500.00? And look at the value in cell E5: 1174.75. Wouldn't it be nicer if there was a 1000 separator: 1,174.75?

Well there's a wonderful style in Excel called the Comma style. And with a single click you can format numbers to two decimal places with a comma separator. Let's do that now.

First of all I'll select all of Column E by clicking in the Column E header. Then it's the Home tab on the Ribbon and, in the Number group, there's the Comma style.

When I click the Comma style I can see that some of the numbers are now too wide for the column. So I'll hover the mouse over the intersection between columns E and F and then click and drag slightly to widen the column. And you can see that all of the numbers now look a lot better. They're all shown to two decimal places and we have comma separators where the number includes thousands.

You can see that that the values in Column G also contain financial values. So let's apply the same Comma style to Column G. I'll click in the Column G Column Header, then the Home tab on the Ribbon, Number group, and the Comma style. And that looks a lot better.

While I'm introducing the Comma style, let's also introduce the Accounting number style. That's just the same as the Comma style but it includes a currency symbol. So let's add a currency symbol to the values shown in Column E.

I'll click on the Column E Header to select the entire column and it's the Home tab again on the Ribbon, the Number group, and here's the Accounting number format.

Notice this is a Split button, and if I click the dropdown on the right of the split button you can see five of the world's leading currencies. I can apply the US dollar, the English Pound, the Euro, the Chinese Yen or the Swiss Franc, with a click of the mouse. But let's now remove the currency symbol by reapplying the Comma style.

Now let's look at the values in Column F. As I'm sure you're aware, to apply seventeen and a half percent as a tax rate you'll need to multiply by 0.175. So 0.175 is a very common way to express percentages in order to make it useful in mathematical calculations. But from an appearance point of view it would be nicer if Column F contained the values as 17.5%.

In order to make percentages both readable and easy to use, Microsoft has created the Percentage style. I'm going to apply that now to the values in Column F. So I'm going to click on the Column F Header to select the entire column, then it's the Home tab on the Ribbon, the Number group again, and I'll click the Percentage style. And when I click, the 0.175 numbers are now showing as 18%.

Well that's not quite what I wanted, but the Percentage style shows whole numbers by default, so I just need to add one decimal place.

Once again Home tab on Ribbon, Number group, and I'll click the Increase Decimal button once. And that looks a lot better.

Remember that the value in the cell is still 0.175, it's just being displayed (or "formatted") so that it appears as 17.5% for visual reasons.

Now we need to look at that penny rounding problem. I'm going to AutoSum Column G by clicking in cell G18 and then Home on the Ribbon, Editing group, AutoSum, and I'll click the AutoSum button once more to come up with a total of 18,137.37.

Now you may be surprised if I tell you that, if you were to take out your pocket calculator and add up all of the numbers in the range G2 to G17, it wouldn't add up to 18,137.37; it would add up to 18,137.38. The reason for that is the penny rounding problem that I discussed at the beginning of this lesson.

Let me bring back the values to five decimal places in Column G, and the problem may become more apparent. So I'll select all of Column G and then click the Increase Decimal button three times. And I'll widen Column G so that we can see all of the values. So you can see that each individual value in Column G was rounded to the nearest penny. But the value in G18 is the sum of the actual numbers in the cells and they're rounding to a penny less than the rounded amounts displayed in the cells.

I'm now going to describe two solutions to this problem. One is the best practice solution and the only solution you should use. And the other solution is truly awful.

You might wonder why I'm teaching you the truly awful method. Well it's because somebody may have configured Excel to use the truly awful method and you may find it very difficult to understand what's happening with the calculations on a copy of Excel that's been configured in this way.

Here's the awful method. Click on the File tab on the Ribbon, then Options, then Advanced, and then scroll down the list until you see the "When calculating this workbook" group.

Notice there's a checkbox here: "Set precision as displayed". This will tell Excel to completely ignore the value in all of the cells, and treat the value as if it was really the value that's displayed in the cell rather than the true value of the cell.

I'm going to click that checkbox now, with a warning that you should never really do this. When I click, you can see that Microsoft think it's a pretty bad idea too and they're warning me that data will permanently lose accuracy. I'm also messing up this copy of Excel completely, because this setting will persist for all future Excel sessions. So I'll click OK and then click OK again.

Now let's look at the value in cell G18. It's still correctly calculating as 18,137.37050, but I'll now format Column G with the Comma style. So I click the Column G Header to select it, and on the Home tab on the Ribbon, in the Number group, I'll click the Comma style.

And now look at the value in cell G18. It's changed from 18,137.37 to 18,137.38. It actually now is correctly calculating based on the displayed values in Column G, rather than the actual values in Column G. But I must stress it's a really, really bad idea to configure Excel in this way.

So we'll now put things back to normal and explore the best practice solution. So I'll click File again and Options once more and Advanced. I'll scroll down again until I come to that "When calculating this workbook" group and I'll clear the checkbox that says: "Set precision as displayed", and then click OK.

And you can see that the value at the bottom of Column G is now displaying as 18,137.37 again. I'll now format the values in Column G so that they once again show five decimal places. So it's Home tab on the Ribbon, Number group, and Increase Decimal 1, 2, 3 times.

Now let's look at the correct way to round values. That is to round them at the point of calculation.

Let's look at the formula in cell G2. You can see it's E2 (that's the Amount), multiplied by the Tax: that's the 17.5% in F2 plus 1. The reason 1's being added to 17.5% is because that comes to 1.175 and it will calculate the total including tax. If you were to simply multiply by F2 you'd only get the tax charged, not the amount including the tax.

We're now going to leverage on the skills that you learned in Lesson 2-12, when you learned how to use Excel functions, because there's an Excel function called ROUND that's designed specifically to solve this problem.

I'm going to click in front of E2 in the Formula Bar and type on the keyboard RO. And when I do that, you see all the functions in the Excel Function Library beginning with RO, and the second one is called ROUND. So I'll press the Down Arrow key to select ROUND and you can see that the single line tip says: "Rounds a number to a specified number of digits", and that's exactly what we want. So I'll press the Tab key on the keyboard and you can see that the Syntax Box has now popped up underneath the ROUND function.

We discussed the Syntax Box in depth in Lesson 2-12. You can see that the ROUND function accepts two arguments: the number to be rounded and the number of digits to round to the number to. And the number of digits is 2 of course, so I'll click just after the closing bracket, comma, and the argument 2. And then I'll close the bracket. When I press the Enter key, you can see that the value in cell G2 is now rounded to two decimal places. I'll now AutoFill that formula to the bottom of the range. So I select cell G2 and then click on the AutoFill button and AutoFill to the end of the range. And did you notice the value in cell G18 changed from 18,137.37 to 18,137.38?

And now let's format Column G so that, once again, it shows two decimal places. So I'll select all of Column G and then Home on the Ribbon, Number group, and I'll apply the Comma style again. And you can see that now I have the correct number again at the bottom of Column G: 18137.38.

This penny rounding problem in Excel has confounded many professional accountants over the years. And in my Excel classes, I've had a huge number of students bring workbooks with them completely baffled as to why their columns of figures don't add up properly. So you need to always be aware of this penny rounding problem when working with financial values in Excel.

I'll now delete that total at the bottom of Column G because I only added it to demonstrate the penny rounding problem.

All that remains now is to save your work. I'm going to save to my computer. I click the Browse button and I'll save with the new name: Sales Week Ended 14th March 2008-2. And as usual I'll save to the folder above my Sample Files Folder.

I click the Save button and you've now completed Lesson 4-3: Format Numbers Using Built In Number Formats.