Zehad Rian Jim
This tutorial will demonstrate how to stop excel from changing numbers. Sometimes if you insert a number excel auto changes it to date or another scientific method. In many cases, it can create huge confusion. So, it is very important to learn how to stop excel from changing numbers.
Download Practice Workbook
You can download the practice workbook from here.
3 Easy Methods to Stop Excel from Changing Numbers
We’ll use a sample dataset overview as an example in Excel to understand easily. In this case, we have the Dates in column B and the Numbers in column C. If you follow the steps correctly, you should learn how to stop excel from changing numbers on your own. The steps are:
1. Using Format Cells Option
Our aim is to stop excel from changing numbers by using the Format Cells option. We can do so by following the below steps.
Steps:
- First, select the desired cell range.
- Next, press Ctrl+C and then go to the desired cell and again press the Ctrl+V buttons.
- After that, right-click on the desired column and select the Format Cells option.
- Then, in the Format Cells dialog box, go to Number > select the desired Types > OK options.
- Finally, you will the below result.
Read More: How to Stop Excel from Auto Formatting Numbers (3 Easy Ways)
2. Adding Apostrophe Before Number
In this case, our goal is to using the Apostrophe before numbers. We can do so by following the below steps.
Steps:
- At first, in the cell C5 insert the same value that you want to insert in cell B5 only this time you have to put it in front of it like this: ‘6/1
- After pressing the Enter button, you will get the result for this cell, and then press the Fill Handle option to use it for all cells.
- Last, you will the below result.
Read More: How to Stop Autocorrect in Excel for Dates (3 Quick Ways)
3. Applying VBA Code
We can also apply VBA code to stop excel from changing numbers by following the below steps.
Steps:
- Firstly, arrange a dataset like the below image.
- Secondly, press the Alt+F11 options to open the VBA window and insert the following VBA code in the Module option.
Private Sub Workbook_Open()
ActiveSheet.Cells.NumberFormat = "@"
End Sub
- Lastly, after pressing the RUN or F5 button, you will the below result.
Read More: [Fixed!] Why Is Excel Changing My Numbers? (4 Reasons)
How to Stop Excel from Rounding Numbers
Now, we want to stop excel from rounding numbers by following the below steps.
Steps:
- At first, enter large numbers, but you will see that excel converts them into round numbers automatically.
- Second, in cell B5 insert the apostrophe in front of the number and press the Enter button.
- Third, you will the below result for this cell.
- Forth, if you do the same steps for every cell then you will get the below result.
- Fifth, you will see an error option. Just select the Ignore Error option to get rid of this problem.
- Last, you will the below result.
Read More: How to Stop Excel from Rounding 16 Digit Numbers (2 Easy Ways)
How to Stop Excel from Changing Numbers to Dates in CSV file
Sometimes excel changes numbers to dates in CSV file. This creates a problem. So, we want to stop excel from changing numbers to dates in the CSV file by following the below steps.
Steps:
- At first, arrange a notepad file like the below image.
- Second, go to Data > From Text/CSV options.
- Third, select the desired txt file and press the Import option.
- Fourth, you will get the below result and press the Transform Data option.
- Fifth, select the desired cell and press Data Type.
- Sixth, select the Text option in the Data Type.
- Last, you will the below result.
Read More: [Fixed!] Excel Changing Dates to Random Numbers (3 Solutions)
Conclusion
Henceforth, follow the above-described methods. Hopefully, these methods will help you to stop excel from changing numbers. We will be glad to know if you can execute the task in any other way. Follow the ExcelDemy website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our level best to solve the problem or work with your suggestions.
