Jan 27, 2017

This information isn’t meant to be ‘secret’ but I find that not a lot of people will tell you this about Excel.

If you go back and read (or reread) Secrect of Excel dates, you’ll remember that Excel stores dates as numbers. The number of days since the 1st of January 1900.

But what happens if Excel doesn’t understand the date you type in, or even more likely you generate a report or list from a database, and it doesn’t seem to recognise the date or number as a date or number?

If I create a new sheet, go to cell F1, and type in a number into the cell, Excel puts it on the right hand side of the cell. This is an important clue that Excel is recognising it at as number, and not a piece of text or something else.

Note: Of course you can use the Alignment buttons to override Excel’s natural inclination but notice that if none of the alignment buttons are activated, then Excel stores text on the left and numbers (and dates which are numbers) on the right side of the cell.

But what if I want Excel to store a mobile phone number that starts with a 0?

If I just type it in, Excel will think it’s a number and get rid of the leading zero, because it doesn’t store numbers that way.

There are two ways to force Excel to store this mobile phone number as a text and not a real number.

1. Put a single talking mark in front of the number like so: '04123456789. Excel will store it as piece of text even though it suspects that it should be stored as a number. Go to cell F3 and type in 04123456789 and widen the column. See how it sits on the left hand side.

2. Format the cell to the Text format before you type in the cell. This means Excel assumes anything, including numbers, dates, or even formulas, should be stored as is and not changed. Go to cell J3 and change the format of the cell to Text. Then type in 04123456789 and widen the column, again Excel treats this as a piece of text, not a number.

This is handy to know, and now you can enter in mobile numbers and other numbers that start with a zero and have them inputted correctly.

However, what if the reverse happens? Say you receive some data that you need to work with as numbers and you can see the data is on the left side of the cell and it has that green triangle in the top left corner. To convert a number stored as text back to a piece of text, simply hover over the cell, click on the dropdown, and choose Convert to Number.

This makes a big difference if you need to sort and filter or do formulas on these numbers and have Excel treat them as numbers.

For more information, take a look at New Horizons' Microsoft Excel courses.