Secrets of Excel dates and numbers

 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.

How do your Excel skills stack up?   

Test Now  

About the Author:

Matthew Goodall  

Matthew is a qualified Microsoft Office Specialist, Microsoft Certified Applications Specialist and a Microsoft Certified Trainer with over 11 years of hands-on experience in a training facilitation role. He is one of New Horizons most dynamic instructors who consistently receives high feedback scores from students. Matt enjoys helping students achieve real professional and personal growth through the courses he delivers. He is best known for creating “fans” of students, who regularly request him as an instructor for any future courses they undertake at New Horizons.

Read full bio