Overtyping is used to replace data in cells by typing new information.
Editing by Overtyping
Click on the cell to be changed.
Enter new data.
After completing the cell entry the new information replaces the old.
3.2.2 Undo/Redo
When editing a worksheet, the last change made can be undone by selecting either Edit | Undo …. or the Undo button, .
After completing a replacement entry, this action can be undone with Undo.
Select Edit | Undo or click Undo button to reverse the last action.
After Undoing an action, it can be Redone by selecting Edit | Redo …. or clicking the Redo button .
All actions that can be undone are stored in the Undo history. To use:
Click the drop down list next to the Undo button
Select the number of required Undo actions to a maximum of 16.
3.2.3 Editing Cells
Cell contents can be changed by editing when the information is more complicated and overtyping becomes impractical.
Formula bar editing
Click on the cell to edit and click in the contents in the Formula Bar.
The cursor can be moved around using the cursor movement keys and the Home and End keys.
To complete the entry press <Enter>. Other keys such as the down cursor do not complete the entry after editing.
In-cell editing
Editing can take place directly in the cell.
Place the cursor within the cell.
Double click to start In-cell editing.
The same rules apply as with Formula Bar editing.
TIP
Press F2 to start In Cell editing. The cursor is placed at the end of the contents in the active cell. Any part of a cell’s contents can be formatted, by clicking and dragging, during the edit process.
3.2.4 Erasing Data
The contents of cell/s can be erased by:
Clicking on the required cell or highlight a group of cells.
Select Edit | Clear | Contents.
Note:Selecting All clears contents, comments and any formatting. Formats clear all the formatting and leaves the contents. Comments only clear the cell comments.
TIP
A selected cell’s contents can be erased, by pressing the <Delete> key.3.2.5 Copy, Cut and Paste Cells
The copy command can be used to copy labels, values and formulas of a single cell or a group of cells to other areas of the worksheet.
The selected cells are placed in an area of Windows called the Clipboard, from where they can be pasted back into the required
Copy
Select the cell or cells to be copied.
Select Edit | Copy or the Copy button, on the toolbar.
Note: Excel places a marquee (a dotted line around the selected cells to be copied).
Paste
Select the cell or click and drag a range or select the top left cell of the paste area for a range.
Click Paste , if required, more than once (see Note) Or
Press <Enter>, to paste and clear the clipboard.
Note: When usingPaste, the original cell can be pasted repeatedly whereas
<Enter> terminates the copy process.
Paste Special
Paste Special is used to paste cell information when a straight forward copy is not required.
When to use Paste Special:
To combine 2 ranges by adding, subtracting, etc.
To transpose a range (rows to columns and columns to rows).
To convert formula to values by pasting to the same location.
To use paste special
Highlight cell/s to copy.
Select Edit | Copy or click or right click and select Copy.
Place active cell at the destination cell.
Select Edit | Paste Special.
Choose the required options.
Select destination cell and Edit |Paste or or press <Enter>
TIP
Cells that are Cut and Pasted can be easily moved. Select the range, place the cursor on any edge (it becomes a white arrow), drag and drop to the new location.3.3 Ranges
3.3.1 Selecting Cells using the Mouse
A range is a rectangular collection of cells, consisting of just one cell, a row or column of cells or a block of cells covering several rows and columns. Just as single cells are identified by location, e.g. C9, F11 etc., ranges are identified by the cells of their outer limits, e.g. the four cells B2, B3, C2 and C3 is the range B2:C3.
Selecting a range using the mouse
Click and drag the mouse to highlight a group of cells.
Note: The first cell is the active cell and remains white, whilst the rest of the range is displayed in black.
Alternatively, click on the first cell and hold down <Shift> whilst clicking the last cell.
Note: If the range is larger than the screen, stay in the grey areas next to the worksheet, as the selection process is very fast if the pointer touches the edge of the screen.
Selecting more than one range
Holding <Ctrl> whilst clicking and dragging from the second range onwards. This keeps the first range highlighted whilst selecting the others (useful when charting).
Select a range using the keyboard
Move using the cursor keys to highlight the range.
Press F8 again to ‘turn off’ the selection.
TIP
To extend a range after releasing the mouse button, hold down Shift and click the last cell or drag to the last cell.3.3.2 Using the Fill Handle
Excel allows ranges to be quickly filled with data by using the Fill Handle, at the bottom right corner of the active cell or range.
Select the cell to be copied.
Place the mouse pointer over the fill handle (pointer changes to a small black cross).
Click and drag in one direction (usually down or right) until the required range is selected and the mouse button released.
The range will be filled with the original cell contents or the formula copied.
The Fill Handle can be used to custom fill a range (a ToolTip shows the extent of the range).
Any text with a number on the end, e.g. Month 1, Week 4, Salesperson 2.
Months of the year (long or short format, Jan or January).
Days (steps in single days).
Custom Lists created by user (covered in a later section).
More complicated series
Enter in the first two cells (week ending dates, stepped numbers).
Highlight the two entries.
Drag with the Fill Handle to complete the series.
TIP
For a number sequence increasing in ones, enter first number and hold Ctrl whilst dragging the fill handle.3.3.3 Filling a Range
Enter first cell.
Fill Handle
Highlight the range to fill including the first cell.
Select Edit | Fill | Series.
Enter step value, if not 1.
Select from Type or Date unit.
Click OK to fill the range.
Note: Fill series only works in one direction, either a row or a column.
3.3.4 Copy Formulas
Copying formulas can be carried out in precisely the same way as labels. If the user wishes to use the same formula in another position, e.g. to total the NEXT column of figures, then the references of the columns within the formula will require adjustment.
By default Excel will carry out this adjustment automatically. This is because cell addresses are held as Relative Addresses. (This is covered in a later section.)
Copying formulas using copy and paste
Select the cell to be copied.
Select Edit | Copy or click .
Highlight a cell or range to receive the copy.
Select Edit | Paste or , if required more than once or press
<Enter>.
Copying formulas using the Fill Handle
The fill handle can be used to copy formulas across a row or down a column.
Enter the formula to the right of the first row and at the bottom of the first column.
Use the fill handle to copy each of the formulas to adjacent cells (the grand total, bottom right can be either the sum of the rows or of the columns in a normal spreadsheet model).