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

Get FREE Advanced Excel Exercises with Solutions!

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.

### 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")
.NumberFormat = "@"
.Value = .Formula
End With
End Sub```
```

VBA Breakdown

• First, we are naming the Sub Procedure as Stop_Excel_from_Auto_Formatting_Numbers.
``Sub Stop_Excel_from_Auto_Formatting_Numbers()``
• Next, we select the cell range, where we want to see the output.
``With Range("C5:C8")``
• Then, we are changing the format of the cell range into Text.
``````.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.

ðŸ’¡ Notes:

• If you want to stop Excel from changing numbers to date in CSV, then go to Power Query Editor from the Data tab of the ribbon from the Excel file. Then import the CSV file into the query and transform the data type from date to text to solve this issue.

## 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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Araf Bin Jayed

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF