# Thread: formating in millions (2003)

1. ## formating in millions (2003)

Hello,

I have a spreadsheet that has a cell with the number \$209,864,250. I need to link this cell to another worksheet which I am able to do. The problem is that on the other spreadsheet I would like the cell be formatted to show \$209.86 which is the amount in millions. Any help would be great.

2. ## Re: formating in millions (2003)

Welcome to Woody's Lounge!

You can use a custom number format:

- select the cell
- select Format | Cells...
- select the Custom category in the Number tab
- enter <code>0,,.00</code> in the Type box
- click OK

The commas <code>,,</code> tell Excel to display millions (one comma would display thousands), and the <code>.00</code> specifies that you want to display 2 digits after that.

3. ## Re: formating in millions (2003)

Thank you Hans. It worked great.

4. ## Re: formating in millions (2003)

Use this:
<code>
0.00,,_);<!t>[Red]<!/t>(0.00,,)
</code>
The first section is for positive numbers (and zero), the second section after the semi-colon for negative numbers. The _) in the first section adds a space after the number with the same width as a closing paretnhesis ), so that positive and negative numbers are aligned correctly.

5. ## Re: formating in millions (2003)

Hans,

One more question. How do I have it so it formats with the same as above, but in case the number is negative with the parenthesis and in red? I also have a sum that does not add the totals right. It seems that it is of by 1. How can I fix this? Thanks.

6. ## Re: formating in millions (2003)

Thanks Hans for the information. How about when I have a sum, it seems that the addition is short by 1 everytime? Any ideas on how to fix this. By the way thanks for all the help and I hope I am not asking too much.

7. ## Re: formating in millions (2003)

When you format numbers as in this thread, you are rounding the displayed value. The actual number stored by Excel has more digits than are shown on screen. Excel performs all calculations by default using the stored values, not the displayed values. This can lead to apparent discrepancies in calculations. Here is an example:

<table border=1><tr><td align=right>

8. ## Re: formating in millions (2003)

Thanks Hans this is what I needed.

9. ## Re: formating in millions (2003)

In addition to what Hans said, using the Precision as displayed option will affect all values in the workbook, and this can cause other unexpected results. If you don't want to use that option, then there is another solution. If the original value (the cell where the \$209,864,250 is) is in cell A1, then in the cell where you want the value in millions you can use the formula =ROUND(A1/100000,2). Then format that cell normally, not with the special format Hans gave you. You should now be able to add up the values and get the expected answer, and you haven't affected the precision of any other values in the workbook.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•