Inserting and Deleting Rows and Columns
Inserting and deleting entire rows and columns in a worksheet is very similar to inserting new cells into a range - or deleting cells from the same range. In fact, you can actually insert or delete rows from within the Insert and Delete dialog boxes by selecting their Entire Row or Entire Column option button. There is, however, a big difference in the result:
- Inserting or deleting a new row affects all the columns in the worksheet, from column A over to column IV.
- Inserting or deleting a new column affects all the rows in the worksheet, from row 1 down to row 65536.
Because these insertions and deletions affect more than the cells in their immediate vicinity, you have to be sure that you're not about to adversely affect data in unseen rows and columns of the sheet before you undertake these operations. Note that, in this regard, inserting columns or rows can be almost as detrimental as deleting them if, by inserting them, you split apart existing data tables or lists whose data should always remain together.
One way to guard against inadvertently deleting existing data or splitting a single range is to use the Zoom feature: You can zoom out and check visually for intersecting groups of data in the hinterlands of the worksheet. The quick way to do this is to enter 10 into the Zoom box (on the Standard toolbar) and then press Enter. Of course, at a zoom setting of 10%, you can't read any of the data entered into the worksheet, but you can tell whether the column or row you intend to fiddle with intersects those data ranges (which you can identify as tiny specks of black).
Another way to check is to use the Ctrl key with the → or ↓ key to move the cell pointer from data range to data range across the column or row affected by the deletion of a column or row. Remember that pressing Ctrl at the same time as an arrow key when the cell pointer is in a blank cell jumps the cell pointer to the next occupied cell in the current row or column. That means if you press Ctrl+→ when the cell pointer is in row 79 and the pointer jumps to cell IV79 (the end of the worksheet in that row), you know that you won't mess up any data in that row by deleting the row (which would eliminate the data) or inserting a new row (which would shift the data up or down). So, too, if you press Ctrl+↓ when the cell pointer is in column C and the cell pointer jumps down to cell C65536, you're assured that no data is about to be purged - or shifted left or right - if you get rid of that column or insert a new column in its place.
After using one of these methods to ascertain that you won't harm existing data in the hinterland of the sheet, you're ready to insert or delete the row or column. Here are some points to remember:
- To insert or delete rows, you first select the row number(s) in the row header where the new rows are to be inserted or existing rows removed.
- When you insert rows, the existing data in the rows below shifts downward.
- When you delete rows, the existing data in the rows below shifts upward.
- To insert blank rows, choose Insert → Rows.
- To delete the selected rows, choose Edit → Delete instead.
The steps for inserting and deleting worksheet columns is almost identical for either procedure:
- Select the column letters in the column header.
- Choose Insert → Columns (to insert a column) or Edit → Delete (to delete a column).
Excel automatically moves any data that's currently in the columns to the right of those you selected:- When you insert new columns, the existing data moves to the right.
- When you delete existing columns, the existing data moves to the left.
