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.
- First, in cell C5, type 1-2 as shown in the following image.
- Next, press Enter and you will see Excel will automatically format our input into a date.
- Then, to solve this, use an apostrophe (‘) before typing 1-2.
- After that, press Enter and you will see that the format of the output is intact.
- 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.
- First, select the cell range C5:C8.
- 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.
- After that, you will see the Format Cells dialog box.
- Then, from the Category tab choose Text.
- Finally, press OK.
- 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.
- 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.
- Then, you will see the VBA window after performing the previous step.
- Now, from the Insert tab of the window, choose Module.
- 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
- First, we are naming the Sub Procedure as Stop_Excel_from_Auto_Formatting_Numbers.
- Next, we select the cell range, where we want to see the output.
- Then, we are changing the format of the cell range into Text.
.NumberFormat = "@" .Value = .Formula
- Finally, save the code into the module.
- Then, keeping the cursor in the module, press F5 or the Run button to run the code.
- Finally, enter your desired number into the cell range C5:C8 and you will find all their input formats intact after entering.
- 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.
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
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.
- 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