Excel Basics, Page 3

Copy, Cut, & Move Data & Cells

Copying or Moving Data with the Mouse

If you need to move or copy data from one place to another on the same worksheet or to a different worksheet, you can do so using the Cut, Copy, and/or Paste commands.

Select the cells containing the data you want to move or copy by clicking on the bottom right or top left cell and dragging through the range of cells to be moved or copied (to use Cut or Copy and Paste, the cells must be in a contiguous block. You can't paste a non-contiguous block of cells.).

To move data, right-click in the selected block and click on the Cut (scissors icon) on the Standard toolbar or press [Ctrl + X]. If you prefer, you can right-click in the highlighted block and select Cut from the context menu or highlight the block and select Cut from the Edit menu.

To copy data, the instructions are the same, except you'll use the Copy button on the toolbar (usually just to the right of the Cut button) or, from the keyboard, [Ctrl + C].

To paste copied or cut data, right-click in the cell where you want to insert the cut data (the top left corner of the range will be placed in the cell you click in) and select Paste from the context menu.

Cut, Copy, and Paste from the Keyboard:

Hold down the [Shift] key while using the arrow keys to select the cells you want to cut or copy. Use [Ctrl + C] to copy the range or [Ctrl + X] to cut the range, and [Ctrl + V] to paste the cells to a new location (these keyboard commands work exactly the same way in almost all Windows programs).

Using Drag & Drop or the Fill Handle to Copy Data or Move Cells:

To copy the contents of a cell into a number of cells in either the same column or the same row, click on the cell to activate it, then place the mouse cursor over the copy handle in the lower right corner of the cell (the tiny square; the mouse pointer will change to a plus sign when placed over the copy handle). Click and drag through the range of cells where you want to copy the data. When you stop dragging and release the mouse button, the data from the original cell will be copied into all the cells in the new range.

Caution: Excel 97 uses almost the same method (the copy handle) to copy a series. Be sure that you see a small plus sign to the right to copy data, and pay attention to the little tags that pop up – they’ll tell you exactly what it's going to put into the next cell. You may need to use the [Ctrl] or [Shift] key as you drag to get the desired results.

To move the contents of a cell or of a range of cells, click on the cell to select it (or select a range of cells), then move the mouse pointer to the cell or range border until it changes to a northwest arrow (an arrow that points up and to the left). Hold down the left mouse button and drag the cell(s) to a new location.

Caution: If there is already data in the destination cell, the Excel Office Assistant will ask you if you want to replace the contents of the destination cell(s). Click No – unless you’re sure you want to replace the data currently in the cell(s) – and move the selected cell(s) to another location.

Manipulating Columns & Rows

Changing Column Height/Width

The easiest way to change the height of rows and the width of columns is to size them by hand (if you're only doing a few!). Move the mouse pointer to the column header on the right side of the column you want to size and place it on the line between columns. The cursor will change to a black vertical bar with left and right horizontal arrows. Click and drag to the desired width.

New in Excel 97

As you drag on column or row borders to change the height or width, Excel 97 will use text tips to tell you the current measurement of the column or row. "The column width display shows the average number of standard-font characters that will fit in the column." By default, this number is 8.43. "Row height is measured in points and the standard height is based on the size of the default font." The default size is 12.75 points.

To set all columns to the standard width –except those you've changed manually – select Standard Width from the Column...command in the Format menu.

New in Excel 97

Excel 97 will automatically resize columns when numeric entries exceed the column width unless you've already manually resized the column. So you won't see as many of those pound signs when the number is too big for the column it's in. If you've resized the column manually and see #,##### instead of a number, double-click on the line to the right of the column header (A, B, etc.) to automatically resize the column.

Change the height of a row the same way, but place the mouse pointer at the bottom of the row label on the black line. The black bar will be horizontal and the arrows will point up and down, indicating the directions you can drag to change row height.

To set a specific cell width or row height on a number of rows or columns at the same time, select a range by clicking and dragging on the row or column labels, or click on the Sheet Select button (the blank button to the left of the first column header (A) and just above the first row number (1)) to change the rows or columns for the entire sheet. Select either Row or Column from the Format menu. Select Height... in the Row menu, and type in a height or select Width... in the Column menu and type in a width.

This function can also be accessed by right-clicking within the highlighted area to open the context menu. If you have highlighted a column or columns, click on Column Width... or select Row Height if you've selected one or more rows.

Standard Width: If you set a standard width in the Columns menu, it will change the width of any columns that have not been changed manually.

AutoFit: Use the AutoFit function in either columns or rows after you have entered data in one or more cells. The column width will change automatically to accommodate the widest entry in the column. This can also be done by double-clicking on the line to the right of the column label. Excel automatically adjusts the row height based on the largest font in the row.

Hiding & Unhiding Columns & Rows:

It is occasionally convenient to be able to print or display a worksheet without including certain information: perhaps you have a column or two of highly confidential data that you don't want to include in a report for general circulation. Or you might prefer to hide a column containing formulas. The hidden column(s) or row(s) will not print and will not be visible on the monitor screen. But they are still there! The formulas still work and the data is all intact, because the way Excel hides them is to reduce their height or width to zero. They're still there; but they're very, very small.

To hide a column or row:

1. Select the column(s) or row(s) you want to hide by clicking on the appropriate heading (A, B, 1, 2, 3, etc).
    
2. Select Column or Row from the Format menu then click on Hide, or right click in the highlighted area and select Hide from the context menu.

To unhide a column or row:

1. Select a range that includes the hidden row(s) or column(s).
    
2. Click on Column or Row in the Format menu, then select Unhide, or right click in the highlighted area and select Unhide from the context menu.

You can also use GoTo ([Ctrl + G]) in the Edit menu and type in a cell address in the hidden area, or type the hidden cell address into the Reference box*, then select Column or Row, Unhide from the Format menu (this is particularly helpful when trying to unhide column A or row 1.

Note: I found that when I hide columns or rows all the way to the left or at the very top of the sheet, the easiest way to unhide them is to click on the sheet select button, then double click on either the top or the left border of the first column or row label that is unhidden (make sure the cursor has changed to a dark double line with arrows pointing either right and left, or up and down). You may have to use the scroll bar to see all the unhidden columns or rows, but they'll be there.

*The Reference box (also known as the Name box) is directly above column A. When you click in a cell, the cell's address is shown in the Reference box. You can also use it to move to a particular cell or a named range by clicking in the box and typing in a cell address, then pressing the [Enter] key, or by clicking on a range name (named ranges are not covered in here; see Excel Help for further information).

Moving Columns and Rows

Occasionally, you set up a worksheet and begin to work in it, only to find that you forgot to include a particular column when you designed the sheet. It's easy enough to insert a column and even rearrange the order of columns. You can use the same methods to insert and rearrange rows, although it's generally easier to just sort the data (when appropriate) than to physically move rows around.

To Insert a Column:

1. Click in the column to the right of where you want the new column to appear.
    
2. Select Columns from the Insert menu (or right-click and select Insert from the context menu).

Once you've inserted a column, you can enter new data in it or copy, move, and paste data from another column.

To Move a Column: Perhaps you set up a field (a category of data) in column N, then decide that you'd prefer to have that data in column C. It's easy to move it:

1. Insert a new column as described above where you'd like the existing data to appear.
    
2. Select the column you want to move by clicking on the column label (or by selecting all the data in the column).
    
3. Move the mouse pointer to the edge of the highlighted area until it turns into a northwest arrow. Click and drag the data to the new location.

Note: Whenever you move data from one location to another, if the new location already contains data (unlikely if you just inserted it!), you'll be asked if you want to replace the existing data with the data you're about to paste. Be careful: if you answer 'yes', the data that was in the cells originally will be gone for good.