Excel / Google Sheet Formatting Tips

Updated by Theja Talla

Excel / Google Sheet Formatting Tips

  1. Scientific Notation / Rounding of Large Values
  2. Currency Notation for non-US Currency
1. Scientific Notation / Rounding

Issue: Excel truncates values with 10+ digits by rounding with scientific notation.

Examples of values we expect to see:

  • 138363285099
  • 138363285114
  • 138363213983

Examples of Excel output (with scientific notation "rounding")

  • 138363000000

Solution: to get around this in the future, if you need to make edits to a CSV

  1. Instead of opening a CSV in Excel, open the file with a basic Text Editor. On a Mac, this is simply the TextEdit app. On a standard on PC this is called "Notepad".
  2. Once the file is opened, make any edits to the format as you normally would in Excel (such as removing a "totals" row) and delete the entire file. This will prevent any unexpected data changes.
  3. Save it in Notepad or TextEdit as a CSV file.

2. Currency Notation for non-US Currency

Numerical formatting outside of the US may differ than the standard US-based formatting. In the European convention, a comma , is used as a decimal separator and a period . is used as a thousands separator.

For example the number 123456.789 can be represented as:

  • US convention 123,456.789
  • European convention 123.456,789

Customers that export data into a google sheet configured for the European convention can run into number formatting challenges. This will come up for customers using our adhoc exports feature.

The problem is that the number 123456.789 in the source export will be represented as 123456789 in the google sheet and will be visualized as 123.456.789

Solution

  1. Import the data into the spreadsheet by:
  • Click File > Import
  • Click on the tab “Upload” and then click “Browse” and select the file from your computer
  • Choose “insert new sheets” and UNCHECK convert text to numbers, dates, and formulas
  1. Formula to convert conversions to number format 1.234,5
    =text(SUBSTITUTE(cell_reference;".";",");"#,##0.0;(#,##0.0);0")
  2. Formula to convert revenue/spend to number format €1.234,56
    =text(SUBSTITUTE(cell_reference;".";",");"€#,##0.##;(€#,##0.##);€0")

How did we do?