How to Stop Excel from Auto Formatting Numbers (3 Easy Ways)

In Microsoft Excel, users have to input different types of numbers for working purposes. For example, to calculate salaries for workers, employers have to use the date and accounting format of Excel. By default, Excel changes the format of numbers after entering them into the cells. You can observe it from the following animation. So, users find it difficult to change their input repeatedly. In this article, I will show you how to stop Excel from auto-formatting numbers.

How to Stop Excel from Auto Formatting Numbers: 3 Handy Ways

In this article, you will see three easy and different ways to stop Excel from auto-formatting numbers. In my first method, I will use an apostrophe while entering the value into the cell. Secondly, I will alter the format of the cells to preserve the input. Lastly, I will apply a VBA code as the third method of this procedure.

To illustrate my article, I will use the following data set. Here, I’ve used different types of numbers to solve this issue.

1. Using Apostrophe While Entering Cell Value to Stop Auto Formatting Numbers

Using an apostrophe before entering the cell value is the easiest way to tackle this issue. You don’t need any formula or to change anything to use this method. The steps for this procedure are as follows.

Step 1:

• First, in cell C5, type 1-2 as shown in the following image.

Step 2:

• Next, press Enter and you will see Excel will automatically format our input into a date.

Step 3:

• Then, to solve this, use an apostrophe (‘) before typing 1-2.

Step 4:

• After that, press Enter and you will see that the format of the output is intact.

Step 5:

• Finally, use the same technique in the following cells to get the desired result.

2. Altering Cell Format to Stop Excel from Auto Formatting

In my second method, I will alter the cell format of a fixed cell range. Excel cell format changes automatically after entering data. By altering the format, you can achieve the desired result. To do so, see the below-given steps.

Step 1:

• First, select the cell range C5:C8.

Step 2:

• Next, go to the Home tab of the ribbon.
• Then, in the Number group, click on the little arrow, in the lower right corner of the group.

Step 3:

• After that, you will see the Format Cells dialog box.
• Then, from the Category tab choose Text.
• Finally, press OK.

Step 4:

• Finally, after altering the format, input your desired number and their format will be intact.

Read More: How to Prevent Cell Format Changes Automatically in Excel

3. Applying VBA Code to Stop Auto Formatting

For my last method, I will use a VBA code to stop auto-formatting. For that, I have to provide proper sequences and commands in the code. If you want to know more about this procedure, see the following steps.

Step 1:

• First, before entering any number in the Output column, enable the Developer tab in the ribbon and select the tab.
• Next, from the Code group, choose Visual Basic.

Step 2:

• Then, you will see the VBA window after performing the previous step.
• Now, from the Insert tab of the window, choose Module.

Step 3:

• After that, paste the following code into the module.

'Giving the sub procedure name
Sub Stop_Excel_from_Auto_Formatting_Numbers()
'Selecting desired cell range
With Range("C5:C8")
'Addressing cell format into text
.NumberFormat = "@"
.Value = .Formula
End With
End Sub 
			

Sub Stop_Excel_from_Auto_Formatting_Numbers()

With Range("C5:C8")

.NumberFormat = "@"
.Value = .Formula

Step 4:

• Finally, save the code into the module.
• Then, keeping the cursor in the module, press F5 or the Run button to run the code.

Step 5:

• Finally, enter your desired number into the cell range C5:C8 and you will find all their input formats intact after entering.

Read More: How to Stop Excel from Changing Last Number to 0

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.

Conclusion

That’s the end of this article. We hope you find this article helpful. After reading the above description, you will be able to stop Excel from auto-formatting numbers by using any of the above-mentioned methods. Please share any further queries or recommendations with us in the comments section below.

Related Articles

• Why Excel Is Changing My Numbers to Decimals
• How to Stop Excel from Rounding 16 Digit Numbers 
• How to Stop Autocorrect in Excel for Dates
• [Fixed!] Why Is Excel Changing My Numbers?
• How to Stop Excel from Auto Formatting Hyperlinks
• [Fixed!] Excel Changing Dates to Random Numbers
• How to Stop Excel from Changing Numbers to Scientific Notation
• Why Is Excel Changing My Numbers to Zero?
• How to Stop Excel from Changing Numbers to Dates

<< Go Back to Excel Auto Formatting | Excel Cell Format | Learn Excel