# Thread: Automatically divide all numbers by 100 (2003)

1. Is there any way that when I type a number into a cell, it will automatically divide that number by 100 - eg If I type 1000, it would display as 10. Perhaps using a Custom Format??

2. [quote name='GillHarding' post='774653' date='11-May-2009 13:37']Is there any way that when I type a number into a cell, it will automatically divide that number by 100 - eg If I type 1000, it would display as 10. Perhaps using a Custom Format??[/quote]
The simplest solution to this kind of problem is to type the value into one cell and have the calculation carried out in a different cell. Is there a real need to have the divide by 100 take place in the same cell?

I've come up with a Custom Format to effectively divide by 1,000 (#,###,) but don't seem to be able to make it divide by 100 using this method.

I know I could put a formula in a different column but I want to be able to type say 1000 in a cell and for it to display as 10. I've tried Paste Special, Divide which would do the trick after the event, but not as I type the number in. I've also discovered the Option for Fixed No of Decimal places in the Options menu and if I set this to 2, it seems to do what I want. However, this only seems to work on new numbers I type in, not on existing numbers.

Hope this makes sense! If anybody can come up with a different idea, I'd be extremely grateful.

I've come up with a Custom Format to effectively divide by 1,000 (#,###,) but don't seem to be able to make it divide by 100 using this method.

I know I could put a formula in a different column but I want to be able to type say 1000 in a cell and for it to display as 10. I've tried Paste Special, Divide which would do the trick after the event, but not as I type the number in. I've also discovered the Option for Fixed No of Decimal places in the Options menu and if I set this to 2, it seems to do what I want. However, this only seems to work on new numbers I type in, not on existing numbers.

Hope this makes sense! If anybody can come up with a different idea, I'd be extremely grateful.[/quote]
Hi Gill,

Here's a way to achieve the display you're after, but it may adversely affect any calculations you're doing. That's because it actually multiplies the values by 10.

1. Define a cutom number format as '#,' or '#,###,'
2. Go to Tools|Options|Edit and set the 'fixed decimal places' value to '-1'

This won't update any existing numbers. However, if you simply select a cell's value in the formula bar and press <Enter>, the contents willbe multiplied by 10 - which can be both a blessing and a curse.

You have been warned.

5. Why not use copy- paste special to divide the existing numbers by 100, then use the fixed decimals to do it for new numbers. The Paste special will only have to be done once....

Steve

6. Well, you learn something new every day! I had no idea that it was possible to set the Fixed Number of Decimals to a negative number.

Thank you both very much for your help. It's much appreciated.

Gill

7. [quote name='GillHarding' post='774653' date='11-May-2009 12:37']Is there any way that when I type a number into a cell, it will automatically divide that number by 100 - eg If I type 1000, it would display as 10. Perhaps using a Custom Format??[/quote]

From Excel's top panel commandbar you can use
Tools->Options->Edit
..then tick the checkbox for Fixed decimal places and set the value to 2

zeddy

#### Posting Permissions

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