Editing Spreadsheet Data

What does editing look like in your business? In one department, editing might involve passing the sales projection worksheet among team members for their changes and suggestions. In another department, editing might involve double-checking formulas, switching ranges, and correcting misspellings. This section shows you quickly how to edit your worksheets to ensure their accuracy.

Aha! ”Commenting on Worksheets

If you re the lucky person who is reviewing another s work, you will find that the Comment feature comes in handy. On your desktop system or Tablet PC, you can add your thoughts and suggestions in little pop-up boxes without adding information in the worksheet itself. To add a comment, click the cell to which you want to add the comment and choose Comment from the Insert menu. Type your message in the little message box that appears; then click outside the cell .

 

Editing Techniques

Depending on what you need to correct in your worksheet, you might use any or all of the following editing techniques to check and correct things:

• Correct typos and incorrect data The easiest way to make an editing change in an Excel worksheet is to click the cell you need to change, type the correct information, and press Enter.

  Note  

  If you want to edit quickly in the cell without typing in the Formula bar, double-click the cell (or press F2 to turn on Edit mode), delete the errant data, and type the correct information; then click outside the cell or press Enter.

• Check spelling by pressing F7 This starts the spelling checker, which displays any unrecognized words so that you can correct them or add them to the spelling dictionary.

• Check your formulas with the Error Checker Excel includes a feature you can use to double-check your math on the worksheet. Choose Error Checker from the Tools menu to start the process.

Aha! ”Setting Error Checking Options

To display the settings the Error Checker uses by default, choose Options from the Tools menu and click the Error Checking tab. Here you can specify whether you want Excel to check errors in the background while you work, or wait for you to choose the command from the Tools menu. You can also control which errors Excel looks for by default.

 

Copying and Pasting Cells and Ranges

At times you may need to make bigger edits ”the kind that require highlighting a range of cells and then copying, cutting, pasting, or dragging them to another location, perhaps on a new worksheet. You ll use block copy and paste operations to do that. Here are the steps:

1. Select the area you want to move to the new sheet. If you have previously named the range, choose the range name in the Name box to select it. If you have not saved the area as a range, drag to select the area.

2. Copy the cells by pressing Ctrl+C. This places a copy of the selected range on the Office Clipboard. A dotted outline (often called marching ants ) appears around the perimeter of the range.

3. Click the Sheet2 worksheet tab. Click in the cell where you want the data to be placed, press Ctrl+V to paste the cells, then press Enter.

Notice that the copied data brings with it any formats you previously applied, except the cell widths on the new sheet, which appear at their default setting. If you want to retain the column width and row height settings when you paste cells in your worksheet, click the Paste Options button that appears beneath and to the right of the pasted cells (see Figure 8-1). Click the Keep Source Column Widths option; Excel then preserves the column widths with the pasted information.

Figure 8-1: Paste Options enable you to preserve the format of the cells you paste.

Lingo  

The source column is the column from which you ve copied data; the destination is the new area where the copied cells will be placed.

Caution  

Be sure that you are pasting data into a clear area, because the incoming information will overwrite the data in existing cells without any warning from Excel.

Clearing Data

Once you ve placed the copied data into the second worksheet, you can clear the information from the first worksheet. To do that, follow these steps:

1. Click the Sheet1 worksheet tab. The information you previously highlighted is still selected.

2. Choose Clear from the Edit menu and then select All. The cell contents are cleared.

There s another way to handle this copy-paste-and-clear operation: You can select the information on the first worksheet, choose Cut from the Edit menu (or press Ctrl+X), move to the new worksheet, click the area where you want the data to go, and press Ctrl+V (or Enter) to paste. This removes the data and places it in the new worksheet in one process; you don t need to go back and clear the data later. Note that when you use Cut to remove data, the format and cell contents are cleared, and the blank cells are preserved in the worksheet.

Clearing vs. Deleting Data

At first glance, it may not be obvious why we need two commands that seem to remove data from a worksheet. However, there s a subtle difference between clearing and deleting data.

When you clear data by using the Clear command in the Edit menu, you are telling Excel to wipe the cell clean of all contents, formats, and comments. The cells remain intact, but now they are simply empty.

When you use the Delete command from the Edit menu, you are actually removing the selected cells from the worksheet. The other cells in the worksheet are moved to close that void, which means that the cell references in your formulas are automatically updated.

When you highlight a range and then press your Delete key, or use the Backspace key to delete data, you are removing the data only; the format of the cell remains in place. For that reason, when you want to remove all data and formats within a given range, and intend to plug other data into the blank space you ve created, use the Clear command. When you want to condense the amount of space on your worksheet and remove the unnecessary rows or columns , use Delete.

 

Working with Rows and Columns

Some of the rearranging you need to do might involve changing the width or height of columns and rows. By default, Excel s columns are set wide enough to display 8.43 characters (yes, it s an odd number), and the rows are set to 12.75 points, or slightly more than 1/6 of an inch in height, since 72 points equals 1 inch.

When you want to make a change to a column or row, you need to begin by selecting the one you want to work with. To select an entire column, click the column label. To select an entire row, click the row label. Then use the following steps to make the changes you want to make:

• To change the width of a column, choose Column from the Format menu and select Width. In the Column Width dialog box, type a new width for the column and click OK.

• To set the width of a column or the height of a row to automatically accommodate the largest data entry, use AutoFit. Choose Column from the Format menu and then select AutoFit Selection to resize a column automatically. Choose Row from the Format menu and click AutoFit to resize a row.

• To change the height of a row, select Row from the Format menu and choose Height. In the Row Height dialog box, type a new value in the text box and click OK.

Aha! ”Resize Columns and Rows Quickly

You also can resize columns and rows by selecting the divider line in the column or row label area and dragging the divider in the direction you want. Dragging a divider line down in the row labels increases the row height; dragging a divider line to the right in the column labels widens the column. All other rows and columns are moved accordingly to allow for the size change.

 

• To insert columns and rows, select the column or row beside which you want to add another column or row. Then, depending on what you ve selected, choose Rows (or Columns) from the Insert menu. If you add a row, the new row is added above the selected row; if you add a column, the new column is added to the left of the selected column.

Aha! ”Inserting Multiple Columns and Rows

If you want to insert multiple rows or columns, highlight the number of columns or rows you want to add before you choose Rows or Columns from the Insert menu. Excel will add as many rows or columns as you have highlighted.

 

• To hide columns and rows, you can again choose Row or Column from the Format menu and choose Hide (or, alternatively, Unhide). But there s also another way: right-click the column or row you want to hide. A context menu of row or column options appears, and you can choose Hide to hide the selected column or row.

Note ”Unhiding Columns

How do you select a hidden column to Unhide it? If you have hidden column C, for example, select columns B and D, and then right-click the column label area. Choose Unhide from the context menu, and column C reappears.