Modifying Worksheets

After you put up the signposts that make your data easy to find, you can take other steps to make the data in your workbooks easier to work with. For instance, you can change the width of a column or the height of a row in a worksheet by dragging the column or row’s border to the desired position. Increasing a column’s width or a row’s height increases the space between cell contents, making it easier to select a cell’s data without inadvertently selecting data from other cells as well.

Tip 

You can apply the same change to more than one row or column by selecting the rows or columns you want to change and then dragging the border of one of the selected rows or columns to the desired location. When you release the mouse button, all the selected rows or columns change to the new height or width.

Modifying column width and row height can make a workbook’s contents easier to work with, but you can also insert a row or column between the edge of a worksheet and the cells that contain the data to accomplish this. Adding space between the edge of a worksheet and cells, or perhaps between a label and the data to which it refers, makes the workbook’s contents less crowded and easier to work with. You insert rows by clicking a cell and clicking the Home tab. Then, in the Cells group, click the Insert button’s down arrow and click Insert Sheet Rows. Excel 2007 inserts a row above the row that contains the active cell. You insert a column in much the same way by choosing Insert Sheet Columns from the Insert button’s drop-down list. When you do this, Excel 2007 inserts a column to the left of the active cell.

When you insert a row, column, or cell in a worksheet with existing formatting, the Insert Options button appears. Clicking the Insert Options button displays a list of choices you can make about how the inserted row or column should be formatted. The following table summarizes your options.

Open table as spreadsheet

Option	Action
Format Same as Above	Applies the format of the row above the inserted row to the new row.
Format Same as Below	Applies the format of the row below the inserted row to the new row.
Format Same as Left	Applies the format of the column to the left of the inserted column to the new column.
Format Same as Right	Applies the format of the column to the right of the inserted column to the new column.
Clear Formatting	Applies the default format to the new row or column.

If you want to delete a row or column, right-click the row or column head and then, from the shortcut menu that appears, click Delete. You can temporarily hide a number of rows or columns by selecting those rows or columns and then, on the Home tab, in the Cells group, clicking the Format button, pointing to Hide & Unhide, and then clicking either Hide Rows or Hide Columns. The rows or columns you selected disappear, but they aren’t gone for good, as they would be if you’d used Delete. Instead, they have just been removed from the display until you call them back. To return the hidden rows to the display, on the Home tab, in the Cells group, click the Format button, point to Hide & Unhide, and then click either Unhide Rows or Unhide Columns.

Likewise, you can insert individual cells into a worksheet. To insert a cell, click the cell that is currently in the position where you want the new cell to appear. On the Home tab, in the Cells group, click the Insert button down arrow and then click Insert Cells to display the Insert dialog box. In the Insert dialog box, you can choose whether to shift the cells surrounding the inserted cell down (if your data is arranged as a column) or to the right (if your data is arranged as a row). When you click OK, the new cell appears, and the contents of affected cells shift down or to the right, as appropriate. In a similar vein, if you want to delete a block of cells, select the cells, and on the Home tab of the user interface, in the Cells group, click the Delete button down arrow and then click Delete Cells to display the Delete dialog box-complete with option buttons that enable you to choose how to shift the position of the cells around the deleted cells.

Tip 

The Insert dialog box also includes option buttons you can select to insert a new row or column; the Delete dialog box has similar buttons that enable you to delete an entire row or column.

If you want to move the data in a group of cells to another location in your worksheet, select the cells you want to move and position the mouse pointer on the selection’s border. When the mouse pointer changes to a four-way arrow, you can drag the selected cells to the desired location on the worksheet. If the destination cells contain data, Excel 2007 displays a dialog box asking if you want to overwrite the destination cells’ contents. If you want to replace the existing values, click the OK button. If you don’t want to overwrite the existing values, click the Cancel button and insert the required number of cells to accommodate the data you want to move.

In this exercise, you will insert a column and row into a worksheet, specify insert options, hide a column, insert a cell into a worksheet, delete a cell from a worksheet, and move a group of cells within the worksheet.

Use the Route Volume workbook from the Creating folder under SBS_Office2007.

Open the Route Volume workbook.

1. On the May 12 worksheet, select cell A1.

2. On the Home tab, in the Cells group, click the Insert button down arrow and then click Insert Sheet Columns.

A new column A appears.

3. On the Home tab, in the Cells group, click the Insert button down arrow and then click Insert Sheet Rows.

A new row 1 appears.

4. Click the Insert Options button and click Clear Formatting.

Excel 2007 removes the formatting from the new row 1.

5. Right-click the column header of column E and click Hide.

Column E disappears.

6. On the tab bar, click the May 13 sheet tab.

The worksheet named May 13 appears.

7. Click cell B6.

8. On the Home tab, in the Cells group, click the Delete button down arrow and then click Delete Cells.

The Delete dialog box appears.

9. If necessary, select the Shift cells up option button and then click OK.

The Delete dialog box disappears and Excel 2007 deletes cell B6, moving the cells below it up to fill in the gap.

10. Click cell C6.

11. On the Home tab, in the Cells group, click the Insert button down arrow and then click Insert Cells.

The Insert dialog box appears.

12. If necessary, select the Shift cells down option button and then click OK.

The Insert dialog box disappears, and Excel 2007 creates a new cell C6, moving cells C6:C11 down to accommodate the inserted cell.

13. In cell C6, type 4499 and press .

14. Select cells E13:F13.

15. Point to the border of the selected cells. When your mouse pointer changes to a four-pointed arrow, drag the selected cells to cells B13:C13.

The dragged cells replace cells C13:D13.

Close the Route Volume workbook.