Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!


Free Excel Forum

Decimal Places

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

When I enter 5526 in a cel, I want it to automatically be 55.26. Is this possible or do I have to enter in 5 5 . 2 6 everytime?

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Filter Data in Excel to Show Only the Top X Percent of that Data Set - AutoFilter
- This Excel macro filters a set of data in Excel and displays only the top X percent of that data. This means the highes
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Get the Number(s) Out of a Cell that Contains Both Text and Numbers in Excel - UDF
- This free Excel UDF allows you to get the numbers out of a cell that contain both text and numbers. This is a great fun
Enter Text/Characters with a Double-Click of the Mouse
- This macro allows you to enter any text or number, that you specify, in a cell on which you double-click. In the exampl
Format Cells in The Number (Numerical) Number Format in Excel
- This free Excel macro formats selected cells in the Number or Numerical number format in Excel. This means that the cel

Similar Topics

Numbers entered into a set of cells may have between 1 and 5 decimal places. If it is 1 decimal place and it is between .1 and .9 it enters fine (e.g.12712.9). But if I try and enter e.g.13712.0 into a cell it changes to 13712 automatically.

Any ideas how to stop it automatically cutting off the .0? Perhaps with a formula for forcing it to have between 1 and 5 decimal places..?

Help very much appreciated!

0 is also a possible value, if that makes any difference


When i open Excel and enter a number it automatically registers to 2 decimal places.

i.e if i enter 3 it shows as 0.03
if i enter 165 it retuns as 1.65

It never used to do this unless i formatted the sheet, but now it is doing this every time i open up a worksheet.

Can this be solved so that whenever i open Excel an enter 3 it shows as 3

Hope you can help

Kind regards


Here's a long standing issue which I haven't seen anybody answer. How do I get Excel to obey a decimal point I specify? For example...usually on calculating adding enter a number...say 1235 and when you hit the Enter shows 12.35 with the decimal exactly two places within the number punched into the number pad. But when I enter 1235 in Excel and I specifically list the decimal point to be 2 places...instead I get 1235.00.

How do I get Excel to put the decimal number exactly two places inside any number I enter into a cell? It could be 100 and the result should read 1.00 or if I enter 25600 the result should read 256.00. So my frustrating problem with Excel is when I specify the 2 decimal point...Excel simply dumps the 2 decimal point after the entire number is entered into that cell when instead...I want it to read any number but force it to place the decimal 2 places regardless of the number entered.

Thanks in advance.


I have a userform and an textbox on it, which has controlsource in cell A1.

If I enter decimal number with two decimal places (exapmle:12,34), everything is fine. But if i enter more than two decimal places, the number somehow "loses" comma, and becomes the normal (non-decimal number). For example, if I enter 12,3456 it becomes 123456. Why this happens? How can I solve this problem?

Thanks In advance

So the user enters data and they will need to enter a certain number of decimals.
Well if I just count the string length, somebody is can enter:


And they may be required to enter 4 decimal places.
So it seemed simple enough to do the below, but it is not working.
I keep getting the MsgBox.

If InStrRev(TextBox1, ".") < 5 Then
Cancel = True
MsgBox ("You must enter at least 4 decimal places.")
End If

Thanks all


I am assisting with the creation of something where the user has to be made to enter a number with 3 decimal places, even if they're all zeros. We've got the custom format set up so as to display as 0.000, but obviously this just makes the user able to enter "12", it display as "12.000", at which point the user shrugs and moves on. We want to insist that they enter those three decimal places.

Is there any way of using data validation to create an input mask to do this? Or perhaps a simple piece of VBA that runs when that cell is changed - any pointers?

Or do we have to get into user form territory if we really want to do this? My colleagues not keen on that as this is literally the only field that we've got problems with, and it would mean re-designing the whole complex model he's done already.


In Excel 2000 with fixed decimal 2 places in the setup, I could enter 10+.
and the cell would read 10.00. In 2003, the decimal is ignored and I must
enter 1000 to get 10.00. Is there a way to fix this change?

I have a problem in formating of decimal places when enter some value in a cell which is just like that "5.00" when i enter that type of value it converts only 5 how can i improve this
please help

I need to custom format some cells to -2 Decimal Places. (Automatically insert 2 decimal places to the Left)
In cell when I enter 1234 it would become 12.34
I don't like entering a Decimal Point 320 times 3 times a day!


Yes, I'm aware of going into: Excel Options-->Advanced-->Automatically Insert a Decimal Point-->2 Places.

BUT I don't want to change the whole Workbook.
I only want to change 320 cells in a Worksheet.
If it can be done through Excel Options there must be a way to do it in the cells.


I've also applied a Macro that (Divides by 100) but it isn't what I want either.

I've been trying to overcome this problem for over a year.
Please help!!! I'm in desperate need of assistance

ctrl + shift + 5 (%) formats the value into a percentage with no decimal places. is there any way to change this default to show 2 decimal places?

along those lines, is there keyboard shortcut to enter the format cell menu?

Good afternoon,
I have a regular sheet and set Decimal Places as zero using Format_Cells_Number. But it looks like it automatically round off or truncate my Decimal Places as zero. When I set Decimal Places as two, it produces the exactly results.
It puzzles me for a long time today.
Any questions will be welcome.

Hi Everyone

just after a little guidance please

i would like to do the following

a1 enter a value of 0 then b1 to equal 0
a1 enter a value of 0.0 then b1 to equal 1
a2 enter a value of 0.00 then b1 to equal 2

so basically it relates to the number of decimal places

hope someone can help

thanks in advance

I want to enter data into cells and not have to type the decimal point every
time. Is there any way to do this? I know you can set the number of places
behind the decimal point, but it still means you have to type the point every

