How to avoid automatic data formatting in Excel

Alexander Frolov by , 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.

Pre-format cells as text

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:

  • Select the range where you'll have your special data. It can be a column or a number of columns. You can even select the entire worksheet (press Ctrl+A to do it straight away)
  • Right-click on the range and select "Format Cells…", or press Ctrl+1 Select to format cells
  • Select Text in the Category list on the "Number" tab Format range as text
  • Click Ok

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:

  • Format worksheet as text following the steps above;
  • Save as… - Excel template file type. Now every time you need text-formatted worksheet, you have it ready in your personal templates.

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.

Use data import wizard in Excel to open existing csv files

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:

  • Go to Data tab and find the first group on the ribbon - Get External Data. Use data import wizard
  • Click on From Text and browse for the file with your data.
  • Use "Tab" as the delimiter. We need the last step of the wizard, where you can select "Text" in the "Column data format" section. Pre-format cells as text

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