About entering text, numbers, dates, and times in a spreadsheet

Some of the content in this topic may not be applicable to some languages.

Entering text

Characters treated as text   In a spreadsheet, text is considered to be any combination of numbers, spaces, and nonnumeric characters ù for example, a spreadsheet treats the following entries as text:

10AA109, 127AXY, 12-976, 208 4675.

Entering numbers and dates as text   To enter numbers and dates so that they are stored as text, type an apostrophe (') at the beginning of the text. For example, to enter the part number 02-01-23 as text instead of a date, type '02-01-23.

Aligning text   By default, all text is left-aligned in a cell. You can change the alignment to be centered or right-aligned.

Text can't be wrapped in cells   You cannot display multiple lines of text in a single cell.

Repeating text   To enter the same text string in several cells, select the cells, type the text, and then press CTRL+ENTER.

Euro sign   To enter the euro sign ( ), make sure NUM LOCK is turned on, and then type ALT+0128 on the numeric keypad. If you have Microsoft Windows NT 4.0 with Service Pack 3 or earlier, you might need to install the euro product update from the Microsoft Web site. For information about connecting to the Microsoft Web site, see Help in your design program. The fonts that can display the euro sign are as follows.

Entering numbers

Characters that can be used as numbers   In a spreadsheet, a number can contain only the following characters:

0  1  2  3  4  5  6  7  8  9  +  û  (  )  ,  /  $  %  .  E  e 

The spreadsheet ignores leading plus signs (+) and treats a single period (.) within a number string as a decimal. All other strings that combine numbers with nonnumeric characters are treated as text.

Entering fractions   To avoid entering a fraction as a date, precede fractions with a 0 (zero) and a space; for example, type 0 1/2.

Entering negative numbers   Precede negative numbers with a minus sign (û), or enclose the numbers in parentheses ( ).

Aligning numbers   By default, all numbers are right-aligned in a cell. You can change the alignment to be centered or left-aligned.

How numbers appear   The number format that's applied to a cell determines the way the number is displayed. If you type a number into a cell that has the General number format, the spreadsheet might apply a different number format. For example, if you type $14.73, the spreadsheet applies a currency format. You can change the number format.

The General number format   In cells that have the default General number format, a spreadsheet displays numbers as integers (789), decimal fractions (7.89), or scientific notation (7.89E+08). Scientific notation is automatically used if the number is longer than the width of the cell. The General number format displays up to 11 digits, including a decimal point and characters such as "E" and "+." To use numbers that consist of more than 11 digits, you can apply the Scientific number format (exponential notation).

15-digit limit   Regardless of the number of digits displayed, a spreadsheet stores numbers with up to 15 digits of precision. If a number contains more than 15 significant digits, the spreadsheet converts the extra digits to zeros (0).

Regional settings   The characters recognized as numbers depend on the options you select in the regional settings of Microsoft Windows Control Panel. The options that are selected also determine the default format for numbers ù for example, the period (.) is used as the decimal symbol on United States Englishûbased systems.

Repeating data   To enter the same data in several cells, select the cells, type the data, and then press CTRL+ENTER.

Entering dates and times

Dates and times are numbers   A spreadsheet treats dates and times as numbers. The way that a time or date is displayed in a cell depends on the number format applied to the cell. When you type a date or time that the spreadsheet recognizes, the cell's format changes from the General number format to a date or time format. By default, dates and times are right-aligned in a cell. If the spreadsheet cannot recognize the date or time format, the date or time is entered as text, which is left-aligned in the cell.

Control Panel   Options you select in the regional settings of Microsoft Windows Control Panel determine the default format for the current date and time, including the characters that are recognized as date and time separators ù for example, the slash (/) and hyphen (û) for dates and the colon (:) for times on United States Englishûbased systems.

Use a four-digit year   When interpreting ambiguous dates, the spreadsheet makes certain assumptions. For example, when you enter a date that includes only the month and one or two digits, the spreadsheet assumes that 1 through 31 is the day and that the year is the current year. For instance, December 01 is assumed to be December 1 of the current year, not December of the year 2001. December 32 is assumed to be December 1, 1932. 12/1/30 is December 1, 1930, but 12/1/29 is December 1, 2029, unless your system administrator has changed the default cutoff dates.

To ensure that dates are interpreted by the spreadsheet the way that you intended, always type four digits for the year.

Entering dates and times together   To enter a date and time in the same cell, type a space between the date and the time ù for example, 7/10/1976 23:12.

Entering times as AM or PM   To enter a time based on the 12-hour clock, type a space and then AM or PM after the time ù for example, type 9:00 followed by AM or PM.

Viewing the serial number behind a date or time   Regardless of the format that's used to display a date or time, a spreadsheet stores all dates as serial numbers and stores all times as decimal fractions. To display a date as a serial number or display a time as a fraction, select the cells that contain the date or time, and change the number format to the General number format. In the General number format, the date portion of the number is to the left of the decimal point, and the time portion to the right of the decimal point. In a spreadsheet, the date serial number 1 corresponds to the date Sunday, December 31, 1899.

Calculating with dates and times   You can add and subtract dates and times and include them in other calculations. To use a date or time in a formula, enter the date or time as text and enclose the text in quotation marks. For example, the following formula would display a difference of 68:

="5/12/2004"-"3/5/2004"