The always awesome Chandoo comes up with a tip that needs to shared as widely as possible, more about which in just a second.

Have you ever needed to enter numbers in a sheet where for whatever reason, they need to be entered as 001, rather than 1?

Of course you have, who hasn’t? Well, in that case, you have also obviously suffered from the bang-my-head-against-the-wall syndrome, because Excel is, in this case, more stubborn than an obdurate mule.

If you don’t know what I’m talking about, simply fire up an Excel sheet and enter “001″ without the inverted commas in any cell

… and hit Enter

Slightly frustrating, isn’t it? You want Excel to show the leading zeros, but Excel couldn’t care less.

There are a couple of  workarounds to this, which are mentioned in Chandoo’s blog post linked to above, but there’s one that is even simpler than the solutions he has mentioned… simply append a leading ‘ to the number you wish to enter.

Once you press enter, the leading zero’s don’t disappear!

The only thing is that Excel gently reminds you that what you’ve entered as text really seems to be a number:

And the number, since it has been formatted as text, is left-aligned. But it is a pretty quick workaround all the same!

Other posts you might like: