> But this would come at the cost of not being able to mix types in column, and a lot of people want to be able to mix.

I feel like a lot of the desire for this comes from Excel's desire to fill the screen with a single infinite spreadsheet. Apple's Numbers and some other programs make it more natural to have multiple separate tables on screen, each of which can have their own header rows and columns and separate type and format rules for those columns/rows. Excel more or less forces people to emulate this capability by just using a separate region of the same table, which gets in the way of applying consistent formatting and typing rules to whole columns or rows.

Nah, much of this is the same as the dumbness of many autocorrecting keyboards:

automatically "correcting" something that was correct based on a dumb hunch

It is really simple to remove the most embarrassing autocomplete errors on phones:

- just disable autocorrect,

- or preferably use another keyboard that just proposes fixes but leaves it to you to select the suggestion (I use SwiftKey and it does this, others probably exist too).

The same is not possible in Excel as far as I am aware:

You cannot turn of autoformatting.

The whole thing could be easily (on a ux level at least ;-) solved by introducing a single checkbox: "stop embarrassing me" with help text that says "turn off autoformatting".

Checking that box would leave values like you typed or pasted them in.

No need to select on a column basis.

Edit: It is also almost at the level of modern search engines that replace my very specific queries with generic queries about something unrelated or vaguely related.

Edit 2: it is already more than a year ago I guess since iPhones were caught changing the names of correctly spelled medications to the name of another unrelated medication. Sooner or later this incessant drive to be smarter than the user is going to cost lives I'm afraid.

When it comes to search it is already costing me many minutes lost a day, and that is just first order effects, before we get into all the things that doesn't get done because we give up on finding it.

Right click on top left corner

Select format cells

Select "Text"

This will disable excel's autoformatting. Given the rarity that someone types "SEPT4" into excel and isn't typing a date, putting the option one level deep in a menu seems more appropriate than a top level button.

> Given the rarity that someone types "SEPT4" into excel and isn't typing a date, putting the option one level deep in a menu seems more appropriate than a top level button.

Given the rarity that someone has pasted 100 000 rows into and want them to be autoborked disabling autoborking, at least for pasted data seems appropriate enough for me ;-)

I would expect dates to show up in large datasets pasted into excel orders of magnitude more often than genes. For the people who use excel, autoformatting is overwhelmingly the desired default behavior.

> For the people who use excel, autoformatting is overwhelmingly the desired default behavior.

Seriously? Also for pasted series? Also when the rest of the column doesn't match?

Let's say you have 100,000 rows of data, and in a column all but 3 of those values look like dates. In a real office, which is more likely: that you are working on some set of data that just happens to look like a date 99.997% of the time but isn't a date, or some guy mis-entered or modified 0.003% of the data?

The I-want-to-put-data-in-my-table-which-closely-resembles-an-incredibly-common-format-but-is-problematic-if-mistaken-for-that-format-oh-and-I-can-not-be-bothered-to-google-how-to-turn-off-autoformat niche is not very large. I don't work for microsoft and I don't have copies of user data, but I would be willing to bet my entire life savings that over 95% of the time someone pastes a series which appears to have mismatched data types in the columns, the series actually does have mismatched data types in the columns.

Sounds a bit contrived in this context.

I'd rather think of 100 000 rows where some of them looks like like a date.

Note how nowhere in those instructions is the phrase "autocorrect."

Why would it? Excel doesn't have autocorrect, it has autoformat - a feature controlled in its formatting menu.

> A reasonable alternative would be for example statically typing each column or row in the sheet.

You can actually statically type the columns now for imported data via the PowerQuery editor (which is built into Excel), although not a lot of people know how to use it.

> A reasonable alternative would be for example statically typing each column or row in the sheet.

Excel actually supports this out of the box. But it's one more option to set, and people get lazy.

Also those properties are lost if you transfer the data to another format then back, or don't properly survive copy/cut/paste operations (often cutting will remove everything about a cell including options such as these).

As well as being easy to be lazy and not use the options, it is easy to accidentally undo them also.