Microsoft Excel XP Tutorial

Section Learning Goals In this section, you will learn: The differences between workbooks and worksheets Entering text and numbers Working with ranges of cells Inserting cells Deleting cells Moving and copying data

Understanding Workbooks and Worksheets

A workbook is the Microsoft Excel file that stores your data. A workbook can contain many worksheets, which you use to enter and work with your data. You can enter and display data in multiple worksheets in a workbook, and perform calculations using data found across all the different worksheets.

Worksheets are composed of cells, active areas in which you input data or formulas for calculation. Cells are aligned along columns, labeled with the letters A through IV, and rows, labeled with the numbers 1 through 65536.

When a cell is selected, its location appears in the name box, located to the left of the formula bar.

In Excel, this location is called a reference, and is used to point to values you want to use in a formula. References let you calculate data contained in different parts of your workbook, or in different workbooks altogether.

References are indicated using the cell’s column and row headings. Cell B2, shown in the name box above, refers to the cell in column B, row 2.

Entering Text and Numbers

To enter data into a cell, you must first select, or activate, the cell. To do this, you click the cell with your mouse, or move the cursor into the cell using the arrow keys on your keyboard. Once a cell is selected, type your data and press Enter (or Tab, if you want to move to the adjacent cell).

Note:

Among Excel’s options is the ability to turn on and off editing in cells. When this option is turned on, you can double-click a cell and type directly into it. You can also double-click a cell and then select all or part of the data contained in the cell-if you want to format it, for example.

When editing in cells is turned off, you must use the formula bar to enter and edit data: Select the cell, type the data in the formula bar (or highlight the data in the formula bar, to edit it), and then press Enter.

If you’re having trouble editing directly in a cell, or wish to turn this option on or off:

1. Select Options from the Tools menu.

The Options dialog opens.

1. Select the Edit tab.

1. Under Settings, check or uncheck the box labeled Edit directly in cell, as appropriate.

Working with Ranges

In Excel, you can work with multiple cells at once (called a range). To select a range, click the first cell and, holding down the mouse button, drag the mouse to the last cell. All the cells in the range you defined are selected.

Alternatively, you can select the first cell, hold down the Shift key, and then select the last cell in the range; this selects adjacent cells, as shown in the illustration above. If, instead, you want to select several cells that aren’t adjacent, hold down the Ctrl key on your keyboard while you click each cell you want to select.

To quickly select an entire row or column, click the row or column heading, respectively. You can select multiple rows or columns by clicking on the first row or column heading in the range, holding down the mouse button, and dragging to the last row or column heading in the range.

Tip:

To select the entire contents of a worksheet, click the box in the upper left corner of the worksheet, where the first row and first column intersect:

Working with Ranges (Continued)

Ranges in Excel are used in
a variety of ways. You can select a range to apply the same formatting to all the cells (such as font, size, color, borders, and shading), or you can enter the same data into each of the cells in the range using the Ctrl + Enter shortcut:

1. Select the range.

1. Type the data.

1. Press Ctrl + Enter.

Ranges are also used in performing calculations. When you enter a formula in the formula bar, you designate a range by typing the first cell in the range, followed by a colon (:), followed by the last cell in the range. For example:

A1:F20

The range above includes cells A1 through A20, B1 through B20, C1 through C20, and so on through cell F20.

Inserting Cells

Use the Insert command to insert a row, a column, or a blank cell into a worksheet.

To insert a row or column:

1. Select the row below, or the column to the right, of the location where you want the new row or column inserted.
2. Select Rows or Columns from the Insert menu, as appropriate. Or, right-click the row or column and select Insert from the shortcut menu.

A new, blank row or column is inserted. Note that it assumes the size and formatting of the adjacent row or column.

To insert a blank cell:

1. Select the cell adjacent to the location where you want to insert the new cell.
2. Select Cells from the Insert menu. Or, right-click the current cell and select Insert from the shortcut menu.

The Insert dialog opens.

1. Choose how you want to shift the existing cells to accommodate the new one.
2. Click OK.

Deleting Cells

To delete cells:

1. Select the cell, the range, or the row or column you want to delete.
2. From the Edit menu, select Delete. Or, right-click the selection and select Delete from the shortcut menu.

Moving and Copying Data

To move data from one cell to another:

1. Select the cell whose contents you want to move.
2. Point to the edge of the cell.

3. When the mouse pointer becomes an arrow, click and drag the cell to the new location.

4. Release the mouse button.

You can drag and drop entire ranges of cells:

You can also use Excel’s drag and drop functionality to copy cells; simply hold down the Ctrl key on your keyboard as you drag. Alternatively, you can use the Copy and Paste functions:

1. Select the cell whose contents you want to copy.

2. From the Edit menu, select Copy. Or, right-click the cell and select Copy from the shortcut menu. (You can also use the Ctrl+c keyboard shortcut.)

A blinking dashed border appears around the cell.

3. Select the cell where you want the data to be copied.

4. From the Edit menu, select Paste. Or, right-click the new cell and select Paste from the shortcut menu. (You can also use the Ctrl+v keyboard shortcut.)

5. The data is pasted into the new cell.

You can continue to paste the same data into as many cells as you like.

Tip:

If you’re only copying the data to one cell, you can press Enter as soon as you select the new cell. Excel will automatically paste the data.

6. To finish pasting and remove the dashed outline from the original cell, press Enter.

Tip:

To quickly copy a data to an adjacent cell:

1. Select the cell containing the data.

The fill handle is displayed as a small black square in the bottom right corner of the cell outline.

1. Click on the fill handle and, holding down your mouse button, drag to the adjacent cell. When that cell is highlighted, release the mouse button.

The data is copied to the adjacent cell.

This is useful when you need to fill many cells with the same data, since you can drag the fill handle across an entire range:

The data is copied to all the cells in the selected range.

In the illustration above, the cell being copied contained a formula that adds the values from the cells above. When the cell was copied, the formula was pasted across a range of cells-not the value displayed in the original cell.

Excel adjusted the cell references in the formula for each new column, so that the
values entered in the cells above were totaled. When you copy a formula, Excel adjusts the cell references by the number of rows and columns the formula was moved. So, for example, you can enter one formula that totals the values in a column, and copy the formula to many different columns without having to change the cell references for each column-Excel does it automatically. This is explained in more detail in Absolute and Relative Cell References.

Note:

Excel does not change the references when you move a formula.

Section Review In this section, you learned: The differences between workbooks and worksheets Entering text and numbers Working with ranges of cells Inserting cells Deleting cells Moving and copying data