Entering Data in Excel Entering numbers text a

Entering Data in Excel

Entering numbers, text, a date, or a time 1 Click the cell where you want to enter data. n 2 Type the data and press ENTER or TAB. (Both operations accept the data but ENTER moves you down one cell, TAB moves you to the right one cell. ) Alternatively, use the arrow keys. n

If your system does not do that, someone has changed the setting. n To reinstate the setting do a n Tools => Options… and set it as in the following dialog box:

Some Tips To fill in rows of data in a list, enter data in a cell in the first column, and then press TAB to move to the next cell to the right. n At the end of the row, press ENTER to move to the beginning of the next row. n

Tips on entering numbers Numbers that you type in a cell are constant values. n In Microsoft Excel, a number can contain only the following characters: n n 0 1 2 3 4 5 6 7 8 9 + - ( ) , / $ %. E e

Microsoft Excel ignores leading plus signs (+) and treats a single period as a decimal. n All other combinations of numbers and nonnumeric characters are treated as text. n The characters recognized as numbers depend on the options you select in the Regional Settings of Control Panel. n

To avoid entering a fraction as a date, precede fractions with a 0 (zero); for example, type 0 1/2 (leave a space after the 0). n Precede negative numbers with a minus sign (-), or enclose the numbers in parentheses ( ). n

All numbers are right-aligned in a cell by default. n To change the alignment, select the cells. n On the Format menu, click Cells, click the Alignment tab, and then select the options you want. n

The number format applied to a cell determines the way Microsoft Excel displays a number on a worksheet. n If you type a number into a cell that has the General number format, Microsoft Excel may apply a different number format. n For example, if you type $14. 73, Microsoft Excel applies a currency format. n

To change the number format, select the cells that contain the numbers. n On the Format menu, click Cells, and click the Number tab, and then select a category and format. n

n In cells that have the default General number format, Microsoft Excel displays numbers as – – – n integers (789), decimal fractions (7. 89), or scientific notation (7. 89 E+08) if the number is longer than the width of the cell. The General format displays up to 11 digits, including a decimal point and characters such as "E" and "+. "

n To use numbers with more digits, you can apply a built-in scientific number format (exponential notation) or a custom number format.

Regardless of the number of digits displayed, Microsoft Excel stores numbers with up to 15 digits of precision. n If a number contains more than 15 significant digits, Microsoft Excel converts the extra digits to zeros (0). n

n Microsoft Excel stores a number as numeric data even if you use the Cells command to apply the Text format to the cells that contain the numbers.

To have Microsoft Excel interpret numbers such as inventory part numbers as text, first apply the Text format to empty cells. n Then type the numbers. n If you've already entered the numbers, apply the Text format to the cells. n Click each cell, press F 2, and then press ENTER to reenter the data. n

Tips on entering text n In Microsoft Excel, text is any combination of numbers, spaces, and nonnumeric characters. For example, Microsoft Excel treats the following entries as text: – 10 AA 109, 127 AXY, 12 -976, 208 4675.

All text is left-aligned in a cell by default. n To change the alignment, click Cells on the Format menu, click the Alignment tab, and then select the options you want. n

n To display all the text on multiple lines in the cell, select the Wrap text check box on the Alignment tab.

n To enter a "hard" carriage return in a cell, press ALT+ENTER.

Dates and times n Use a slash or a hyphen to separate the parts of a date; for example, type 9/5/96 or Jun-96. n To enter a time based on the 12 -hour clock, type a space and then a or p after the time; for example, 9: 00 p. Otherwise, Microsoft Excel enters the time as AM.

To enter today's date, press CTRL+; (semicolon). n To enter the current time, press CTRL+SHIFT+: (colon). n

Tips on entering dates and times Microsoft Excel treats dates and times as numbers. n The way that a time or date is displayed on a worksheet depends on the number format applied to the cell. n When you type a date or time that Microsoft Excel recognizes, the cell's format changes from the General number format to a builtin date or time format. n

By default, dates and times are right-aligned in a cell. n If Microsoft Excel cannot recognize the date or time format, the date or time is entered as text, which is left-aligned in the cell. n

n Options you select in the Regional Settings of Control Panel determine the default format for the current date and time and the characters recognized as date and time separators, for example, the colon (: ) and slash (/) on United States-based systems.

