The following formats are available in Excel:
What are the various data formats in Excel?
- Text format – This may include text as well as alphanumeric strings (eg ABC123). A text string can also contain punctuation and symbols.
- Number formats – There are also different formats within numbers. For example you can have decimals, fractions, thousand separators, etc. No matter what format is applied, you can use numbers such as addition, subtraction, division and multiplication.
- Date format – The most important thing to know about dates is that they are stored as numbers in Excel. However, you can format it to show as a date. For example, 01-01-2019 will be saved in Excel as 43466, which is the number for the given date. In Excel, you can show dates in different formats such as long date (01 January 2019), short date (01-01-2019), etc.
- Accounting / Currency Format – When you apply an accounting / currency format to a number, Excel adds a currency symbol with two decimal places.
- Percent Format – You can format the numbers to be shown as a percentage. For example, you can show 0.1 as 10% when you apply the percentage in the format.
How to change the data format in Excel interview question, I mentioned that Excel can present the following values in 11 different formats:
| Format | Usage | Example | Actual Value |
| General | Not specific number format | 1.5 | 1.5 |
| Number | general display of numbers | 1.50 | 1.5 |
| Currency | general monetary values | $1.50 | 1.5 |
| Accounting | line up the currency symbols and decimal points in a column | $1.50 | 1.5 |
| Date or Time | Date OR Time Format | 1/01/1900 12:00:00 PM | 1.5 |
| Precentage | multiply the cell value by 100 and display result with a % symbol | 150% | 1.5 |
| Fraction | (e.g. ½) | 1 1/2 | 1.5 |
| Scientific | Display Scientific representation of a value | 1.50E+00 | 1.5 |
| Text | Treat everything in a cell as text even if it is a number | ”1.5″ | 1.5 |
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 a value in 11 different formats. However, Excel only store values in 2 different type:
- Numbers
- Text
- Formula
Example
| Formula | Value | ||
| =(1=1) | TRUE | ||
| =(1=2) | FALSE | ||
No matter what you are using, the number group or the drop down from the Format cell window in whatever platform (Ipad, Windows or Mac), to change the format, you are simply instructing Excel that you are in the cell How to present a value, except your “text” format. When you have selected the text format, you instruct Excel to treat the value in the cell directly as 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
| Date | Value | ||
| 1 JAN 1900 | 1 | ||
| 2 JAN 1900 | 2 | ||
| 5 JAN 1901 | 371 (366 + 5) |
Useful Functions
- IsNumber()
- IsText()
- Value ()
- Text()
IsNumber
The IsNumber function checks whether the value is a number. This function will return boolean values (either true or false)
The IsNumber function checks whether the value is a number. This function will return boolean values (either true or false
| Formula | Vale |
| =isnumber(“ABC”) | FALSE |
| =istext(“ABC”) | TRUE |
| =isnumber(123) | TRUE |
| =istext(123) | FALSE |
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
