Excel keeps changing my numbers into dates.
Sometimes importing data into spreadsheets can give 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 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 it 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 in 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 choose the little arrow next to "Paste" and then choose "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 with the pasted text.
Choosing the correct format in Datawrapper
Even if Excel now thinks that your numbers is text, our data visualization tool Datawrapper will be able to deal with them. As soon as you upload the numbers to Datawrapper, it guesses what kind of 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 which pops up on the left side:
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. This is less recommend than the next option:
- 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 space before number formats such as 1/2, to avoid the change to date.
- Change numbers that could be misread from 1/2 to 0.5 (or 0,5, if that is your local number format).