How to stop Excel from changing decimals to scientific notation?
Our take
I just got a new work laptop which uses Microsoft Office 365. I regularly pull in stock prices and currency foreign exchange conversion from Yahoo! Finance into CSV, which I then copy and paste values into my Excel spreadsheet.
One of the items I pull from Yahoo! Finance is IDRSGD=X, which is basically converting Indonesian Rupiah (IDR) to Singapore Dollars (SGD). Now, say IDR 1 = SGD 0.000077 and it is shown as such in Yahoo! Finance. However, in the CSV, the number automatically becomes 7.7E-5. When I copied and pasted from the CSV to my Excel file, it stayed the same way, ie. 7.7E-5. I know they mean the same thing but it's ugly and it just stands out like a sore thumb against all my other data in my spreadsheet, which are nicely presented in decimals. How can I stop Excel from automatically converting this notation? I went to Options --> Data and unchecked the relevant boxes, but it didn't help.
In Excel, even if I Format Cells --> Number to reflect decimals, it does not help either. Somehow the ugly scientific notation overrides the cell formatting. There are many cells in the data set showing scientific notation instead of decimals, and I cannot be going cell by cell.
I never faced this problem with my previous Microsoft Office 2019.
Can someone please help?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Font formatting of a calculation dashboard KEEPS reverting every time the data in the other sheets is updatedI am at my wits end, every time I open this spreadsheet the formatting of these cells (supposed to be large and extremely readable to low-tech users on same team) reverts back to Calibri size 11 for some reason. I even created a custom cell style to try and force it to stay that way and I can see that it's selected in the menu, but not reflected whatsoever. Is it just impossible to format cells with a constantly changing calculation? What can I do here?? I feel like I have gone through every formatting constraint/rule there is to try and force it to behave but it always changes back, even if its not right away. It's a spreadsheet stored on sharepoint primarily but I don't know if that makes a difference. Advice appreciated. 😞 submitted by /u/vailette [link] [comments]
- Need macro advice! Saving longer numbers as strings to show all the charactersDesktop, Excel 365 Build 19929.20106 Click-to-Run, Beginner We have a macro that generates new files using a list of data. Sometimes that data is completely numerical with over 15 digits. Excel keeps reformatting these numerical entries using scientific notation, which screws up the output files. Right now I have to manually check the output files and reformat the affected ones so that all then hidden characters are shown. Can someone please advise on how I can update the macro to automatically convert these numbers into strings that show all the characters? The data is of variable length, but I'm pretty sure it's always under 20 characters. This problem only affects one cell of the output file. Everything else is generated without issue. It's basically the problem of "how do I make Excel macro copy-paste the entire FedEx tracking number correctly" but the data isn't consistently 16 characters and is frequently alphanumeric. EDIT: I'm open to even stupid and booleg solutions like "add a hidden text character before transferring the data" to force this to work. The output files are basically just printables. I just don't know anything about macros and do not have time to learn. submitted by /u/Umklopp [link] [comments]
- How to make Excel just keep 2 decimal place behind comma after executing function?Hello, I recently downloaded Solver and Data Analysis tool pack and it changed the decimal place especially in percentage. Usually with excel, after for example executing =a%/b%, I will receive number with maximum 4-6 decimal place behind the comma. However recently, it starts giving me numbers up to 15 decimal place behind the comma. How to solve this for entire book without using trunc or something else? I just want to keep it to 2 decimal place behind the comma for the whole file but it always automatically gives more than 2 decimal behind place. I have checked option > advance, googled it but no avail. My excel is 2021 version, english, and I'm just beginner who is stressed about this. Thank you in advance. submitted by /u/chipotatochip [link] [comments]
- Excel keeps converting "10-3242" to Oct-3242, no matter what I've tried so farFor some reason, excel keeps converting this value into a date format, no matter what I've tried, which is: '10-3242 ="10-3242" Format cell > General If I do the first two, it'll appear/display correctly, but if I close it and reopen after saving the file, it'll revert back to the date format. Any ideas on what I can do? submitted by /u/GreenGloober [link] [comments]