Why Is Excel Changing My Numbers: 4 Methods

Method 1 – Enabling Auto-Insert Decimal Point Option is Changing the Numbers in Excel

Make a marks list of a subject for the student.
In cells under the Marks column, we’ll input the marks of the corresponding students.
Write down the marks of John Walter in cell C5. It gets 62 in this subject.

Press ENTER to place the number in that cell.
Press ENTER, the number in cell C5 gets transformed into 0.62.
We expected to get the number 62 in the cell.

Insert other students’ scores in the sheet too.

Solution 1:

Steps:

Go to the File tab.
Choose Options from the menu.

Opens the Excel Options dialog box.
Move to the Advanced tab.
In the Editing options, uncheck the box of Automatically insert a decimal point.
Click OK.

Returns us to the worksheet.
Select cell C5 and edit it. Write down 62 again.
Click Enter.

Enter all the entries in the sheet. They are in the correct format.

Solution 2:

Steps:

VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Select Visual Basic from the Code group.

Cclick on the sheet where you want to solve the problem.

Select the sheet if it isn’t already selected. Write down the following code in it.

Private Sub Solution1()
Dim hsh As Worksheet
For Each hsh In ActiveWorkbook.Sheets
    hsh.Cells.NumberFormat = "@"
Next
End Sub

Save the code.
Enter all the entries in the sheet. They are in the correct format.

VBA Code Explanation:

Private Sub Solution1()

Provide a name for the private sub-procedure of the macro.

Dim hsh As Worksheet
Then, declare the necessary variables.
  For Each hsh In ActiveWorkbook.Sheets
    hsh.Cells.NumberFormat = "@"
Next

This piece of code will change the format of the range of the cells.

End Sub

Finally, end the sub-procedure of the macro.

Method 2 – Increase Decimal Command is Changing Numbers in Excel

Use the Currency format in Excel when working with cash, bills, ledgers, or any other medium of exchange. There is also a problem associated with decimals. The dataset looks like this.

Solution:

Steps:

Select the range of the cells C5:C14.
Go to the Home tab.
Click on the Decrease Decimal icon on the Number group twice.

Select the range of the cells.
Go to the Home tab.
Click on the little arrowhead at the right of the Number group.

The Format Cells dialog box appears.
The Currency format under the Category section is already selected because we had applied this to the range at first.
Change the Decimal places to 0.
Click OK.

Utilize any of the two ways we showed above. The numbers will look like the one below.

Method 3 -You Have Typed a Really Long Number which Is causing Excel to Change Your Numbers

The Excel shows the full number, like in cells D6 and D7. See the process in detail.

Solution:

Steps:

Select the range of the cells D5:D14.
Go to the Home tab.
Click on the Number Format drop-down on the Number group.
Select the Number format from the options.

Place your cursor on the thin line between columns D and E.
Make double-click on your mouse.
All the numbers are showing in full format instead of rounding themselves.

Method 4 – Excel Can’t Differentiate Between Numbers and Dates, So It’s Changing the Numbers

Spreadsheets can sometimes present a challenge when it comes to importing data. In Excel, numbers are usually detected as dates, but sometimes the software is trained incorrectly to do so. By entering 3/4, you will get 03-April as below.

Press Enter, and you will get 03-April as shown below.

We want to solve this problem.

Solution 1:

Steps:

After adding the apostrophe (‘) sign before the number, type the number.
Press Enter.

The number will appear in its entirety.

Solution 2:

Steps:

Select the range of the cells C5:C9 and press Ctrl+1.

The Format Cells dialog box appears.
Select Text under the Category section.
Click OK.

Type the number.
The number will appear in its entirety.

How to Stop Excel from Changing Numbers to Formulas

When you enter numbers containing 11 or more digits, MS Excel converts them to exponential (in scientific format). Converting back is possible in a number of ways. The problem will look like this.

Solution:

Steps:

Select cell D5 and type the following formula.

=CONCATENATE(C5)

Press Enter.
The output will look like this.

All the numbers are showing in full format instead of rounding themselves.

How to Stop Excel from Changing Numbers to Pound Signs

We use the Currency format in Excel when working with cash, bills, ledgers, or any other medium of exchange. There is also a problem associated with decimals. The dataset looks like this.

Solution:

Steps:

Select the range of the cells C5:C14.
Press Ctrl+1.

The Format Cells window will appear.
Select the Number from the Category option.
Click OK.

The numbers will look like the one below.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets and methods in different spreadsheets for a clear understanding.

Excel Is Changing My Numbers.xlsm