Contents
Excel is a useful program when there are standard tasks and standard data. As soon as you want to take the non-standard path to Excel, it will entail some frustration. Especially when there are large amounts of data. I stumbled upon one of these formatting questions while reviewing our clients’ tasks in Excel.
Surprisingly, this turned out to be a ubiquitous problem, for example, when we enter a dash or a slash, Excel applies the date (or time) format to the cell. So, if you want to find the answer to the question: “Is it possible to undo automatic formatting in Excel?“, the answer will be -“Нет!“. But if this problem is between you and your data, there are several ways to deal with it.
Solutions that work as you enter data: cell preformatting
It’s actually quite a simple solution if you’re entering data into a spreadsheet. The fastest way is as follows:
- Select the range that will contain your data. It can be a column or multiple columns. You can even select the entire worksheet (click Ctrl + Ato highlight it).
- Right click on the range and select Format Cells (Format Cells) or click Ctrl + 1.
- Specify number format − Text (Text) in the list on the left side of the tab Number (Number).
- Press OK.
That’s all! Now all the values that you will enter in this column (or on this sheet) will retain their original form: be it “1/4” or “March 5“. All of them are recognized as text, aligned to the left and displayed in the desired form.
TIP: You can automate this task, both at the sheet and cell levels. On the forums, some pros suggest that you can create a sheet template and then use it if necessary:
- We format the sheet as text (discussed above).
- Save as (Save As) > Template Excel. Now, every time you need to create a sheet in text format, you just need to select it in the list of your templates.
If you need to set the text format for a specific range of cells, select the icon Styles (Styles) tab Home (Home) and create your own style that you can use later. To do this, select the desired range of cells and apply your formatting style to it.
Another way to display data in text format is to use the apostrophe (‘). In order for the entered data to be recognized as text, you must first put an apostrophe (‘).
Use the Excel Data Import Wizard to open existing CSV files
Solution #1 doesn’t work for me very often, because I use data in the form of CSV files from the Internet and other places. You won’t be able to recognize your entries if you just try to open the CSV file in Excel. So this question becomes a little painful when we start working with external data.
However, there is a way to quickly deal with this problem. In Excel, you can use Import Wizard. In Excel 2013, use the following steps:
- Click the tab Data (Data) and find the command group Get External Data (Getting external data). She is the first on the Tape.
- Click on From Text (From text) and select the file with your data.
- Use the key Tab as a separator. In the last step of the wizard, we must choose Text (text) in section Column data format (Column data format).
Summarize: there is no simple answer that would allow us to forget about automatic formatting in Excel. Use these two solutions to save at least some of your time, because not many clicks separate you from your cherished goal.