Why Do My Excel Files Change to Read Only
Excel has some really smart features that can exist actually useful in many cases (and sometimes information technology could be frustrating).
One such surface area is when you enter numbers in Excel. Sometimes, Excel automatically changes these numbers to dates.
For example, if you enter ane/ii in Excel, Excel will automatically change this to 01-02-2020 (or 02-01-2020 if you're in the United states of america)
Similarly, if yous enter 30-06-2020, Excel assumes you want to enter a date, and it changes this to a date (as shown below)
While this may be what you desire in most cases, but what if I don't want this. What if I simply want the exact text 30-06-2020 or the fraction 1/2.
How do I stop Excel from irresolute numbers to dates?
That'southward what this tutorial is most.
In this Excel tutorial, I will show you two actually simple ways to finish Excel from irresolute numbers to text.
But before I show you methods, allow me rapidly explain why Excel does this.
Why does Excel Changes Numbers to Engagement?
While information technology tin be frustrating when Excel does this, it'due south trying to help.
In almost of the cases, when people enter numbers that can also represent valid date formats in Excel, it volition automatically catechumen these numbers into dates.
And this but doesn't mean that it changes the format, it actually changes the underlying value.
For case, if you enter 3/6/2020 (or 6/three/2020 if yous're in the United states and using the United states engagement format), Excel changes the cell value to 44012, which is the numerical value of the date.
Too read: How to Convert Numbers to Text in Excel
Terminate Excel from Changing Numbers to Dates Automatically
The but style to stop Excel from irresolute these numbers (or text string) into dates is by immigration letting it know that these are not numbers.
Let's see how to do this.
Change the format to text
The easiest way to make sure Excel understands that it's not supposed to change a number to date is past specifying the format of the jail cell every bit Text.
Since dates are stored as numbers, when you make the format of a cell text, Excel will understand that the entered number is supposed to be in the text format and not to be converted into a engagement.
Below is how you can stop Excel from irresolute numbers to dates:
- Select the cell or range of cells where you want to make the format as Text
- Click the Home tab
- In the Number group, click on the dialog box launcher icon (or yous can utilize the keyboard shortcut Control + 1).
- In the Format Cells dialog box, in the category option, click in Text
- Click OK
The above steps would change the prison cell format to text. Now when you enter any number such every bit xxx-06-2020 or 1/two, Excel volition non convert these into date format.
You can also open the Format Cells dialog box by selecting the cell, right-clicking on information technology so clicking on the Format Cell choice.
Note: You demand to change the format earlier you enter the number. If you lot exercise this afterwards the number has been entered, this would modify the format to text but yous would get the numeric value of the date and not the exact number/text-cord you entered.
This method is best suited when you take to change the format of a range of cells. If you lot only have to do this for a couple of cells, it'south all-time to use the apostrophe method covered next
Likewise read: How to Convert Serial Numbers to Dates in Excel (2 Easy Ways)
Add an Apostrophe before the Number
If you merely have to enter a number in a few cells and you don't want Excel to change it to date, you tin use this elementary technique.
Only add an apostrophe sign before y'all enter the number (every bit shown below).
When you add an apostrophe at the very starting time, Excel considers that cell every bit text.
While you would non see the apostrophe sign, y'all can visually see that the numbers would be aligned to the left (indicating that information technology'due south text).
By default, all numbers align to the correct and all text values marshal to the left.
Some other benefit of using the apostrophe sign is that you tin notwithstanding use the lookup formulas (such every bit VLOOKUP or MATCH) using the value in the cell. The apostrophe will exist ignored by these functions.
You can also apply this method to change existing dates into text. For case, if you have thirty-06-2020 in a cell, you can simply add an apostrophe (') at the beginning and it will exist changed the date to text
Sometimes, y'all may come across a light-green triangle at the top-left part of the prison cell, which indicates that numbers have been stored equally text in those cells. But since that'due south exactly what we want in this case, you can ignore those green triangles, or click on it and then select the Ignore Error option to make it become abroad.
While these methods are great, what if you accept a column full of dates that you want to employ every bit a text and non as dates. Hither are some simple methods you can utilise to catechumen engagement to text in Excel.
I hope y'all constitute this Excel tutorial useful!
You may also like the following Excel tutorials:
- How to Remove Cell Formatting in Excel
- How to Remove Time from Engagement/Timestamp in Excel
- How to Wrap Text in Excel
- Convert Fourth dimension to Decimal Number in Excel (Hours, Minutes, Seconds)
- How to Stop Excel from Rounding Numbers
Source: https://trumpexcel.com/stop-excel-from-changing-numbers-to-dates/
0 Response to "Why Do My Excel Files Change to Read Only"
Post a Comment