Chapter 2: Entering and Editing Worksheet Data

2.1. Exploring Data Types

A cell can hold any of three basic types of data:

• A numeric value
• Text
• A formula

Understanding numeric values

Numeric values represent a quantity of some type: sales amounts, number of employees, atomic weights, test scores, and so on. Values also can be dates (such as Feb-26-2013) or times (such as 3:24 a.m.).

Excel’s Numeric Limitations

How large can a number be? And how accurate are large numbers?

Excel’s numbers are precise up to 15 digits. For example, if you enter a large value, such as 123,456,789,123,456,789 (18 digits), Excel actually stores it with only 15 digits of precision. This 18-digit number displays as 123,456,789,123,456,000. 

One situation in which the 15-digit accuracy can cause a problem is when entering national ID numbers.

National ID numbers are 16 digits, but Excel can handle only 15 digits, so it substitutes a zero for the last national ID digit. The solution? Enter the national ID numbers as text. 

The easiest way is to preformat the cell as Text (choose Home ➪ Number and choose Text from the Number Format drop-down list). Or you can precede the credit card number with an apostrophe. Either method prevents Excel from interpreting the entry as a number.

Understanding text entries

Text can serve as data (for example, a list of employee names), labels for values, headings for columns, or instructions about the worksheet. Text is often used to clarify what the values in a worksheet mean or where the numbers came from.

Text that begins with a number is still considered text. For example, if you type 12 Employees into a cell, Excel considers the entry to be text rather than a numeric value. Consequently, you can’t use this cell for numeric calculations. If you need to indicate that the number 12 refers to employees, enter 12 into a cell and then type Employees into the cell to the right.

Understanding formulas

Formulas are what make a spreadsheet a spreadsheet. Excel enables you to enter flexible formulas that use the values (or even text) in cells to calculate a result. When you enter a formula into a cell, the formula’s result appears in the cell. If you change any of the cells used by a formula, the formula recalculates and shows the new result.

2.2. Entering Text and Values into Your Worksheets

• To enter a numeric value into a cell, move the cell pointer to the appropriate cell, type the value, and then press Enter or one of the navigation keys. The value is displayed in the cell and also appears in the Formula bar when the cell is selected.
• Entering text into a cell is just as easy as entering a value: Activate the cell, type the text, and then press Enter or a navigation key. A cell can contain a maximum of about 32,000 characters.

 What happens when you enter text that’s longer than its column’s current width?

If the cells to the immediate right are blank, Excel displays the text in its entirety, appearing to spill the entry into adjacent cells. If an adjacent cell isn’t blank, Excel displays as much of the text as possible. (The full text is contained in the cell; it’s just not displayed.) If you need to display a long text string in a cell that’s adjacent to a nonblank cell, you have a few choices: 

• Edit your text to make it shorter.
• Increase the width of the column (drag the border in the column letter display).
• Use a smaller font.
• Wrap the text within the cell so that it occupies more than one line. Choose Home ➪ Alignment ➪ Wrap Text to toggle wrapping on and off for the selected cell or range.

2.3. Entering Dates and Times into Your Worksheets

Dates and times are values that are formatted so that they appear as dates or times. 

Entering date values

Excel handles dates by using a serial number system. The earliest date that Excel understands is January 1, 1900. This date has a serial number of 1.

January 2, 1900, has a serial number of 2, and so on. This system makes it easy to deal with dates in formulas. For example, you can enter a formula to calculate the number of days between two dates.

Entering time values

When you work with times, you extend Excel’s date serial number system to include decimals. In other words, Excel works with times by using fractional days. For example, the date serial number for June 1, 2013, is 41426. Noon on June 1, 2013 (halfway through the day), is represented internally as 41426.5 because the time fraction is added to the date serial number to get the full date/time serial number.

2.4. Modifying Cell Contents

After you enter a value or text into a cell, you can modify it in several ways: 

• Delete the cell’s contents.
• Replace the cell’s contents with something else.
• Edit the cell’s contents.

Deleting the contents of a cell

To delete the contents of a cell, just click the cell and press the Delete key. To delete more than one cell, select all the cells that you want to delete and then press Delete. 

Replacing the contents of a cell

To replace the contents of a cell with something else, just activate the cell and type your new entry, which replaces the previous contents. 

Editing the contents of a cell

If the cell contains only a few characters, replacing its contents by typing new data usually is easiest. 

When you want to edit the contents of a cell, you can use one of the following ways to enter cell-edit mode: 

• Double-click the cell to edit the cell contents directly in the cell.
• Select the cell and press F2 to edit the cell contents directly in the cell.
• Select the cell that you want to edit and then click inside the Formula bar to edit the cell contents in the Formula bar.

2.5. Learning some handy data-entry techniques

Using Ctrl+Enter to place information into multiple cells simultaneously

If you need to enter the same data into multiple cells, Excel offers a handy shortcut. Select all the cells that you want to contain the data, enter the value, text, or formula, and then press Ctrl+Enter. The same information is inserted into each cell in the selection.

Entering decimal points automatically

If you need to enter lots of numbers with a fixed number of decimal places, Excel has a useful tool that works like some old adding machines. Access the Excel Options dialog box and click the Advanced tab. Select the Automatically Insert a Decimal Point check box and make sure that the Places box is set for the correct number of decimal places for the data you need to enter.

When this option is set, Excel supplies the decimal points for you automatically. For example, if you specify two decimal places, entering 12345 into a cell is interpreted as 123.45. 

Using Auto Fill to enter a series of values

The Excel Auto Fill feature makes inserting a series of values or text items in a range of cells easy. It uses the Auto Fill handle (the small box at the lower right of the active cell). You can drag the Auto Fill handle to copy the cell or automatically complete a series.

Using AutoComplete to automate data entry

The Excel AutoComplete feature makes entering the same text into multiple cells easy. With AutoComplete, you type the first few letters of a text entry into a cell, and Excel automatically completes the entry based on other entries that you already made in the column. 

Forcing text to appear on a new line within a cell

If you have lengthy text in a cell, you can force Excel to display it in multiple lines within the cell: Press Alt+Enter to start a new line in a cell. When you add a line break, Excel automatically changes the cell’s format to Wrap Text.

Entering numbers with fractions

To enter a fractional value into a cell, leave a space between the whole number and the fraction. For example, to enter 6 7/8, enter 6 7/8 and then press Enter. When you select the cell, 6.875 appears in the Formula bar, and the cell entry appears as a fraction. If you have a fraction only (for example, 1/8), you must enter a zero first, like this — 0 1/8 

2.6. Applying Number Formatting

Number formatting refers to the process of changing the appearance of values contained in cells. 

As an exercise, complete the first column "unformatted" with given values and copy these values in "formatted" column. Then select each cell in "formatted" column and set it as indicated type in "Type" column.

The Number Format drop-down list contains several common number formats. Additional options include an Accounting Number Format drop-down list (to select a currency format), a Percent Style button, and a Comma Style button.