Excel Data Types

Number Values

Number values consist of entries of any combinations of numbers and equation symbols that are used in mathematical calculations. Excel recognizes the following values as numbers: 0 1 2 3 4 5 6 7 8 9 + - ( ) , / $ % . E e

Numbers can be formatted to display in different ways by using formatting commands, as shown in the following table:

Type of Value Entry Made Display General Number 245 245.00 00245 .245 2.45 245 245 245 0.245 2.45 Currency $357.5 $357.50 Accounting $357.5 $ 357.50  Percentage .25 3/4 25% 75% Fraction .75 25% 3/4 1/4

If the number of digits in a number entry exceeds the column width, the numbers will be displayed as hash symbols (####). The numbers will display normally when the column is made wide enough for all the numbers to fit correctly.

Mathematical calculations such as addition or subtraction can only be done on the numbers data type. So when you data enter a number, Excel automatically records it as the number data type, and automatically right-aligns the number in the cell as shown below:

Text Values

Text entries include any combination of letters, numbers or symbols on the keyboard. Excel will accept up to 32,000 text characters in a single cell.

Entries that contain both letters and numbers are always recorded as text by Excel. If a text entry is included in a mathematical calculation, Excel will assign the entry a value of 0 (zero).

To get Excel to record a number as text, you have to specifically set the data type to "text" otherwise Excel will record it as a number. A text entry or a number that has been recorded as text will be left-aligned in the cell as shown below:

You can force Excel to enter a number as text by entering the number in the formula box with an apostrophe (') in front of it. As an example, if you enter '10 in the formula box, then 10 will be displayed in the cell as a text value.

If text is wider than the column it is entered into it will overflow into the cell immediately to its right, if that cell is empty. If it is not empty, then the text will be cut off at the point where the next cell begins.

Text entries include labels or headers and information that is being stored in a worksheet, such as names, phone numbers and so on.

Date Values

Dates values are used to display dates and time and to create or calculate date or time intervals. Excel actually stores dates as a numeric value which is the count of the number of days from January 1st 1900. For example, the date value Excel stores for January 1, 2000 is 37257. For display purposes, the dates can appear in various familiar date formats. As an example, the date January 1, 2002 may be data entered as 1/1/02 and set to be displayed as any of the following:

• 1/1/02
• 1-Jan
• 1-Jan-2002
• 01/01/02
• January 1, 2002

By default, Excel records the entry of fractions such as 1/2, 1/4 and so on as the "date" data type. In the example below, row 1 shows a fraction that has been entered as text. Row 2 shows what Excel does with a fraction entry if you do not specify it as a fraction. In both cases, the fraction 1/5 was entered.

To get Excel to record a fraction as text or as a number, you have to specifically set the data type to text or to a fraction format, otherwise Excel will record it as a date.

You can force Excel to enter a fraction as text by entering in the formula box with an apostrophe (') in front of it. As an example, if you enter '1/2 in the formula box, then 1/2 will be displayed in the cell as a text value. You can see how it is entered in the formula box in the example below:

Formulated Values

Formulated values are created by entering equations that perform calculations or instructions that manipulate information into the formula box of a specified cell. The equation or instruction is referred to as a "formula". The formula itself is entered in the formula box for a specific cell and the result of the formula is displayed in the cell itself.

In the example below, a simple equation (10+10) has been entered in the formula box. The result (which is 20) is displayed in cell A1:

Formulas can be very simple arithmetic equations such as addition or subtraction or extremely complicated or specialized equations, such as averaging or totalling. They can also be instructions to change or manipulate text entries, such as combining the text from two cells into a third cell. The result of a formula can be a number, date or text value, depending on the equation or instructions used in the formula.

In the example above, numeric values (10 + 10) were used in the equation to demonstrate the relationship between the equation in the formula box and the result displayed in the cell.

The real power of Excel is that the cell address containing a value can be entered into an equation and the result will instantly change every time the value in the referenced cell changes.

In the example below, the values 10 + 10 have been entered into cells A1 and B1. The formula that has been entered in cell C1 uses these two cell addresses to capture the values. The result is displayed in cell C1.

In the next example, the formula in cell C1 is exactly the same. The value entered in cell A1 has been changed. The result of the formula in cell C1 instantly changes as well.

The speed at which Excel can calculate formulas is restricted only by the capability of the computer it is installed on and the number of values it is instructed to perform its calculations on. For most uses, the calculations are virtually instantaneous and unless there is an error in the formula, completely error free.

Click here to go to the next lesson