Excel Hack - Using a "Magic" Apostrophe to Force a Cell to be Text

In Excel you can prefix values in cells with an apostrophe to force Excel to interpret them as text.  Why do this you may ask?

To explain, let's say I want to enter James Bond's telephone number into a spreadsheet.  All I have to do is type 01234007007 (which everyone knows is his number) into a cell, right?  Wrong!  Let's see it in action.  First I type his number (numbers in the UK always start with a 0, that includes MI6 spies):

Because of the leading zero, when I press return, Excel strips the first 0 from what I've typed:

I can get around this by prefixing 007's number with an apostrophe (so a ').  Here we go:

In the image above you can see how the apostrophe is not visible in the cell itself but is in the formula bar at the top right.

Note you also get a little helpful hint warning you there's an apostrophe there.  If you hover over the exclamation mark you get a pop up that says "The number in this cell is formatted as text or preceded by an apostrophe".

So I often use the apostrophe trick for phone numbers or to put formatting in cells that Excel may complain about.  Say I want to write some text in a cell with several sub-points.  You can't do bullet points in a cell so I often prefix lines with a hyphen.  So I type this in:

Hit return and I get:

Oh no!  Because I start with a hyphen, Excel thinks it's a formula with a minus sign, can't interpret the rest of the formula and throws an error.  Put in a magic apostrophe and you get:

Again you can see the apostrophe in the formula bar (top right of the image) but not the cell itself.

Hope that's useful!