To type a date and time in the same cell, separate the date and time with a space. n To type a time based on the 12 -hour clock, type a space followed by AM or PM (or A or P) after the time. n Otherwise, Microsoft Excel bases the time on the 24 -hour clock. For example, if you type 3: 00 instead of 3: 00 PM, the time is stored as 3: 00 AM. n

Regardless of the format used to display a date or time, Microsoft Excel stores all dates as serial numbers and stores all times as decimal fractions. n To display a date as a serial number or display a time as a fraction, select the cells that contain the date or time. n On the Format menu, click Cells, click the Number tab, and then click General in the Category box. n

Times and dates can be added, subtracted, and included in other calculations. n To use a date or time in a formula, enter the date or time as text enclosed in quotation marks. For example, the following formula would display a difference of 68: n – ="5/12/94"-"3/5/94"

In Microsoft Excel for Windows (and Lotus 1 -2 -3), days are numbered from the beginning of the century; the date serial number 1 corresponds to the date January 1, 1900. n Microsoft Excel for the Macintosh uses the 1904 date system; the date serial number 1 corresponds to January 2, 1904. n

To change the date system for use in calculations, click Options on the Tools menu, and then click the Calculation tab. n Under Workbook options, select the 1904 date system check box. n Use the 1904 date system for a workbook if you use that workbook with other workbooks that use the 1904 date system. n

If you open, in Microsoft Excel for Windows, a file created in Microsoft Excel version 2. 0 or later for the Macintosh, Microsoft Excel recognizes the file format and automatically changes dates to the 1900 date system. n Similarly, if you open a Microsoft Excel for Windows file on a Macintosh, Microsoft Excel changes dates to the 1904 date system. n

To enter the same data into several cells at once 1 Select the cells where you want to enter data first. (The cells can be adjacent or nonadjacent. ) n 2 Type the data (it will appear in the formula bar) and press CTRL+ENTER. n

To quickly fill in repeated entries in a column If the first few characters you type in a cell match an existing entry in that column, Microsoft Excel fills in the remaining characters for you. n Microsoft Excel completes only those entries that contain text or a combination of text and numbers; entries that contain only numbers, dates, or times are not completed. n

To accept the proposed entry, press ENTER. n The completed entry exactly matches the pattern of uppercase and lowercase letters of the existing entries. n

To replace the automatically entered characters, continue typing. n To delete the automatically entered characters, press BACKSPACE. n You can also select from a list of entries already in the column. n To display the list, press ALT+DOWN ARROW to display the list, or right-click the cell, and then click “Pick from List” on the shortcut menu. n

Entering a formula 1 Click the cell in which you want to enter the formula. n 2 Type = (an equal sign). (If you click Edit Formula or Paste Function, Microsoft Excel inserts an equal sign for you. ) n 3 Enter the formula. n 4 Press ENTER. n

Tips You can enter the same formula into a range of cells by selecting the range first, typing the formula, and then pressing CTRL+ENTER. n You can also enter a formula into a range of cells by copying a formula from another cell. n

Fill Handle and Auto. Fill An alternative to copying and pasting cell contents is to use the Fill Handle. n Whenever a contiguous range of cells is selected, the range is framed by a dark border, and there is a small square in the lower right corner of this border. n This is the Fill Handle: n

Fill Handle

If you grab the Fill Handle of such a region and drag horizontally or vertically, Excel attempts to extrapolate the data into the neighboring cells in an intelligent way. n For example, if cell C 4 contains the value 5 and you select that cell and drag its Fill Handle downward through cell C 9, Excel copies the value 5 into cells C 5: C 9. This is just a simple copy operation: n

n This is faster than doing a copy and paste, and can be used to copy formulas as well.

However, if cell C 4 contains certain kinds of text that is recognizable as naming a month or day of the week, for example, dragging its Fill Handle down through cell C 9 yields a progression likely to be appropriate for that situation. n For example, if we enter Jan in cell C 4 and drag its Fill Handle downward, we get: n

This is called the Auto. Fill feature. n Another example occurs if we enter 5 in C 4 and 6 in C 5, then select C 4: C 5 and drag the resulting Fill Handle down through cell C 9. n In this case we get: n