Blog Post

Microsoft 365 Insider Blog
3 MIN READ

Control data conversions in Excel for Windows and Mac

Linda_C's avatar
Linda_C
Icon for Microsoft rankMicrosoft
Aug 25, 2024

(Originally published on October 19, 2023 by Chirag Fifadra)

 

Hi, Microsoft 365 Insiders! My name is Chirag Fifadra, and I’m a Product Manager on the Excel team. I'm thrilled to announce that, based on your feedback, we've improved the Automatic Data Conversion settings we made available last year, and are now making them broadly available in both Excel for Windows and Excel for Mac.

 

Automatic Data Conversion in Excel for Windows and Mac

 

What did we change?

Last year's blog post explains how the initial version of the feature worked. Based on your feedback, we have:

  • Made the feature easier to find
  • Added more format support
  • Made the feature available in Excel for Mac

 

How it works

We wanted to address customers' frustration with Excel automatically converting data to specific formats. So, we have now given you the ability to change Excel's default behavior and disable specific types of automatic data conversions as needed.

 

To do so:

  • In Excel for Windows, select File > Options > Data > Automatic Data Conversion, and then choose the conversion(s) that you’d like to disable.

     

    Excel Options dialog box on Windows with Automatic Data Conversion section selected

     

  • In Excel for Mac, select Excel > Preferences > Edit > Automatic Data Conversion, and then choose the conversion(s) that you’d like to disable.

 

Tips and tricks

  • You can enable or disable the following options:
    • Remove leading zeros from numerical text and convert to a number.
    • Truncate numerical data to 15 digits of precision and convert to a number that may be displayed in scientific notation, if needed.
    • Convert numerical data surrounding the letter “E” to a number displayed in scientific notation.
    • Convert a continuous string of letters and numbers to a date.

       

  • If you select the When loading a .csv file or similar file, notify me of any automatic number conversions check box, Excel will display a warning message when it detects that an optional automatic data conversion is enabled and about to occur when opening a .csv or .txt file. This message gives you the opportunity to open the file once without converting the data.

 

Scenarios to try

Based on the settings you chose above, try some or all of the scenarios below to test the increased control over data conversions.

  • Type directly into a cell.
  • Copy and paste from external sources (e.g., a web page).
  • Open a .csv or .txt file.
  • Find and replace operations.
  • Select Data > Text to Columns, and then use the Convert Text to Columns Wizard.

 

NOTE: Since the feature works by saving the entered data as text, you might see a green triangle with a Number stored as text error. This is expected. You can dismiss the error by selecting Ignore error in the context menu. Also, you may not be able to use that data in mathematical operations.

 

Known issues

The feature does not support disabling these conversions during macro execution.

 

Availability

This feature is available to all users running:

  • Windows: Version 2309 (Build 16808.10000) or later
  • Mac: Version 16.77 (Build 23091003) or later

 

Feedback

We want to hear from you! Please go to Help > Feedback in Excel to provide your thoughts about this feature.

 

Learn about the Microsoft 365 Insider program and sign up for the Microsoft 365 Insider newsletter to get the latest information about Insider features in your inbox once a month!

Updated Aug 25, 2024
Version 1.0
excel
mac

8 Comments

Sort By
  • JakeTExcel's avatar
    JakeTExcel
    Copper Contributor
    Jun 18, 2025

    The feature doesn't work at all.  Excel is still converting and corrupting CSV data even with these check boxes all disabled. 

  • diondotlogue's avatar
    diondotlogue
    Copper Contributor
    Apr 12, 2025

    This is absurd. A feature that has been requested for years and they finally "address" the issue. THIS DOES NOT WORK.

    These settings are currently completely pointless. Absolute idiots behind these stupid decisions and complete lack of support around fixing it clearly.

  • MakeItEasier-NotHarder's avatar
    MakeItEasier-NotHarder
    Copper Contributor
    Mar 06, 2025

    These new options are great, but one of the great frustrations with Excel is all the automatic changes it does to data and how it's displayed and there are many more ways left that we have no control over. PLEASE let us decide if we want our data mangled or not. The answer to the question "When should Excel display a value in scientific notation" is "Never". Why would I ever want that? Why would I want my data destroyed? I'm begging you to add an option to disable this data corrupting feature.

  • Alexia's avatar
    Alexia
    Copper Contributor
    Feb 18, 2025

    6 months later this feature still does not work as promised.

    Firstly the 'Notify me of automatic conversation' doesn't ever fired off.

    When opening a CSV file with phone numbers formatted as whole numbers, they all turn into E+11 numbers, despite holding the whole number (as seen in formula bar). Resizing the cells don't remove the scientific notation 

    Problem is, most users are aware of nor familiar with this feature, so they can copy and paste data in from their opening of CSV files with the auto conversion happening, and now there's no way of noticing that some mobile numbers are truncated and some not.

    By the time this was spotted, we'd already imported the data onto our CRM.

    I expect when deselecting the options above, that my cells simply won't display long numbers as E+11 numbers. We straight up just need an option to completely disable Scientific notations (which are rarely used by the average user) so that whole numbers show up as whole numbers (i.e. Custom format > "0") if they've not been 'converted'.

    • JakeTExcel's avatar
      JakeTExcel
      Copper Contributor
      Jun 18, 2025

      No one ever has used the scientific notation formatting in Excel.  Actual scientists do not use it, the way the actual data displayed just like everyone else.  

  • JNC's avatar
    JNC
    Copper Contributor
    Feb 10, 2025

    Please remove the feature, to convert numbers into Scientific Notation/Text.  Just keep it as numbers.

     

    We are users who use your software daily and this feature wastes our time.

     

    We get thousands of numbers daily, that we need to copy and paste it into excel(that automatically converts it into Text or Scientific Notation) where we then convert it into numbers again (also ensuring that it isn't converted to text).

     

    I do not understand the point of executing this feature and not have it work like it was promised.

     

  • JNC's avatar
    JNC
    Copper Contributor
    Feb 10, 2025

    Please update the feature, thanks.

  • ML_'s avatar
    ML_
    Copper Contributor
    Jan 06, 2025

    This setting sounded SO promising. The automatic reformatting of text data is often so opposite of helpful. Alas, although I found this setting and unchecked all the boxes, restarted Excel, rebooted my PC, and regenerated the .csv file... the problem persists!  Ugh.