How many data format available in Excel – Excel Interview Question

How many data format available in Excel – Excel Interview Question

From my previous blog How to change data format in Excel, I mentioned that Excel can present values into following 11 different data format available in excel:

[vtftable ]
Format;;;Usage;;;Example;;;Actual Value;nn;
General;;;Not specific number format;;;1.5;;;1.5;nn;
Number;;;general display of numbers;;;1.50;;;1.5;nn;
Currency;;;general monetary values;;;$1.50;;;1.5;nn;
Accounting;;;line up the currency symbols and decimal points in a column;;; $1.50 ;;;1.5;nn;
Date or Time;;;Date OR Time Format;;;1/01/1900 12:00:00 PM;;;1.5;nn;
Precentage;;;multiply the cell value by 100 and display result with a % symbol;;;150%;;;1.5;nn;
Fraction;;;(e.g. ½);;;1 1/2;;;1.5;nn;
Scientific;;;Display Scientific representation of a value;;;1.50E+00;;;1.5;nn;
Text;;;Treat everything in a cell as text even if it is a number;;;”1.5″;;1.5;nn;
[/vtftable]

However, does it really means that Excel stores the value in a cell in 11 different formats as the same way it present to you?

The answer is “NO”. Excel can present value in 11 different formats. However, Excel only store values in 2 different type:

  1. Numbers
  2. Text
  3. Formula

Example

[vtftable cols=”{0}0-1:ffff00;{/}”]
Formula;;;Value;;; ;;; ;nn;
=(1=1);;;TRUE;;; ;;; ;nn;
=(1=2);;;FALSE;;; ;;; ;nn;
;;; ;;; ;;; ;nn;
[/vtftable]

No matter you are using, the drop down from the number group or the Format Cell window in whatever platform (Ipad, Windows or even Mac), to change format, you are simply instructing Excel how to present a value in a cell, except you selected the “Text” format. When you selected Text format, you are directly instruction Excel to treat the value in the cell as a text.

Question:

Suppose I set the format of Cell J1 to “Date” and then typed abc. Why it still give me “abc” in J1 while the drop-down is still as “Date”

It is because Excel know that “abc” is not a date, so it simply display it as a text, but still remind you that this cell is formatted as a date.

So how to tell if the value is in text or a number data format?

The rule is simple:

If the cell have been manually formatted to “Text” format or the value is not a number or a date, then, it is a text. Otherwise, it is a number.

Date is a number in Excel?

Yes. Excel stores date as number of dates since 1 JAN 1900.

For Example

[vtftable cols=”{0}0-1:ffff00;{/}”]
Date;;;Value;;; ;;; ;nn;
1 JAN 1900;;;1;;; ;;; ;nn;
2 JAN 1900;;;2;;; ;;; ;nn;
5 JAN 1901;;;371 (366 + 5);;; ;;; ;nn;
[/vtftable]

Useful Functions

  • IsNumber()
  • IsText()
  • Value ()
  • Text()

IsNumber

The IsNumber function checks if the value is a number or not. This function will return a boolean value (either true or false)

The IsNumber function checks if the value is a number or not. This function will return a boolean value (either true or false)

[vtftable cols=”{0}0-1:ffff00;{/}”]
Formula;;;Vale;nn;
=isnumber(“ABC”);;;FALSE;nn;
=istext(“ABC”);;;TRUE;nn;
=isnumber(123);;;TRUE;nn;
=istext(123);;;FALSE;nn;
[/vtftable]

Value Function

  • Convert the input text into value
  • E.g. value (“123”) – > 123 as a number

Text Function

  • Convert the input value into text according to the specified format
  • Text(<Input value>, <Format>)
  • E.g. Text (“123456.12”,”###,###.##”) à “123,456.12” as a text

Leave a Comment