Hi, I'm trying to work out a formula in excel which requires me to use Pi to over 30 decimal places. Excel will only let me have 30 decimal places of Pi whether I copy and paste it as a number or use its Pi function and even then seems to round up. Is there any way I can get more than 30 decimal places for this calculation? If not in excel then can anyone suggest another programme that may be capable of this?

You can post here or contact me at

Thanks in advance

Mini xxx

EDIT : I worked out how to get more decimal places but I also worked out that excel is rounding up after 14 decimal places which defeats the object of allowing me to see 30 decimal places somewhat... Is there any way I can prevent this auto rounding?


I have a cell which I have a percentage value in. If a user enters 10% I want it to display 10%, but if they enter 10.5% I want it to display 10.5%.

If I change decimal places to 2 it displays 10.00%.

So basically I want it to display a decimal if there is one and not round up if I enter 10.5%.

Any ideas?


By coincidence, two posts today have asked about Excel adding phantom
decimal places to entries:

I have experienced this as well: when I enter a dollar amount with just
2 decimal places and convert those values to variables in VBA code,
Excel adds trailing decimal places. These do not affect the dollar
value, but they do create problems when performing a comparison, such
If TotalBux = 0 Then (etc)
In this example, the value of TotalBux can be so close to zero that it
makes no effective difference: except that mathematically it does not
equal zero due to the decimal places, creating an internal logic flaw.

The workaround is to round to an appropriate number of decimal places,
but why does this happen?

Hey everyone. I'm making a spreadsheet of my daily expenses. I want every number to show 2 decimal places, so something that cost $2 will always show as 2.00 instead of just 2.

The only catch is I use the spreadsheet to also log how many gallons of gas I fill my car with and how much gas per gallon is, which have 3 decimal places to them (such as 3.659).

I want to set it up where at least 2 decimal places are mandated but if there's more than 2 it won't be restricted to 2.

Basically I want 2 to automatically show as 2.00, but if I put in 3.659 I don't want it shortened to 3.66.

How do I do this?

Hello. I'm using VBA Excel to enter data onto a spreadsheet. Due to formatting constraints, I'm entering two sets of numbers into one cell and dividing them with a horizonal dash, so one cell looks like this:

"5.69 | 0.24"

In order for my data to align along the column all the numbers need to be two decimal places. I'm using code to round my numbers to two decimal places so 5.69244 shows up as 5.69. I'm using code to round since the actual cell must be in text format since it's split. It works fine when I round unless the original number is already truncated to less than two decimal places, like 4.9. When I round that to two decimal places, it's still 4.9. Is there a way to use code to convert the 4.9 to 4.90? It seems like it should be easy but I can't figure anything out.


I need to convert a column of numbers currently formatted with 2 decimal places e.g. 112.12 to 4 decimal places (without the decimal point)

I need the end result to be 1121200

I've tried a few different suggestions given on the forum previously but can't seem to retain the 4 decimal places that I require.

Appreciate any help. Thanks.

I formatted the cells as Number and/or Currency, repaired, and reinstalled.
If I enter an amount like five dollars, I have to enter the entire "5.00" or
it will show as ".05". If I enter just " 5" it shows as .05 (five cents).
In Excel 2000, I only had to enter "5" and the decimal and two zeros appeared
after the 5.

Users will enter a cost (monetory value) into a textbox on a user form.
I need to ensure that the user enters numbers to the right of the decimal point and not just whole numbers.
What would the code be to check for two figures after the decimal point and how would I have a pop up box informing the user of what's required if they try to exit the TB without entering a value with two decimal places?


I have a spreadsheet that I have used for years. Just recently, I am having an issue with entering numbers. The decimal place is moved two places to the left. For example, I type in "12345" and hit enter and the cell will read "123.45".

I cannot figure this one out. Any help is appreciated.

I think this may fall under Data Validation but I'm not sure.

In column A, I want the user to only be able to enter the word "ACTUALS". It has to be all upper case.

In column B, I want the user to enter a 6 digit account number. The account number must be 6 characters.

In column C, I want the user to enter an amount that has only 2 decimal places. This column cannot have more than 2 decimal places.

In column D, I want the user to enter a description (text). This column cannot include any symbols such as @!#$.-&'()?/,\

If any of the above is in violation I would like an error message to pop up.

I have a VBA UserForm that has 2 ActiveButtons.
One will Add Fixed Decimal Places & the other will Remove Fixed Decimal Places to the Workbook.

Private Sub DecimalPlacesButton_Click()
    If Application.FixedDecimal = False Then
       MsgBox "Add Fixed Decimal Places"
       Application.FixedDecimal = True
    End If
End Sub
Private Sub DecimalResetButton_Click()
    If Application.FixedDecimal = True Then
       MsgBox "Remove Fixed Decimal Places"
       Application.FixedDecimal = False
    End If
End Sub

This works fine if using the cells on the Worksheet.
However, I have 320 TextBoxes in the VBA UserForm.
All 320 TextBoxes are linked to Cells in Worksheet via ControlSource.
When I enter a Numbered Value into the TextBoxes they don't Add the Decimal Places to the WorkSheet.
How can I Add Decimal Places to the Values of the 320 TextBoxes?
Any Ideas?

Okay, so I enter into cell A1 the formula =NOW(), and set the format to:

m/d/yyyy h:mm:ss.000

.... thus giving me fractions of a second to three decimal places. And those
decimal places display just fine - for example the cell currently reads:

8/21/2006 16:18:15.260

However, when I set the same cell using VBA:

Range("A" & 1) = Now()

I get this:

8/21/2006 16:18:15.000

For some reason it rounds to the nearest second. Any idea why, and how I
can fix it? Thanks!