How to stop Excel from formatting numbers in scientific notation
Chances are, you've come across an Excel idiosyncrasy that looks a little something like this...
Perhaps you received a CSV from a colleague, or downloaded an export from your database - the data contained long numbers, and when you opened in Excel you were greeted by that funky looking format.
Excel can be the best, and it can provide plenty of eye roll moments that can end up creating extra work for you. In this case, when you open a CSV in Excel without actually importing the data in, Excel does it's best to automatically format your numbers into what it thinks is correct.
So, why on Earth would Excel think you are working in scientific notation? Well it actually has to do with Excel's 15-digit precision limit. The program is designed to cap numbers at 15 digits, in order to preserve accuracy. So, when you open up your CSV that contains 15+ digit numbers, Excel automatically converts that to scientific notation as a way of dealing with that limit. Seems reasonable, right?!
Not to fret, there is an easy way around this. If you recall from our guide on converting CSV to Excel, we detail why it's important to actually import your file into Excel, rather than just simply opening the CSV in the program. The reason being, importing your CSV to Excel allows you to control how your file is converted, including formatting your columns, so you don't end up with crazy Excel auto-formatting issues.
If you refer to our guide, you can see in Step 8 where you can choose the format of your columns.
Excel can be the best, and it can provide plenty of eye roll moments that can end up creating extra work for you. In this case, when you open a CSV in Excel without actually importing the data in, Excel does it's best to automatically format your numbers into what it thinks is correct.
So, why on Earth would Excel think you are working in scientific notation? Well it actually has to do with Excel's 15-digit precision limit. The program is designed to cap numbers at 15 digits, in order to preserve accuracy. So, when you open up your CSV that contains 15+ digit numbers, Excel automatically converts that to scientific notation as a way of dealing with that limit. Seems reasonable, right?!
Not to fret, there is an easy way around this. If you recall from our guide on converting CSV to Excel, we detail why it's important to actually import your file into Excel, rather than just simply opening the CSV in the program. The reason being, importing your CSV to Excel allows you to control how your file is converted, including formatting your columns, so you don't end up with crazy Excel auto-formatting issues.
If you refer to our guide, you can see in Step 8 where you can choose the format of your columns.
So, if your CSV file contains a column with 15+ digit numbers, you can opt to format this column as text. The result will allow you to view your data in Excel with the full number present, with no funky formatting.
The moral of the story is to remember this: when opening CSV files in Excel, it's worth the extra few minutes to actually import your data in, rather than just open it. If you don't, Excel will take liberties with formatting your data, and you may not like the result.
The moral of the story is to remember this: when opening CSV files in Excel, it's worth the extra few minutes to actually import your data in, rather than just open it. If you don't, Excel will take liberties with formatting your data, and you may not like the result.