How do I COMPLETELY turn off Excel autoformatting?

September 28, 2006 7:20 AM

How do I completely turn off all autoformatting in Excel?

Excel's autoformatting is driving me insane. At work I have to enter lots of ranges into Excel, such as "10-50". Excel helpfully thinks I actually mean "1st of October, 1950", and changes the cell to "Oct-50". Of course, changing the cell format away from Date merely changes the cell value to "18537". So not only does Excel automatically change the format away from what you entered, it irreversibly changes the actual data as well.

I know that changing the cell format to Text beforehand will stop any autoformatting, but remembering to do this every time I enter data is incredibly frustrating (and I have other autoformatting issues aside from the date one). Is there a way to completely deactivate autoformatting?

The most annoying thing is that if I make a mistake and enter "10-49" into lots of cells instead of "10-50", and then try and correct all the cells with Find & Replace, it changes the cells back to Dates even if I've previously set them all as Text!

posted by EndsOfInvention to computers & internet (6 comments total)

If you start the field with a single quote, autoformatting won't be applied (and the quote won't be displayed). That may be more keypresses than you want to deal with, though.
posted by bcwinters at 7:40 AM on September 28, 2006

I think you could go into your the style sheet and modify it so that is sees numbers as text or whatever format you want. [Format -> Styles and then change the number from "general" to "text" or whatever.]

Here's the help page that shows you how to make sure Excel changes the default template.
posted by birdherder at 7:53 AM on September 28, 2006

bcwinters: Yeah, I found that when I was Googling for an answer, but I would still have to remove the 's at some point as I have to import the Excel file into a database.

birdherder: Thanks, I'll check that out and see if I can get it to work.
posted by EndsOfInvention at 8:23 AM on September 28, 2006

Or you could just normalize your data properly, and have separate cells for the 10 and the 50. This would help with the database import too, assuming the database is built properly.
posted by flabdablet at 8:41 AM on September 28, 2006

Well, it's not really a proper database, it's a program used to store data for the pricing of materials for construction work, and the data has to be in a specific format when imported. Plus, it's practically impossible to alter the data once it's in the program (except for altering each entry one by one - which can take a while if there are 10,000 entries) which is why I have to faff around with getting everything formatted properly in Excel beforehand.

I think my best bet is to set the Style so that the default cell format is Text, but this means I'll have to change some of the text back to General to make formulae work. Still, at least it means Excel won't eat my data.
posted by EndsOfInvention at 8:56 AM on September 28, 2006

Would it be useful do something as simple clicking on a column heading to select a whole column, then doing Format->Cells->Text?
posted by flabdablet at 3:24 PM on September 28, 2006