Inserting and Deleting Columns and Rows

INSERTING A COLUMN OR ROW
To insert a column or row, click in the frame area on a column letter or a row number to highlight the entire column or row. Then click on Insert on the menu bar. Depending on which is highlighted (row or column), you would select either Rows or Columns. The highlighted row would move down and a new blank row appears. The highlighted column would move to the right and a new column appears. Every time you go back into this Insert, Row/Column menu item, a new row or column would be added.

Exercise 2
Click on B in the frame area and then click on Insert, then Columns. Column B and the other columns to its right are moved to the right. Type in a unit (or department) name in column B for each of the "fundraisers."

As a further example, click on row 1 in the frame area so the entire row is highlighted. Click on Insert, then Rows for a total of three times, so you have three blank rows at the top of the spreadsheet.

Be sure to click on the "Save" button on the Standard toolbar to save a current copy of the PRACTICE.XLS file.

Check your work.

DELETING A COLUMN OR ROW
In a reverse process you can click on one or more column letters or one or more row numbers in the frame area, and click on Edit on the menu bar. Select the option of Delete and the highlighted column or row would be deleted. These columns or rows can be empty or contain information.

Exercise 3
Select the entire row at the top of the PRACTICE file and then click on Edit, then Delete to remove this row.

RECOVER WITH AN UNDO
Since it is so easy to delete information, remember that you can use the "Undo" button on the Standard toolbar to recover a deletion. Only one "undo" is stored, so only the last single action is recoverable.

HOW DO INSERTIONS AND DELETIONS AFFECT FORMULAS?
Whether or not insertions and deletions negatively affect formulas depends on where the insertion or deletion occurs and how the formulas were entered. When you inserted the column B for unit information, you probably noticed that the total column continued to give a correct value, because all the columns affecting the totals were moved. Click on the total column for Mary and look at the formula in F4. Previously it was =SUM(B2:D2) and now it reads =SUM(C4:E4) because two rows were inserted at the top and a new column at B.

Select column D by clicking on its frame area. Insert a column there, and look at the formulas in the Total column. If you had used the "AutoSum" button to create =SUM functions there, then the new formula for G4 is =SUM(C4:F4). Excel automatically adjusts the formula because the insertion was in the middle of a SUM range. However, insert a new column at G and move to H4. If you look at the formula, you will see that it still reads =SUM(C4:F4). Excel will not assume that a new column at the edge of a range is to be added into the range. If G were another color, you would have to edit the formula so it reads =SUM(C4:G4).

Exercise 4
Make these changes in the PRACTICE file:
  1. Have a new column D be Black and type in numbers in that column for each person. Use the "AutoSum" button to total that column in cell D10.

  2. Have a new column G be Purple and type in numbers in that column for each person. Again use the "AutoSum" button to total that column in cell G10.

  3. Highlight the totals in H and press the [DELETE] key to erase the numbers. Highlight from cells H4 through H10 and click on "AutoSum" to calculate new totals in the last column.

  4. Move to cell A1 and type in the heading of FUND RAISING PROJECT.

  5. Be sure to save this new version of PRACTICE.XLS.

The first three rows of the spreadsheet should now look like this:

In the same way that insertions can sometimes make calculations inaccurate, deleting certain columns or rows can also interrupt a formula and the formula may have to be adjusted or re-entered.

Learning Excel - Index