Section 3.3. Adding and Moving Columns or Rows

3.3. Adding and Moving Columns or Rows

The cut-and-paste and copy-and-paste operations let you move data from one cell (or group of cells ) to another. But what happens if you want to make some major changes to your worksheet itself? For example, imagine you have a spreadsheet with 10 filled columns (A to J) and you decide you want to add a new column between columns C and D. You could cut all the columns from D to J, and then paste them starting at E. That would solve the problem, and leave the C column free for your new data. But the actual task of selecting these columns can be a little awkward , and it only becomes more difficult as your spreadsheet grows in size .

A much easier option is to use two dedicated Excel commands designed for inserting new columns and rows into an existing spreadsheet. If you use these features, you won't need to disturb your existing cells at all.

3.3.1. Inserting Columns

To insert a new column, follow these steps:

  1. Select the column immediately to the right of where you want to place the new column.

    That means that if you want to insert a new, blank column between columns A and B, start by selecting the existing column B. Remember, you select a column by clicking the column header.

  2. Choose Insert Columns.

    Excel inserts a new column, and automatically moves all the columns to the right of column A (so column B becomes column C, column C becomes column D, and so on).

3.3.2. Inserting Rows

Inserting rows is just as easy as inserting new columns. Just follow these steps:

  1. Select the row that's immediately below where you want to place the new row.

    That means that if you want to insert a new, blank row between rows 6 and 7, start by selecting the existing row 7. Remember, you select a row by clicking the row number header.

  2. Choose Insert Rows.

    Excel inserts a new row, and all the rows beneath it are automatically moved down one row.


Note: In the unlikely event that you have data at the extreme right edge of the spreadsheet, in column IV, Excel won't let you insert a new column anywhere in the spreadsheet because the data would be pushed off into the region Beyond The Spreadsheet's Edges. Similarly, if you have data in the very last row (row 65536), Excel won't let you insert more rows. If you do have data in either of these spots and try to insert a new column or row, Excel displays a warning message.

3.3.3. Inserting Copied or Cut Cells

Usually, inserting entirely new rows and columns is the most straightforward way to change the structure of your spreadsheet. You can then cut and paste new information into the blank rows or columns. However, in some cases you might simply want to insert cells into an existing row or column.

To do so, begin by copying or cutting a cell or group of cells, and then select the spot you want to paste into. Next, choose Insert Copied Cells from the menu (or Insert Cut Cells if you are performing a cut instead of a copy operation). Unlike the cut-and-paste feature, when you insert cells, you won't overwrite the existing data. Instead, Excel asks you whether the existing cells should be shifted down or to the right to make way for the new cells (as shown in Figure 3-9).

Figure 3-9. Top : When you insert copied cells, Excel asks whether it should move the existing cells down or to the right. Middle: Here, two price cells ($43.99 and $3.00) were copied and pasted before the picture was taken, and the existing price cells were shifted down to accommodate the new entries. But the prices now no longer line up with the appropriate item names , which is probably not what you want.
Bottom : It makes much more sense to use the Insert Copied (or Cut) feature when you're copying a whole row's worth of data. Here's a worksheet where two new rows have been pasted, and Excel moves the original set of items politely out of the way.



Warning: Be careful when you use this featurebecause you aren't changing your whole worksheet equally, it's possible to mangle your data, splitting the information that should be in one row or one column into multiple rows or columns!

3.3.4. Deleting Columns and Rows

In Chapter 1, you learned that you can quickly remove cell values by moving to the cell and hitting the Delete key. You can also delete an entire range of values by selecting multiple cells, and then hitting the Delete key. Using this technique, you can quickly wipe out an entire row or column.

However, using delete simply clears the cell content. It doesn't remove the cells or change the structure of your worksheet. If you want to simultaneously clear cell values and adjust the rest of your spreadsheet to fill in the gap, you need to use the Edit Delete menu command.

For example, if you select a column by clicking the column header, you can either clear all the cells (by pressing the Delete key), or remove the column by choosing Edit Delete. Deleting a column in this way is the reverse of inserting a column. All the columns to the right are automatically moved one column to the left to fill in the gap left by the column you removed. Thus, if you delete column B, column C becomes the new column B, column D becomes column C, and so on. If you take out row 3, row 4 moves up to fill the void, row 5 becomes row 4, and so on.

Usually, you'll use Edit Delete to remove entire rows or columns. However, you can also use it just to remove specific cells in a column or row. In this case, Excel will prompt you with a dialog box asking whether you want to fill in the gap by moving cells in the current column up, or by moving cells in the current row to the left. This feature is the reverse of the Insert Copied Cells feature, and you'll need to take special care to make sure you don't scramble the structure of your spreadsheet when you use this approach.



Excel. The Missing Manual
Excel 2010: The Missing Manual
ISBN: 1449382355
EAN: 2147483647
Year: 2003
Pages: 185

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net