Spreadsheet Data Types
A worksheet is built up by entering data into cells. Four data types can be entered in a cell: Text, Values, Dates, and Formulas.
In most cases, Excel will identify the type of data you are trying to enter and format the cell appropriately. You can also apply your own formatting to a cell to make it look the way you want.
When entering data, it is extremely important to be careful and methodical. You should check what you type against any source material very carefully (for example, if you are updating a worksheet from a paper copy). It is quite hard to spot mistakes in a spreadsheet with many cell entries.
Text
Text cells are often used as labels to identify the information entered in columns or rows, or you may want to store text data, such as names.
Text can include any letters, numbers, and symbols on the keyboard. Up to 32,000 characters can be entered as text in a single cell. Text cells have a value of 0 (zero) if used in a calculation.
Entries containing a mix of letters and numbers are always treated as text. Type an apostrophe (') at the beginning of the entry to force Excel to format the entry as text. For example, if you want to store a telephone number in a cell, it should be treated as text not a value, so you would type: '0800 150 150
Text will automatically align on the left of a column, but can be moved to the right or centre. If text is wider than the column it is entered into, it will appear to overflow onto the adjacent cell if that cell is blank, otherwise the text will appear truncated. Changing the column width affects how much of the entry can be seen. (A formatting option allows text to word wrap onto multiple lines within the width of a column, with a corresponding increase in the height of the row).
Values
Values are usually number entries and used in calculations on the sheet. Numbers can be formatted to appear in different ways, for example with or without currency symbols or with as many decimal places as required.
Numbers that contain too many digits for the width of the column are displayed as hash symbols (####). The number can be displayed either by holding the mouse pointer over the cell to display the Narrow Column ScreenTip or by adjusting the column width.
Numbers can be formatted to display in many different ways by using the formatting commands.
| Value Type |
Formats |
| Numbers |
123
123.00
00123
0.123
1.23 |
| Currency |
£1,234.50 |
| Accounting |
£ 1,234.50 |
| Percentage |
12.3% |
| Fraction |
1 2/9 |
All number formats are aligned to the right-hand side of the cell by default.
Dates
Dates (and times) are values that can be used in calculations to create new dates or calculate time intervals. Excel counts dates as the number of days from January 1st 1900 and stores the date as a number. For example, 1st May 2002 is stored as 36647. However the date is formatted to display in a more traditional manner.
Excel recognises dates separated with a slash (/) or a hyphen (-) or dates entered using the month name. Times should be separated by colons (:).
When you enter a date that Excel recognises, the cell will be formatted in one of the default styles, depending on what you typed. All date and time formats are aligned to the right-hand side of the cell.
| You Type |
Excel Displays |
You Type |
Excel Displays |
| 28/9 |
28-Sep |
9:00 |
09:00 |
| 28/9/2002 |
28/09/2002 |
9:00 a |
9:00 AM |
| 28 Sep |
28-Sep |
9:00 p |
9:00 PM |
| 28 September 2002 |
28-Sept-02 |
21:00 |
21:00 |
Year 2000 Best Practice
It is important that anyone creating or using a spreadsheet understand the year 2000 problem - often referred to as the "Millennium Bug". Essentially, the year 2000 problem means that if you enter a date using two digits to represent the year, an error could occur in any calculations involving that date. This is true even after the year 2000 has passed.
Year 2000 best practice means learning always to enter a date using four digits for the year, and to create spreadsheets that always store and display the date this way.
Excel 2002 itself is a compliant application. That means if you enter unambiguous, 4-digit year format dates, no errors will occur. Excel uses the Windowing Technique set in Windows Regional Options to interpret dates entered using a 2-digit year. By default, a 2-digit year less than 30 is assumed to be in the 21st century (for example 31/12/25 is assumed to be 31/12/2025). Conversely, a 2-digit year equal to or greater than 30 assumed to be in the 20th century (for example, 13/12/31 is assumed to be 13/12/1931).
As mentioned above, you should train yourself to always type in the date using four digits for the year. However, to make it obvious how Excel handles 2-digit years, you should set the short date style in Windows Regional Options (or Regional Settings) to a YYYY format.
Formulas
Formulas are instructions that perform calculations on the sheet. Formulas can be very simple or extremely complex and the result (the data that actually appears in the cell) may be text, a value, or a date.
A formula begins with an equals sign (=) followed by one or more values and functions to calculate. The values can be entered directly into the formula, but it is more effective to enter the values into cells on the worksheet and refer to those cells in the formula.
Formulas can include arithmetic operators, specialised functions such as averaging or totalling, or be used to transfer data around the sheet.
| Function |
Description |
Example |
| + |
Add |
=A1+A2 |
| - |
Subtract |
=A1-A2 |
| * |
Multiply |
=A1*A2 |
| / |
Divide |
=A1/A2 |
| ^ |
Exponential |
=A1^3 |
| SUM |
Total of values |
=SUM(A1:A20) |
| AVERAGE |
Average of values |
=AVERAGE(A1:A20) |
| Cell reference |
Transfer data |
=A1 |
