When using Excel to open, edit, or review a spreadsheet, Excel may convert the data automatically to another format. As our products can import documents made by Excel or export documents for use with Excel, it is important to be aware of this to mitigate any potential misinformation.
What Is Going On?
Excel will try to automatically convert data that it sees in a cell to what it thinks it should be. This is done by default. This can affect data such as dates, longs numbers, etc.
When Creating the File with Excel
- In the example below, we are trying to enter a bank account number manually. When we are done entering "123456123456", Excel has automatically converted the number into "1.23456E+11" in the cell. The actual data within the cell is still "123456123456"
- Another example is Excel dropping any leading 0's to a number.
- If we save a document with "123456123456" as a CSV (comma-separated values) file, the "1.23456E+11" is what is saved instead of "123456123456". This can be seen if you were to open the CSV file in Notepad like below.
- If we were to import this CSV file into any system, "1.23456E+11" will be imported and can result in further issues.
When Opening and Saving the File with Excel
- The same thing can occur when trying to open a previously made CSV with Excel. In our example below, we have opened the CSV with Notepad and we can see that the account number is correct and showing as "123456123456".
- When opening the CSV file in Excel, it will show the account number as "1.23E+11". Clicking into the field though shows the correct number.
- If we were to save this document in Excel as a CSV file, it will save the reformatted value. This can be seen when we open the CSV file in Notepad.
What Can I Do To Mitigate This?
While we cannot take into account every situation you may encounter, the following steps may help try to mitigate potential data formatting issues.
Change Cell Formatting
When creating or editing an Excel file, you can change the cell formatting to something that would allow the full number to show.
- In our example, we are looking at a number that is showing as "1.23456E+11," but is actually "123456123456".
- Right-click the column header and click Format Cells.
- In the following window, select a format that would best fit the data type you are entering. Click OK after selecting a cell format.
- You may have to click into the cell for the data to update the cell or re-type the data, but the data will now adhere to the formatting you selected.
Do Not Save After Opening An Existing File
Simply opening a file in Excel to review its contents is fine. It is when you save after opening an Excel document that can cause trouble. As Excel can automatically reformat data upon opening the file, you can prevent the reformatting of the data from being permanent by simply not saving the changes. One of the many habits that we develop from using computers is to constantly save to ensure that anything we are working on is not lost so you should be mindful of pressing "CTRL + S" out of habit.
Open the File In a Program That Does Not Reformat Automatically
In many of our examples above, we have been opening the CSV file in Notepad to view the contents. Notepad is a basic application that does not reformat any data allowing you to review the data before use. It may be tricky when reviewing the data as the data is not in a readable format so it is best to remember that the comma in a CSV file is where the cell data begins or ends.
- CSV (comma-separated value) File
- A CSV file is a common file used for the transferring of data from one system to another. As the name suggests, a comma is used to designate when the data for a cell begins and ends. When Excel opens a CSV file, it will use the commas to create the cells and make them into the readable format you are accustomed to. The same will happen if you were to save an Excel spreadsheet into a CSV file and perform the reverse, with a comma being placed around the data.