Objectives of this Tutorial

Generally: To make changes to a worksheet and use the Find and Replace tools.

Specifically: On completion of this lesson, you will be able to:

• Go To a specific cell or named range
• Move, copy and paste cells using the Windows or Office Clipboard
• Use Find and Replace

Once you have created a workbook, the chances are that you will want to edit it. Excel makes it very easy to delete data from and add new data to a workbook. Excel also has a Find and Replace tool you can use to make changing several things at once much easier. There is also a Go To facility that helps you to navigate larger worksheets.

Name a Range and Use Go To

You can go directly to a specific location on the sheet. The location can be a grid reference or a named range. The Name box displays a list of cells in the workbook identified by text references (names) rather than grid references. For example, the name Sales could be given to a single cell containing a sales total, or to a group of cells containing sales figures.

To name a range of cells

• Select a cell or group of cells
• Click the Name box and enter a name up to 255 characters long
• Press Enter

Example of a cell name

Note: Names must start with a letter or underscore (_), and can include numbers, full stops (.), or underscores, but no spaces or other punctuation.

To select a range using the Name box

• Type the cell reference into the Name box

OR

• Type the range name into the Name box

OR

• Click the down arrow on the Name box and select a location from the pull-down list

Name box

The named cell(s) are selected on the sheet, exactly as they appeared when the name was created.

To select a range using the Go To dialogue box

• From the Edit menu, select Go To...(SpeedKey: F5 or Ctrl + G)

The Go To dialogue box is displayed showing a list of named locations in the workbook.

Go To dialogue box

• Select a name from the list or enter a grid reference in the Reference box then click OK

Move Data with Drag-and-Drop

If you create an entry and decide that it is in the wrong place, it can be moved around the sheet without retyping. Where data needs to be entered in two or more places it can be copied instead of being entered manually each time. Single cells, groups of cells or whole columns and rows can be moved or copied using drag-and-drop.

To move selected cells

• Select the cells to be moved
• Position the pointer on any edge of the highlighted selection

The pointer changes to a four pointed arrow .

• Click-and-drag to the new location

Drag-and-drop selection

A placement box and a ScreenTip display the range of cells that the selection is dropping into.

Note: To copy a range instead of moving it, hold down the Ctrl key.
A small cross appears above the pointer . Release the mouse before releasing the Ctrl key.

Warning: If you move a selection and the new location overlaps an existing entry a warning message is displayed, allowing you to replace the existing entry or cancel the move.
If you copy a selection and the new location overlaps an existing entry the existing entry is replaced with no warning.
You can insert the selection by holding down Shift while you drag. You can combine the Ctrl + Shift keys.

To move columns or rows

• Select the column(s) or row(s) to be moved
• Point to the edge of the highlighted selection

The pointer changes to an four pointed arrow .

• Click-and-drag to the new location

Drag-and-drop rows

A placement line jumps between the columns or rows and a ScreenTip displays the range of columns or rows that the selection is dropping into.

Tip: Use Ctrl to copy a selection and Shift to insert the selection.

Continued...