login | sign up

Your session has timed out due to inactivity.

You can go back to the overview without login or use the login form to login again before redirect

Knowledge Base / Administrators – one2edit™ Data Sources / Linking Excel-file cells to segments using one2edit™ Data Sources

Why is the formatting of cells in Excel important?

Created on 24th April 2018 at 11:48 by Jamie O'Connell



Because of the way that Microsoft Excel stores numbers, it is important to format the fields in your spreadsheet accordingly.

For example, numbers should be formatted as 'numbers', and not as 'general'. This is especially true if the numbers are not simple integers.

This lesson gives an example of how a number is stored in a 'general'-format cell in Excel, versus in a cell formatted as a 'number'.

NOTE:
Incorrect formatting of cells can lead to large numbers, which in turn can lead to overset text in tables. In such cases, please check the 'Cell Value' field in the one2edit™ Data Sources panel, as well as the cell-formatting in the Excel spreadsheet.

Excel cell is formatted as 'General'

  1. The cell contains the value '85.85', as shown in the 'Sample' field.
  2. However, the cell is formatted as 'General' instead of as 'Number' (or as another numerical format).

Unexpected 'Cell Value'

However, when we see the value provided to one2edit, it is not '85.85', but is instead '85.84999999999999'.

While the cell in Excel will still display '85.85', what is actually stored in the XLSX-format file is the above approximation.

This is due to the cell being formatted as a 'General' cell.

Excel cell is formatted as 'Number'

  1. The same cell contains the same value of '85.85'.
  2. The format (or 'category') has been switched to 'Number'.

NOTE:
It is not just the 'Number' category that will work. Choosing any 'numerical' category should have the same effect.

'Cell Value' is as expected

As we can see, when the cell format is not 'General', the number is stored in the XLSX-format file correctly.




© 2009-2024 1io BRANDGUARDIAN GmbH · All rights Reserved · Legal / Privacy Policy


top