Excel keeps changing my numbers into dates.

Sometimes, importing data into spreadsheets can be a headache. While this issue is not directly related to Datawrapper, it's a common problem with Excel that can be frustrating and time-consuming: Excel is trained to "detect" formats, and sometimes the software wrongly assumes that a certain number represents a date. If you enter 12/2, it will change to 2-Dec or 12. Feb. If you then ask Excel to change the format to "Text," it shows you a weird high number like 44239. 

So, how can you show a column with values like 12/2 without having them converted into dates?

How to get around this in Excel

The trick is to choose the format of the column before you enter or paste the data:

  • Open Excel 
  • Select the columns or cells you want to paste into and choose "Text" in the format dropdown of the Home tab. You can also press Cmd+1 to select "Text" there and then click "Ok." 
  • Now, make sure that your data is pasted as text. To do so, go to the Home tab, then click on the little arrow next to "Paste" and select "Match Destination Formatting." (To achieve the same result, you can also go to Edit > Paste Special..., and then select "Text" before clicking "Ok.")
  • You'll see little green triangles in the top left of your pasted cells. Select them, click on the warning sign ⚠️ and select "Ignore Error."
Please note that you can't perform calculations such as =SUM() on the pasted text. 

Choosing the correct format in Datawrapper

Even if Excel now thinks that your numbers are text, our data visualization tool Datawrapper will be able to recognize them. As soon as you upload the numbers to Datawrapper, it guesses what data format your columns have. 

If that guess is wrong, you can easily change the format again. Click on the column, then choose the correct format in the drop-down menu that pops up on the left side:

Special formatting tricks to avoid having Excel change numbers into dates

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 data points were wrongly changed into dates. They are quite easy to spot in the columns.
  • To then change the format back: 
    • Add a space before the number. Press Enter to make sure that this space remains in the cell.
    • Or, add an apostrophe (') BEFORE you enter the number, e.g. '1/2. The apostrophe will not be displayed after pressing Enter.
    • Or, add a zero and space before number formats such as 1/2 to avoid the change to date format.
    • Or, change fractions into decimals, e.g. 1/2 to 0.5 (or 0,5, if that is your local decimal format).

Related Articles