Prevent Excel from changing numbers into dates

Sometimes importing data into spreadsheets gives you headaches. While this issue is not directly related to Datawrapper, but a common and known issue with Excel - which can be frustrating and time consuming: Excel is trained to "detect" formats, and sometimes the software tends to wrongly assume that a certain number represents a date and changes it. So 12/2 will change to 2-Dec. But there are other formats where this might happen although the number is not a date at all. 

Quote from the official Excel documentation: 

"Microsoft Excel is preprogrammed to make it easier to enter dates. For example, 12/2 changes to 2-Dec. This is very frustrating when you enter something that you don't want changed to a date. Unfortunately there is no way to turn this off. But there are ways to get around it." 
Source: Microsoft. applies to Excel 2013, but might occur in other Excel versions as well. 

How to get around this - in Excel:

  • Open your Excel workbook
  • BEFORE pasting your data preformat the cells as text
  • Select the the so far empty cells (a range or all)
  • Then open "Format cells" and select "Text". Click "OK".
  • Paste your data into excel

There is a bit of an issue when numbers are formatted as text. For example: You can perform calculations such as SUM with text. 

Choosing the correct format in Datawrapper
Should you have a dataset which is "wrongly" formatted as text, but should be numbers, Datawrapper has a strong feature detecting the data format. The software guesses what kind of data is in the columns. If that guess is wrong, you can easily change the format again - e.g. when you imported numbers as text because of the issue above. Click on the column, then choose the correct format in the drop down menue which pops up on the left side. 

Manual inspection and special formats to avoid that Excel changes the number into a dat

Excel documentation has a some additional tips on how to avoid that automatic change of numbers:

  • Manual inspection
    If your dataset has only a few lines you can manually check wether datapoints where wrongly changed into dates. They are quite easy to spot in the columns.
  • To then change the format back you enter: 
    • A space before the number. Press enter to make sure that this space remains in the cell.
    • Add an apostrophe (') BEFORE you enter the number, e.g. '1/2. The apostrophe will not be displayed after pressing Enter.
    • Add a zero and a space before number formats such as 1/2, to avoid the change to a date.
    • Change numbers that could be misread from 1/2 to 0.5 (or 0,5, if that is your local number format).

Source and more information:  Microsoft

Still need help? Contact Us Contact Us