0

I have to enter values manually into an Excel sheet for use in calculations. The trouble is, because the column I am entering into is formatted to only show to 2 decimal places, if I accidentally add a third decimal place (say I type 12.999 instead of 12.99) I won't see it, and the calculation that is then carried out is wrong, and I have to undo everything and go back to the start of the process. Unfortunately in this case I am unable to change the format of the column to show the values to 3 d.p. instead.

I know that you can limit the number of characters you are able to enter into a cell by using the Data Validation tool. I was wondering if there is a way to limit the number of decimal places I can enter to?

Many thanks in advance for any help that you can give.

Improve this question
2
  • 1
    What do you mean by "the calculation that is then carried out is wrong"? Regardless of how many decimal places are shown, Excel will always use the actual value in the cell when it's calculating. Adding extra digits will never make your calculation "wrong" - it just means your input column isn't displaying its value at full precision.
    – Steve can help
    Commented Jun 12, 2017 at 12:08
  • 12.999 will be rounded to 13.00 as opposed to supposedly 12.99, and obviously this will affect calculations referring to this cell.
    – Vylix
    Commented Jun 13, 2017 at 4:25

2 Answers 2

Reset to default
1

If you need to use Data Validation you can select the column B for example which you already have formatted to 2 decimals only, you can choose:

  1. Data Tab
  2. Data Validation
  3. Settings
  4. Custom
  5. Use Formula: =Mod(B2*100,1)=0
  6. OK

In that case maximum of decimal places is 2

Improve this answer
0

I think you've over-complicated this. Data validation to prevent additional user input just seems unnecessary. Simple alternatives:

Option 1 - Use Rounded values in Output

=ROUND(value,2)

Rather than worrying about restricting the ways users can input data (which unnecessarily annoys people anyway), just round the values feeding into your equation. If somebody's entered 12.945, this formula will change that into 12.95 before it calculates your result.

Option 2 - Show Full Precision of Input

The easiest option to see the entered precision without trailing zeroes is to avoid Number formats altogether, and just go with the General format instead, which typically defaults to full precision when using decimals:

Select Cell > Right Click > Format Cells > Number > General

Alternatively, just press Ctrl + Shift + #.

Improve this answer

You must log in to answer this question.