I was thinking that perhaps the autoformat options could be explicitly shown in the column headers

    number date currency
    A      B    C

and you could click on the format to change it. Italics could show assumed formats, bold could show user set formats.

I'm newly returned to (very minor) Excel use and assumed formats with no feedback seems wrong. My situation is dates _not_ being picked up as dates and it not being obvious that it hasn't; I'm using ISO format (eg 2020-06-17), I guess you have to create a new date format or use USA-ordering before it will recognise dates.

I would be happy if Excel supported some way to specify the column type in the CSV itself. Ie first row headers, second row data type/hint, subsequent rows the actual data.

At the moment users need to remember every time they import the CSV to specify A data type for each incorrectly handled column. Suffice to say they don’t always remember... and with large CSVs with hundreds of columns it’s just a pain.

What might be better is what I'll call “CSV Schema”: support for paired CSV files where one normal CSV has data, and the other is a schema file which contains headers which match the data files headers with a standard extra first header prepends (call it “meta-name”), and the data rows each provided metadata for the corresponding column in the data file. The most common (and often the only) metadata row might be for type information, but the format could support additional metadata.

This would allow existing CSV tools that work with data files to continue to work without modification, while CSV Schema aware tools could make use of the schema.

This is what happens on explicit import, but, formatting is per-cell, not per-column. "General" is the format for "idk, guess" - it stays General after a transform, it's not converted.