Stop Excel from changing large "numbers" (actually text values) to scientific notation.
Prevent Excel from changing large "numbers" (actually text values) to scientific notation.
Thanks very much for your votes. And to those who took the time to fill in the survey, thank you! This is a brief update to let y’all know that we’ve started work on this feature request.
- Urmi [Msft]
Mike W commented
Before we get any more "easy" solutions, I would like to point out that most of the people who cannot overcome this problem need to be able to double click on a .csv file and have it open in the correct format. As soon as your easy solution involves something more than double-clicking on a .csv file, my clients are not having their problem resolved.
Even though Microsoft should most definitely build in an explicit option to stop the application from changing large numbers to scientific notation, there is a super simple and easy work-around. All you have to do is change the cell format to "Number". Excel only changes large numbers to scientific notation when the cell format is "General". If you change it to "Number", Excel assumes you don't want scientific notation and it doesn't convert it. You can still have Excel change "Number" formatted cells to scientific notation but you have to mark that option when changing the format. If you have a large column of data with large numbers, change the entire column to "Number" format (this won't affect cell with alpha or alpha-numeric characters). Excel will default to putting two decimal points after the numbers but you can use the "Decrease Decimal" option (it's a little button in the "Number" section of the "Home" tab that has a decimal and the arrow points toward the decimal). Hope this helps!
I cannot understand how there is no way for the user to control Excel's behavior w/r/t long numbers. Isn't the point of scientific notation to enable long numbers to be written imprecisely by hand? We're using a computer here. I don't get why the default option is to use scientific notation.
A lot of Excel users whose work is being ruined by this Excel "feature" don't have the choice to use a different program. I cannot use anything cloud-based. Bureaucratic IT departments are also slow to approve different software. I do a lot of intermediary data work in a non-networked secure environment and must use the provided software...
Also, Excel is a good, powerful program. A lot of us are comfortable using it and need its features. Just not the insane handling of "long" numbers!
Why is there not an option to control how Excel treats large numbers? I have control over such trivial things as "zoom on roll with IntelliMouse" or "do not automatically hyperlink screenshot" or--this one seems particularly applicable because it concerns the interaction between displayed data and underlying data--"show a zero in cells that have a zero value." Scientific notation should be only a display choice, NOT something that alters the underlying data when you click save. Go ahead, try saving a file as CSV when you've got that effing scientific notation on the screen.
Please at the minimum give users an option for how Excel handles long numbers.
"too slow" must mean you are scanning directly into Excel?
if that is the case, open a new Excel spreadsheet, select all cells (top left button) and change their format to Text.
save as an Excel Template (.xltx) and name it "book".
then move the saved file to:
the next time you start Excel, it should start with that template, and not use SN.
I swear. If my UPCs get messed up one more time. . . . . . I lose hours of work when this **** happens!
Is there even ANYONE in the world that actually uses this dumb feature??
PLEASE GET RID OF IT.
(yes I have used google doc's, and it's too slow for the work I'm doing)
GET WITH IT MICROSOFT.
Joe - I use Google sheets for all my eBay work. MS is useless
Only 1434 votes as of now? This is such a frustrating problem for me. Excel changes ebay item numbers to scientific notification, making the spreadsheet useless. It's stunning that MS has ignored this problem for so long.
Google docs has the unfortunate property that evil Google then mines your documents for "marketing purposes". Have sensitive, confidential information, DO NOT STORE ON GOOGLE. they will be reading your balance sheets, customer lists, NDAs, etc...
But it looks like MS might fix this now so we can hope.
This has been a problem for two decades but you refuse to listen to your users. Google Docs has much better number management and so is what I use when I have the choice.
doesn't sound like Office is being very compliant, at this moment at least. you could always bring it up at a Board Meeting...
I would love to use Google docs bur Office is our compliant package at the company and we cannot use Google Docs.
to reiterate, i would love to see them fix Excel, but in the interim, Docs is a quaint alternative.
i think you underestimate end users. all you need to use Google Docs is what most people already have: a Google Account. it's free, and if your company uses G Suite already, it should already be set up from the get-go for all employees.
If they can handle Gmail, they can handle Docs. our users do, and they have trouble with all manner of "tech stuff".
Of course, as Lee mentioned, Docs cannot do EVERYTHING that Excel can, but it does many things that Excel currently cannot...
Me? No. This isn't for me. This is for your normie end users. Google docs confounds them. And it might not even be available from their corporate network.
If it was just me, I wouldn't care. But getting an entire population of normie end users on board with a work around using Google docs isn't feasible.
I have used google docs quite a bit and it currently does not hold a candle to excel. I do wish there were valid alternatives but sadly there are only distant competitors
have you tried using Google Docs?
This "feature" is beyond incredibly dumb. We use Excel to export serial numbers from our reporting application to a spreadsheet so users can manipulate the data as they see fit.
The serial numbers are a mix of pure alpha, pure numeric or alphanumeric mix.
So when they export and there is a long serial number that is purely numeric, it converts those serials to super dumb and unhelpful scientific notation.
There is practically zero desire for this. "Scientists" who might find scientific notation useful account for less than 1% of the total workforce.
pretty much what i said, basically ;)
Regarding the person's example below trying to manage building room information, I had the same issue. I think you will find if you right click - formatting, you should be able to over come this.
as a workaround, i always pre-format all cells to TEXT format, before i start using Excel.
sad, and should be unnecessary, but it works for me.