Format & Arrange:

Title: Jan's Illustrated Computer Literacy 101
Did you want Working with Numbers: 2007,2010,2013,2016  or españolIcon: Change web

It is rather amazing how often you need to move a row or column on your spreadsheet. Fortunately Excel makes this easy to do. You do have to plan ahead, however. If you are not careful, you may overwrite other cells and lose their data.



Where you are:
JegsWorks > Lessons > Numbers

Before you start...

Project 1: Excel IntroTo subtopics

Project 2: Excel BasicsTo subtopics    

Project 3: Format & Arrange
    Format CellsTo subtopics
    Format ChartTo subtopics
    Arrange Open arrow to subtopics
    Exercises To subtopics

Project 4: Groups & FormulasTo subtopics

Project 5: DesignTo subtopics


Icon Step-by-Step

Step-by-Step: Move

 Icon Step-by-Step

What you will learn:

to move by dragging to empty cells
to move by cut and paste
to move cells to non-empty cells
to move with right drag
to move with menu commands
to move a sheet

Start with: Class disk trips16.xls - Sheet1 (saved in previous lesson)

Whether you are moving a single cell, a range, a row, or a column, the same methods apply. The method you choose will depend on whether the destination is blank or already has data and on whether you want to replace the data or move it aside to insert the new data.

To practice moving data, you will first move the Total column away from the lower table. Then you will put the table back together again by moving its other columns. You will switch the positions of the Customer and Trip columns in the upper table and then of the Week and Date columns in the lower table. Your final move will be to move the sheet Tickets Sold Chart.

Move: Drag to empty cells

  1. Select totalsSelect range D27:D37, the Total column of the second table. You will move these cells to underneath the Total Sale column of the first table, which is in Column E.  
  2. Lower tableDrag the border of the selection to the right.
  3. When the border of range E27:E37 is highlighted in gray, release the left mouse button to drop the cells.

    All of the formulas still work! The cells in D27:D37 are now blank and are not formatted. 

Move: Cut & Paste

  1. Table movedSelect range A27:C37 and cut it by clicking Button: Cut the Cut button or use the key combo CTRL + X. You will paste them so that the table is back together again.
  2. Select cell B27, which will be the upper left cell for the new location.
  3. Paste with Button: Paste the Paste button or use the key combo CTRL + V.

    When you pasted, you pasted both the cell contents and the formats. The now-empty cells in column A have lost their formatting. So cool!

Move: Drag to nonempty cells

  1. Drag Cutomer cells to Trip cellsIf necessary, click in a blank cell to deselect what you pasted.
  2. Select range A4:A25, which is the column of Customers in the first table. You want to move this to the right so that it is between the columns for Trips and Number of People.

  3. Message: Do you want to replace the contents of the destination cells?Drag the selection by its border to the right until the range B4:B25 has its border highlighted in gray. Release the mouse button.

    Message: Do you want to replace the contents of the destination cells?Instead of the cells being dropped, you get a message asking if you want to replace the contents of the destination cells. No! You don't want to do that.

    You see the plain gray message box when Office Assistant is not on.

  4. Click on Cancel since you don't want to erase any data.   

    The selection goes back to its original place, but it is still selected.

Move: Right Drag

  1. Popup Menu: choices to mover or copyRight drag the selection by the border to the right until the range B4:B25 has its border highlighted.
  2. Release the mouse button. A popup menu appears with many choices. The Shift commands will move what is currently in the destination cells, the Trips cells. But none of the choices will shift the Trips cells to the left where you want them! Only right and down are available. Rats! You will have to do this another way.
  3. Select  Cancel .

    Clearly you must plan how you are going to drag cells so that the existing data can shift right or down. Let's try moving the Trips column instead of the Customer columns.

  4. Select range B4:B25, which is the Trips column, and right drag to the left until the range A4:A25 has its border highlighted. Release the mouse button. The popup menu appears again. 
  5. Trips data moved left of CustomersSelect  Shift Right and Move . Hurrah! The Trips cells are moved to the left and then the Customers cells are shifted right. Just what you wanted.

    The column widths did not change to match the new contents.

  6. AutoFit columns A and B by double-clicking the right edge of the each of the column headings.

Move: Cut & Insert with menu

  1. Select range C27:C37, the Date column in the second table. You want it to switch places with the Week column. That is, you want to insert the selected data and move the Week data over to the right.
  2. Cut. The selected range now has a blinking border.

    TipIf you press ESC now, your data is not cut after all and the blinking border is removed.

  3. Click on cell B27, the top of the Week column.
  4. Dates movedFrom the menu select  Insert  |  Cut Cells . The Date column is now on the left, followed by the Week column.  
    Message: This selection is not vallid.Warning If you forget to select a destination before you choose    Insert  |  Cut Cells from the menu, you will confuse Excel and get an error message. None of the reasons listed in the message sound like what you did. Just click on OK and try again.

    TipWhen you copy cells (instead of cut) and forget to choose a destination before choosing  Insert  |  Copied Cells , you get a list of choices for what you can do.

    Dialog: Insert Paste (Excel 97)

    Dialog: Insert Paste (Excel 2002)

  5. Preview: trips17.xlsClass disk Save as  trips17.xls  on your Class disk in the excel project3  folder.
    full floppy diskHow to handle a full disk
  6. Print Preview and Print Sheet1 on one page in Portrait orientation.


Move: Sheet

  1. Drag the tab for the sheet Tickets Sold Chart to the right. A small black arrowhead appears above the sheet tabs and the pointer now includes a sheet of paper icon.Tickets Sold Chart tab is selected
  2. When the arrow is at the far right of the last sheet tab, release the mouse button to drop the sheet.
  3. Class disk Save. [trips17.xls]
    full floppy diskHow to handle a full disk