The days of Microsoft Excel's "helpful" habit of automatically converting values might be at an end as the Windows giant has finally admitted that, yes – not everything is a date.

As well as spawning a [1]thousand [2]memes - or so it feels like - Excel's habit of helpfully converting values into types that the user didn't intend has resulted in some hilarious and time-consuming consequences. [3]Changing the gene naming rules to suit the whims of the spreadsheet springs effortlessly to mind.

Recognizing the pain caused by its productivity tool, Microsoft took some tentative steps [4]last year to allow users to control better what Excel would do when presented with something that looked like it might be a specific data type.

[5]

After a year of requests from users, Microsoft is making the feature available for Excel for Windows and Mac, and tweaked things a little further to make the option more accessible to find. It will also – wait for it – allow users to specifically turn off the spreadsheet's urge to turn letters and numbers into a date when it really shouldn't.

[6]

[7]

Chirag Fifadra, product manager on the Excel team, was charged with [8]dispensing the good news , which only applies from 2309 (build 16808.10000) of the Windows version and 16.77 (build 23091003) of the Mac version or later. There is no retrofitting for the time being.

Fifadra said: "We wanted to address customers' frustration with Excel automatically converting data to specific formats." To that end, an Automatic Data Conversion section has been added to the Data page of the Options dialog.

[9]

Previously, Microsoft listed the options on the Advanced page – which some might argue is the computing equivalent of sticking the settings in the bottom of a locked filing cabinet stuck in a disused lavatory with a sign on the door saying "Beware of The Leopard" ( with apologies to Douglas Adams ).

[10]What did the VisiCalc fairy bring you for Spreadsheet Day?

[11]Excel Hell II: If the sickness can't be fixed, it must be contained

[12]Excel recruitment time bomb makes top trainee doctors 'unappointable'

[13]Building Excel-like UI for Uber's China ops exposed Microsoft calculation quirks

Using the settings, users can halt some of Excel's most egregious habits, including stripping leading zeroes from numerical text and the infamous practice of turning something that looks a bit like a date into an actual date. The user will also be warned if possible conversions are spotted in imported files (such as .csv data) and allowed to open the file once without converting the data.

While Excel will still complain about potential numbers being stored as text - this can be ignored - and the conversions cannot be disabled during macro execution, it is undoubtedly a step in the right direction. Now, about [14]that whole leap year thing ... ®

Get our [15]Tech Resources



[1] https://imgur.com/gallery/uqf83Ay

[2] https://www.reddit.com/r/memes/comments/vvws55/annoying_af/

[3] https://www.theregister.com/2020/08/06/excel_gene_names/

[4] https://insider.microsoft365.com/en-us/blog/control-data-conversions-in-excel

[5] https://pubads.g.doubleclick.net/gampad/jump?co=1&iu=/6978/reg_software/front&sz=300x50%7C300x100%7C300x250%7C300x251%7C300x252%7C300x600%7C300x601&tile=2&c=2ZTaYpPoHVB3ddjWN@Gj52AAAAM0&t=ct%3Dns%26unitnum%3D2%26raptor%3Dcondor%26pos%3Dtop%26test%3D0

[6] https://pubads.g.doubleclick.net/gampad/jump?co=1&iu=/6978/reg_software/front&sz=300x50%7C300x100%7C300x250%7C300x251%7C300x252%7C300x600%7C300x601&tile=4&c=44ZTaYpPoHVB3ddjWN@Gj52AAAAM0&t=ct%3Dns%26unitnum%3D4%26raptor%3Dfalcon%26pos%3Dmid%26test%3D0

[7] https://pubads.g.doubleclick.net/gampad/jump?co=1&iu=/6978/reg_software/front&sz=300x50%7C300x100%7C300x250%7C300x251%7C300x252%7C300x600%7C300x601&tile=3&c=33ZTaYpPoHVB3ddjWN@Gj52AAAAM0&t=ct%3Dns%26unitnum%3D3%26raptor%3Deagle%26pos%3Dmid%26test%3D0

[8] https://insider.microsoft365.com/en-us/blog/control-data-conversions-in-excel

[9] https://pubads.g.doubleclick.net/gampad/jump?co=1&iu=/6978/reg_software/front&sz=300x50%7C300x100%7C300x250%7C300x251%7C300x252%7C300x600%7C300x601&tile=4&c=44ZTaYpPoHVB3ddjWN@Gj52AAAAM0&t=ct%3Dns%26unitnum%3D4%26raptor%3Dfalcon%26pos%3Dmid%26test%3D0

[10] https://www.theregister.com/2023/10/17/spreadsheet_day/

[11] https://www.theregister.com/2023/10/16/excel_hell_comment/

[12] https://www.theregister.com/2023/10/12/excel_anesthetist_recruitment_blunder/

[13] https://www.theregister.com/2023/09/19/matt_uber_china_excel_clone/

[14] https://learn.microsoft.com/en-gb/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year

[15] https://whitepapers.theregister.com/