How to avoid automatic data formatting in Excel

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.

Solutions that work when you enter data - pre-formatting cells

It is really quite a simple solution if you are entering data into your table. The quickest way is the following:

  • Simply 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. I use Excel 2013 for the steps below:

  • 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

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.

3 Responses to "How to avoid automatic data formatting in Excel"

  1. Markb says:

    That's what i was looking for, a simple answer that thousands have asked.

Post a comment

Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
60+ professional tools for Excel
60+ professional tools for Excel
2019–2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Sheila Blanchard