Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Entered Values are Divided by 100.

Entered Values are Divided by 100

by Allen Wyatt
(last updated August 10, 2015)

Christopher noted that if he enters 1 into a cell, Excel automatically converts it to .01. He wonders why the value is being divided by 100.

There are a few different things that could be causing the problem. The most likely is that Excel is configured so it automatically places a decimal point in your entry. You can tell if this is the case by looking at the status bar; if it has the words "Fixed Decimal" appear there, then this is the cause. Turn it off by following these steps:

  1. Choose Options from the Tools menu. Excel displays the Options dialog box.
  2. Make sure the Edit tab is selected. (See Figure 1.)
  3. Figure 1. The Edit tab of the Options dialog box.

  4. Make sure the Fixed Decimal check box is cleared.
  5. Click OK.

You should note that any values entered with "fixed decimal" entry turned on won't change back to their original values when it is turned off; the decimal point on the values is actually moved the number of places indicated in the setting.

If the problem persists when you keep entering data, it could be because of some format applied to the cell. In this case, the value isn't being changed as you enter it; instead the appearance of the value is controlled by whatever format is applied to the cell or cells. The easiest way to check this out is to select the cell and then press Shift+Ctrl+~ (that's the tilde character, found just below the Esc key). If the value changes back to what you expect, then you know you've got a formatting issue that you need to correct.

Finally, it may also be possible that there is a macro intercepting what you enter into the cell and dividing it by 100. This is particularly likely if you know that there are macros in the workbook and the data transformation occurs only when you enter the value into a limited number of cells. The normal place to do this type of change is in the Worksheet_Change or Workbook_Change event handlers.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7452) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Entered Values are Divided by 100.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Inserting a File

Need to combine several files into a single document? You can do it by inserting one file into another, as outlined in this ...

Discover More

Replacing Quoted Text with Italics

If you have text surrounded by quotes in a document, you may want to remove the quote marks and make the text that was within ...

Discover More

Defining a Shortcut for a Macro

You can make running macros very easy if you assign a shortcut key to the macro. This tip demonstrates how easy it is to ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

MORE EXCELTIPS (MENU)

Accepting Only a Single Digit

Want a quick way to enter a series of single digits into consecutive cells? The best approach is with a macro, and this tip ...

Discover More

Requiring Input

If you distribute a workbook that is used by others for data entry, you may want a way to make sure they fill in certain ...

Discover More

Quickly Entering Dates and Times

Excel provides keyboard shortcuts for a variety of purposes. This tip examines two such shortcuts, designed to allow entering ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share