Prevent Excel from changing numbers into dates

Sometimes importing data into spreadsheets gives you headaches. While this issue is not directly related to Datawrapper, it's 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. (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 to be 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 Excel 
  • BEFORE pasting your data, preformat the cells as text. To do so, select the empty cells where you will paste your data later, then open "Format cells" and select "Text". Click "Ok". 
  • Paste your data into Excel.

Please note that you can't 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 menu which pops up on the left side. 

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

Excel documentation has 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 whether 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