by Alexander Frolov, updated on
Excel is a helpful program when you have standard tasks and standard data. Once you want to go your non-standard-Excel way, some frustration is involved. Especially when we have large data sets. I came across one of such formatting issues when I dealt with our customers' tasks in Excel.
Surprisingly, it appeared to be quite a ubiquitous problem when we enter numbers with dashes or slashes, and Excel decides those are dates (or time, or what not). So, if you want to find the answer to the question: "Can you cancel automatic formatting?", it's a "No". But there are several ways you can deal with the format if it stands between you and your data.
It is really quite a simple solution that works when you are entering data into your sheet. To prevent auto-formatting, just do the following:
That's it; all the values you enter in this column or worksheet will retain their original view: be it 1-4, or mar/5. They are regarded as text, they are left-aligned, and that's all there is to it.
Tip: You can automate this task on both worksheet- and cell-scale. Some pros on the forums suggest that you can create a worksheet template you can use any time:
If you need text-formatted cells - create your own cell style under Styles on the Home ribbon tab. Created once, you can quickly apply it to the selected range of cells and enter the data.
Another way is entering an apostrophe (') before the value you are putting in. It basically does the same thing - formats your data as text.
Solution #1 often didn't work for me because I already had data in csv files, the web, and elsewhere. You may not recognize your records if you try to simply open a .csv file in Excel. So this issue becomes a bit of a pain when you try to work with external data.
Yet there is a way to deal with this one as well. Excel has a wizard you can use. Here are the steps:
For more information, please see:
The bottom line: there isn't a simple answer that will let you forget about the format, but keeping in mind these two solutions will save you some time. Not so many clicks keep you away from your goal.
Table of contents