Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

Maintain leading zeroes when entering values in cells

There should be an application setting OR paste recovery option OR cell format to retain leading zeroes when entering values in cells (either manually or through another means like CSV import).

(2015-11-05 Dan [MS] Updated idea title and description to broaden this issue from maintaining leading zeros on CSV data import to maintaining them on any cell entry.)

1,209 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Anonymous shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    67 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • Anonymous commented  ·   ·  Flag as inappropriate

        for phone numbers, add a space and it recognises it as text and keeps leading zeros, (i know this won't cure the problem, but handy for individual cell data entry...

      • corey commented  ·   ·  Flag as inappropriate

        wayyy back in the day (excel 5? 97?) the data importer worked much more like in Access. Iirc, it was possible to define import specs and use those. I'm sure someone decided to make this all 'simpler', but its been a pain point for some time. At least one can sort of do the same with the text to columns functionality (if your text imported/pasted right...).

        at least Powerquery/PowerBI makes more useful observations and formatting/styles when it imports data, if there is schema info available from the source.
        So maybe PowerQuery/PowerBI is a mire right way to hold Excel, as it were...

      • Russell Zager commented  ·   ·  Flag as inappropriate

        Serious question: what is the point of the survey? You know what the issues are and the survey isn't scientifically sound.

      • Brin commented  ·   ·  Flag as inappropriate

        RE: The Survey sent out 1/12/18

        The 3rd question format is incorrect for the ask. You say to check all that apply but we can only select a single radiobox, instead of the multiple checkboxes.

      • H Dum commented  ·   ·  Flag as inappropriate

        @Scott McGee I have Office 365 on my Desktop Excel 2016 version 1711, on a computer that is just barely 2 months old, Nov 2017, and it does not do leave leading zero's. It is Office 365 personal, maybe only home version. Why Microsoft would include it in one version and not another, whether Office 365 or Office home and student which is not even $4 less than 365 personal bare basic programs and no apps, nor in office online, is ludicrous.

        When I choose I want Excel to handle numbers as text as the description/comment is "Text format cells are treated as text even when a number is in a cell. The cell is displayed exactly as entered."
        Deleting a leading zero is not exactly as entered.
        When I'm trying to track 10 digit identification/account/record numbers or 8 digit identification/account/record, and I have clients that have had their accounts 40+ years and they now have leading zeros because when they were issued in both situations some were only 6 digits, tracking those accounts is a nightmare as some are identical other than double zero's at the end, or to figure out if there's a data entry error when new accounts have been added.
        Microsoft unfortunately is the industry standard now, I could make a tracking 'spreadsheet' with far more ease using DOS in the 90's. No automatically changes, it's my data, but I don't have control of it using a spreadsheet, particularly Excel.

      • Scott McGee commented  ·   ·  Flag as inappropriate

        @William Roberts - this already is done, but you have to have Office 365 to get it. If you have a one-time-fee license, too bad, so sad.

      • William Roberts commented  ·   ·  Flag as inappropriate

        It would be nice to save Pivot Table preferences. I almost always use the Classic View and turn off Row Totals. Currently I have to open Settings and make this change at least 10 times a day when I create a new Pivot Table.

      • Tony Apfelbeck commented  ·   ·  Flag as inappropriate

        Also related to this - stop mangling things like account numbers and phone numbers that are 9+ digits in sequence and displaying them in scientific notation, rounded! This ruins all of that data the moment it's opened in Excel unless you go to extremes to work around it.

      • Louie Hannen commented  ·   ·  Flag as inappropriate

        @Scott McGee - The Excel team did an as me anything on Reddit the other day. This was mentioned as a pain point, and the team linked here and said to vote for this. It's been a thorn in my side for more than a decade now, as it has many others. Users shouldn't have to accept that their data is going to be automatically formatted by dropping portions of it without their say-so. That's especially true when opening a file which has data in it (such as csv). Maybe such data could be flagged with suggested changes, but changing it automatically is a bad idea.

      • Scott McGee commented  ·   ·  Flag as inappropriate

        Not sure why this all of a sudden is getting so many hits, but I *DO* hope there is some listening now, two years later!

        The bigger issue is ASSUMING you know what I need/want done with any kind of data I'm bringing in from elsewhere. Best practice IMO is to assume NOTHING and offer the user a choice on how to handle the data, ideally with an option for the user to SET the default.

        I frequently copy/paste data from SQL to Excel. This is convenient, quick, and relatively easy. But it could be better!

      • Hayden commented  ·   ·  Flag as inappropriate

        I manage an ecommerce store and dropping of leading zeros and the rounding of numbers in CSVs makes working with SKUs and UPCs a nightmare

      • Gustavo Herrera commented  ·   ·  Flag as inappropriate

        I would definitely love to see this in a future release. It would be very helpful when dealing with Zip/Postcodes

      • Louie Hannen commented  ·   ·  Flag as inappropriate

        Excel should not perform automatic operations which change the contents of an existing .csv file. It should never have happened in the first place. Unless the user opts in to particular algorithms, user entered AND pre-existing data should be treated as sacrosanct, with the application taking steps to ensure it is retained as-is.

      • Anonymous commented  ·   ·  Flag as inappropriate

        Please also make it an option to disable the automatic application of formulas when you open a csv in Excel. We receive a lot of csv files that contain IDs that start with a leading dash and every single time you open one Excel tries to convert them into a formula, which of course errors.

        Yes, you can import a csv file and force those columns to text but that's a real time suck and annoyance when you're opening anywhere from 50-100 of them a day.

      ← Previous 1 3 4

      Feedback and Knowledge Base