Introduction

There’s something creepy going on in your spreadsheet, and it’s not nice.

“Even the moon is frightened of me”, shrieks the Invisible Man, otherwise known as Dr Griffen, a genius driven mad after experimenting on himself! If you have the time, watch the short trailer for the 1933 film. Creepy things start happening to people in a small town: mysterious footsteps in the snow, household objects seemingly flying through the air, people being pushed down stairs (and over cliffs!). And in our case: messing with our spreadsheet.

We can’t see The Invisible Man himself (obviously, or he wouldn’t be invisible). But we can find traces of his work in our spreadsheet, as we can see his footsteps in the snow:

One error he inserted you can clearly see, but the others are far harder to spot with the naked eye:

• Added extra white spaces at the ends of entries.
• Tabs that are inserted at the ends of lines
• Line breaks and ‘carriage returns’, which you insert by pressing enter (or Ctrl-Enter).

They’re called “non-printable” characters, and aren’t displayed all the time in spreadsheets. But you will still feel their sinister presence as they seriously affect data analysis. This is because spreadsheets treat these sorts of characters as real data. Ignoring the column label, in the data above you can see four terms that are essentially the same. The spreadsheet, however, sees four different, distinct pieces of data. If you were trying to count how many times “Your Data” was mentioned, a spreadsheet would only show a single entry.

In the film, the police set up traps to catch the invisible man. We can do the same in our spreadsheets. By the end of this section you should have:

• some knowledge about how non-printable characters cause errors in data
• tried out different functions and features of the spreadsheet that will remove them