|[ LiB ]|
Much of the time in Excel, you'll be working with cells the little rectangular areas that make up a worksheet. The module tests your ability to perform a number of basic cell operations, including
Inserting and deleting data
Moving data around
Using the search and replace tools
The most basic task of all is to put data into your spreadsheet so that you can work with it.
Enter a number, date, text in a cell.
To insert data in a cell, just click the mouse in the cell (or use the keyboard arrow keys to move to the cell). Then type the data that you want the cell to contain. As you enter the data, you see it appear in both the cell you've selected and in the formula bar at the top of the screen. When you're done, press Enter or one of the arrow keys to confirm the data entry.
To enter text in a cell, just type the text that you'd like Excel to display. Excel might reformat the data that you enter. For example, if you enter 2345678.7878787 , only the rounded value of 2345678 shows in the cell. If you enter June 3, 2003 , Excel will display this as 03-Jun-03 . The data you entered is all there; if you'd like, you can right-click on the cell and select Format Cells to change the way that it's displayed.
To have Excel treat what you've entered as text to display exactly as you type it, type a single quote ( ' ) before the value. For example, if you type 'June 3, 2003 , Excel will display June 3, 2003 .
Sometimes you need to select one or more cells on a worksheet to work with them. For example, later in this chapter, you learn how to change the color of all selected cells. Excel offers many ways to select cells.
Select a cell, range of adjacent cells, range of nonadjacent cells, entire worksheet.
To begin, you can select a single cell by clicking the mouse in that cell. You can also select a cell by moving around with the arrow keys. The selected cell has a heavy black outline.
To select more than one cell, hold down the mouse button and drag the mouse. This action selects all the cells in the rectangular area that you drag. Alternatively, you hold down Shift and press the arrow keys to enlarge the current selection with the keyboard.
To select ranges of nonadjacent cells, hold down the Ctrl key and then click or drag the mouse in all the cells that you want to select.
To select the entire worksheet, click in the upper-left corner of the worksheet (to the left of the A column header, just above the 1 row header), or click anywhere in the worksheet and then press Ctrl+A.
Excel displays a single selected cell by drawing a heavy border around it. If you select multiple adjacent cells, the selection will be displayed with a heavy border and a gray background, as shown in Figure 5.7. If you select multiple nonadjacent cells, each selected cell will be displayed with a gray background but without a heavy border.
Select a row, range of adjacent rows, range of nonadjacent rows.
To select an entire row of cells, click in the row header to the left of the row. For example, click in the gray area labeled 3 to select the entire third row of the worksheet. Alternatively, you can select the entire row containing the current cell by pressing Shift+space.
To select a range of adjacent rows, hold down the mouse button and drag it down through all the row headers that you want to select. Alternatively, click in the first row header, and then Shift+click in the last row header of the area that you want to select.
To select a range of nonadjacent rows, Ctrl+click in each row header for the rows that you want to select.
Select a column, range of adjacent columns, range of nonadjacent columns.
To select an entire column of cells, click in the column header at the top of the column. For example, click in the gray area labeled C to select the entire third column of the worksheet. Alternatively, you can select the entire column containing the current cell by pressing Ctrl+space.
To select a range of adjacent columns, hold down the mouse button and drag it across all the column headers that you want to select. Alternatively, click in the first column header, and then Shift+click in the last column header of the area that you want to select.
To select a range of nonadjacent columns, Ctrl+Click in each column row header for the columns that you want to select.
In addition to selecting them, you can also insert, delete, and modify rows and columns in an Excel worksheet.
Insert rows, columns in a worksheet.
To insert a new row in a worksheet, right-click the row header for the row that you want to end up directly below the new row. Then select Insert Row from the shortcut menu. Alternatively, select Rows from the Insert menu to insert a row directly above the currently selected cell.
To insert a new column in a worksheet, right-click the column header for the column that that should end up directly to the right of the new column, as shown in Figure 5.8. Then select Insert Column from the shortcut menu. Alternatively, select Columns from the Insert menu to insert a column directly to the left of the currently selected cell.
Delete rows, columns in a worksheet.
Of course, Excel also offers ways to delete rows and columns that you don't need.
To delete a row, right-click on the row header and select Delete. Alternatively, click anywhere in the row and select Delete from the Edit menu. This action opens the Delete dialog box. Select the Entire Row option and click OK.
To delete a column, right-click on the column header and select Delete. Alternatively, click anywhere in the column and select Delete from the Edit menu. This action opens the Delete dialog box. Select the Entire Column option and click OK.
You should know your way around a spreadsheet. The exam may ask you to format a cell or a range. In addition, you should be able to insert a new sheet and delete and insert columns and rows.
Modify column widths, row heights.
You can also change the size of any row or column.
To adjust the height of a row, place your mouse pointer at the bottom edge of the row header for the row. The cursor changes into a double-arrow cursor (a line with arrows pointing in both directions). Hold down the mouse button and drag the splitter up or down to change the row height. Alternatively, you can select the row and then select Format, Row, Height to specify a new height for the row.
To adjust the width of a column, place your mouse pointer at the right edge of the column header for the column. The cursor will change into a splitter cursor. Hold down the mouse button and drag the splitter left or right to change the column width. Alternatively, you can select the column and then select Format, Column, Width to specify a new width for the column.
From time to time, you need to change the data in a cell. Perhaps your boss recalculated some piece of information, such as the annual sales projection, or perhaps you simply made a mistake. In any case, Excel makes it easy to edit existing data.
Insert additional cell content, replace existing cell content.
To edit the data in a cell, double-click in the cell. This action turns the cell itself into a live editing area. It also copies the contents of the cell to the formula bar. You can either edit the data directly in the cell or in the formula bar; the two stay synchronized. When you're finished, press Tab or Enter or click the green check mark next to the formula bar.
To replace the data in the cell, just click in the cell and start typing. As soon as you type a single character, the existing data is removed.
Use the Undo, Redo command.
Now that you've seen how easy it is to replace data, you might be worried: what happens if you accidentally type a character in the wrong cell and erase some critical data? Don't worry! Excel supports nearly unlimited undo and redo capabilities.
To undo the most recent action, reversing all its effects, select Undo from the Edit menu, click the Undo button on the Standard toolbar, or press Ctrl+Z. If you change your mind, you can then redo the action by selecting Redo from the Edit menu, clicking the Redo button on the Standard toolbar, or pressing Ctrl+Y.
You'll notice drop-down arrows next to the Undo and Redo toolbar buttons . Clicking on one of these arrows displays an entire list of recent actions that you can undo or redo, as shown in Figure 5.9. Click on any action in the list to undo or redo all the actions to that point.
Excel also offers a variety of ways to duplicate, move, or delete data from cells.
Duplicate the contents of a cell, cell range within a worksheet, between worksheets, between open spreadsheets.
To duplicate the contents of a cell or a cell range, you can use Excel's copy and paste tools. Follow these steps:
Use the autofill tool/copy handle tool to copy, increment data entries.
Excel offers several ways to quickly fill in repetitive information. For example, suppose you need the number 5 to appear in every cell in a row from column 1 to column 20. You can use AutoFill to make this happen quickly. Type the number 5 in column 1. Then select the entire range, from column 1 to column 20. Select Edit, Fill, Right to copy the number to every cell in the range. You can also use Edit, Fill, Down to copy data to other cells in the same column.
Alternatively, you can use the copy handle to perform the same operation with the mouse. Fill in the first cell, and then hover the mouse over the lower-right corner of the cell until you see a small plus sign. This plus sign is the copy handle. Click the mouse and drag to the right or down to duplicate the contents of the cell.
The copy handle method also knows how to generate common series of entries. For example, if you type Jan into a cell and then drag the copy handle from that cell, succeeding cells are filled in with Feb , Mar , and so on.
Move the contents of a cell, cell range within a worksheet, between worksheets, between open spreadsheets.
To duplicate the contents of a cell or a cell range, you can use Excel's cut and paste tools. Follow these steps:
Delete cell contents.
To delete the contents of a cell or a range of cells, first select the cell or cells. Then press the Delete key on your keyboard, right-click the area and select Delete, or select Delete from the Edit menu. If you press the Delete key, the contents of the selected cells are replaced with empty cells, and no other data moves. If you use one of the menu items, you are prompted as to whether to shift data up or left to fill in the deleted cell.
Excel, like any other major application, lets you search for and replace data in a worksheet.
Use the search command for specific content in a worksheet.
To search for data in a worksheet, select Find from the Edit menu or press Ctrl+F. This will open the Find dialog box, shown in Figure 5.11. Enter the text that you want to search for, and specify whether you want to search in formulas (the calculations that determine the displayed text; you'll learn more about formulas later in this chapter) or values (the actual displayed text). Click the Find Next button, and continue clicking the button to find additional instances of the data.
You can also use check boxes in the Find dialog box to specify that the text must match what you typed exactly (including capitalization) and whether you want to only match entire cells instead of text anywhere within a cell.
Use the replace command for specific content in a worksheet.
To replace data, select Replace from the Edit menu, or press Ctrl+H. The Replace dialog box, shown in Figure 5.12, is very similar to the Find dialog box. However, it includes an additional control where you can select a replacement value. Enter the text that you want to find and the text that you want to replace it with. Click the Find Next button to highlight the first instance of that text in your worksheet. Now click Replace to replace that instance and search for the next one, Replace All to replace all remaining instances of the data in the worksheet, or Close to cancel the process.
Few of us are so lucky as to always enter data in the exact order we need to view it. This next section offers a look at sorting data.
Sort a cell range by one criterion in ascending, descending numeric order, ascending , descending alphabetic order.
Excel makes it easy to sort your data. For example, suppose you have a worksheet containing customer names and addresses, and you'd like to have them sorted by zip code. Just follow these steps:
Excel can sort either alphabetic or numeric values using the same procedure. To sort in descending order, click the Descending option button before performing the sort. Or select the data you want to sort and click the Sort Ascending or Sort Descending buttons on the toolbar.
|[ LiB ]|