Excel Data Types and Number Formats Explained

A data type in Excel is the kind of value a cell holds. There are four main ones: numbers (dates and times are numbers too), text, logical values (TRUE and FALSE) and errors such as #N/A. A number format is a different thing: it controls only how a value is displayed, and the value stored in the cell does not change.

Mixing the two up causes a whole family of “my formula gives the wrong answer” problems: totals that skip numbers because they are really text, dates that appear as 45292, and sums that look wrong because the cells were rounded only on screen. This guide shows how to tell the types apart, how formats work, and how to fix each problem. Every result comes from running it in Microsoft Excel.

The four data types and how to tell them apart

The grid below holds one example of each type. Column B shows the result of TYPE(), which returns a code for the type, and columns C and D test the two most common ones. Notice how Excel aligns each value:

Rows 2 and 3 look almost identical, but row 2 is the number 123 (right-aligned, ISNUMBER is TRUE) and row 3 is the text “123” (left-aligned, ISNUMBER is FALSE). Row 7 shows that a date is an ordinary number, with TYPE 1. That is the root of the next two sections.

Numbers stored as text

A number stored as text looks like a number but is a piece of text. It usually comes from imported data, copied web tables, or a cell that was formatted as Text before the number was typed. Excel often marks such cells with a small green triangle. The cell A2 below holds the text 20 between two real numbers:

The dangerous case is when all the numbers are text. SUM does not complain, it just returns 0:

To fix a column for good, use Data, Text to Columns, Finish on the column, or type 1 in an empty cell, copy it, select the numbers and use Paste Special, Multiply. Both turn the text into real numbers. To fix them inside a formula, use VALUE or the double minus.

Dates and times are numbers

Excel stores a date as the number of days since 1 January 1900, so 1 January 2024 is 45292. It stores a time as a fraction of a day, so noon is 0.5. The cell shows a date only because it has a date format. That is why you can subtract dates and add days:

If a date suddenly shows as 45292, its format was changed to General or Number. Set the cell back to a date format (Ctrl + 1, then Date) and the value is unchanged.

Number formats change the display, not the value

A number format is a display rule attached to a cell. It never touches the stored value. That is normally what you want, but it produces one very confusing situation. Here three cells hold 1.4 and are formatted with no decimals, and the total is the sum of the real values:

The screen says 1 + 1 + 1 = 4, and both are “right”: the cells show 1, but they really hold 1.4, so the total is 4.2, which displays as 4. Multiply the total by 10 and you get 42, not 40. If you want the total to match what is shown, round the values themselves:

The rule of thumb: use a format when you only want to show fewer decimals, and use ROUND when the value itself must change, for example before a total that people will check by hand.

Custom number formats

Press Ctrl + 1, choose Custom and type a format code. A code can have up to four sections separated by semicolons: positive; negative; zero; text. Here are common ones, each applied to the typed value shown:

In every row the cell still holds the number you typed. The format only decides how it looks, and formulas that refer to the cell use the real value.

Leading zeros and long numbers

Excel treats anything that looks like a number as a number. That is helpful for prices and unhelpful for IDs: it removes leading zeros and it keeps only 15 significant digits. Anything after the 15th digit becomes zero:

For codes, phone numbers, account numbers and anything else you will not calculate with, store the value as text: format the cells as Text before typing, or start the entry with an apostrophe ('007). If the value must stay a number, keep it a number and use a custom format such as 00000 to show the zeros:

A1 holds the number 123 with the format 00000

A1 is still the number 123, so A1+1 works

The TEXT function

A cell’s format disappears the moment you join it into a text formula, because the join uses the raw value. TEXT(value, format_code) converts a value to text with the format you choose, using the same codes as the custom formats above:

Remember that the result of TEXT is text. It lines up to the left, and functions like SUM will ignore it. Use it for labels and messages, not for numbers you still need to add.

Empty cells, zero and empty text

An empty cell, a cell holding 0 and a cell holding empty text (="") are three different things that often look the same. A1 below is empty, A2 holds the formula ="" and A3 holds 0:

The practical lesson: test emptiness with =A1="" if formulas may return empty text, and use ISBLANK only when you need to know the cell is really empty.

Common mistakes

Mistake 1: trusting the display

What you see is a formatted view of the value. If two numbers look equal but a comparison says they are not, or a total looks off by one, check the real value with General format or the formula bar, and use ROUND when needed.

Mistake 2: a column that is too narrow

A number or date that does not fit the column width is shown as hash signs. It is not an error and the value is fine. Widen the column:

Mistake 3: formatting a cell as Text and then typing a formula

Cells formatted as Text keep whatever you type as text, including formulas. A1 below was formatted as Text first, B1 was not. Both were given the entry =1+1:

If a formula shows up as text, change the format back to General and re-enter it (press F2, then Enter).

Mistake 4: joining a date or a percentage without TEXT

As shown above, ="Due "&A1 prints the serial number. Always wrap dates, percentages and decimals in TEXT when you build sentences.

Cheat sheet

Try it yourself

Work out each answer first, then open the solution. Type the examples into a worksheet to check.

1. A1:A3 hold the text 10, 20 and 30, and =SUM(A1:A3) shows 0. Give two formulas that return the real total.

Show solution

A1:A3 hold the text 10, 20 and 30

Formula	Excel shows
=SUM(A1:A3)	0
=SUM(–A1:A3)	60
=SUMPRODUCT(–A1:A3)	60
=SUMPRODUCT(A1:A3*1)	60

=SUM(--A1:A3) and =SUMPRODUCT(--A1:A3) convert the text before adding. To fix the cells themselves, use Text to Columns.

2. A1 holds 0.075 and is formatted as 0%. What does the cell show, and what does =A1*100 return?

Show solution

The cell shows 8%, because the format rounds for display only. =A1*100 returns 7.5, because the stored value is still 0.075.

3. You need to display the number 123 as 00123 but still be able to add 1 to it. How?

Show solution

Number 123 with the format 00000

	A	B
1	00123	124

Still a number

	A	B
1	00123	=A1+1

Keep it a number and apply the custom format 00000. Typing the text 00123 would keep the zeros too, but it would no longer be a number.

4. A1 holds a date. Write a formula that returns a sentence such as Invoice date: 15 Mar 2024.

Show solution

A1 holds 15 March 2024

Formula	Excel shows
="Invoice date: "&TEXT(A1,"dd mmm yyyy")	Invoice date: 15 Mar 2024

Without TEXT, the sentence would end with the serial number 45366.

Frequently asked questions

What are the data types in Excel?

Numbers (including dates and times), text, logical values (TRUE and FALSE) and errors such as #N/A. Every cell holds exactly one of them, or is empty.

Why does Excel show my date as a number like 45292?

Excel stores dates as serial numbers, and the cell has lost its date format (it is set to General or Number). Apply a Date format with Ctrl + 1 and the date returns; the value does not change.

How do I convert text to numbers in Excel?

Use VALUE(A1) or --A1 in a formula, or fix the cells themselves with Data, Text to Columns, Finish, or with Paste Special, Multiply by 1.

Does formatting a cell change its value?

No. A number format changes only how the value is displayed. Use ROUND when you need the stored value to change.

How do I keep leading zeros in Excel?

Format the cells as Text before typing, or start the entry with an apostrophe. If the value must remain a number, use a custom format such as 00000.

Why do long numbers end in zeros?

Excel keeps only 15 significant digits of a number. For 16-digit values such as card or account numbers, store them as text.

What does the TEXT function do?

TEXT(value, format_code) turns a number or date into text formatted the way you specify, so it can be joined into a sentence. Its result is text, not a number.

What is the difference between an empty cell and a cell with zero?

An empty cell holds nothing, a zero cell holds the number 0, and a formula such as ="" holds empty text. They behave differently in COUNT, COUNTA, ISBLANK and comparisons.