The SMOKEY LAKE

Excel Help site

 

 

Excel Editing Tips

This is a compilation of tips and techniques for working in Excel sheets that can save you time once mastered.

 

Basics

Cells contain:

Contents:

  • Three kinds:

    • Text (John, Dept 1,  A1555.5)

    • Numbers (1,  $1.95,  15%,  May 21, 1944,  1/2)

    • Formulas (=A1,   =A1+A2,    =SUM(A1:A2),    ='Sheet 2'!A1,      ='[Book2.xls]Sheet 2'!A1)

  • Cleared by Delete key.

Formats:

  • Change appearance (number formatting, color, borders, etc.)  Use Formatting Toolbar, or use Format - Cells. A number format makes the number 1.95 appear as $1.95.  A date number format makes the number 38217 (the date serial number for Aug 18, 2004) appears as Aug 18, 2004.  A percentage format makes the number 0.23 appear as 23%. Etc.

  • Some are applied automatically when you enter data (date, percent, currency). You can't stop this behavior unless you've first formatted the cell for Text (Format - Cells - Number), or preceded it with an apostrophe..

  • Not cleared by Delete key. (Use Edit - Clear - Formats to clear all formats.

NOTE:  Sometime the term "format" is used to mean "layout."  In Excel, cell formats change the appearance of cell contents. Sheet layout is, well, sheet layout.

 

Entering into cells
Entering text or numbers:

  • Pressing Enter:

    • Normally finishes the cell, puts Excel back into Ready mode, and moves the cell pointer down
         (unless a different direction has been chosen in Tools - Options - Edit - Move selection after Enter).

    • As an alternative to Enter, you can select another cell via the arrow keys, or by clicking it.  Not applicable when entering formulas.

  • Formatting text as you type:  You can change font formatting as you type, rather than While typing non-bold text, you can press Ctrl-b to switch to bold, type the text that's to be bold, then Ctrl-b again to turn bold off, and continue typing non-bold text.  This works with italic and underscore also, and they can be used in combination. Don't need to reach for the mouse at all. This applies to other font formatting too, like font, size, color.  Not applicable when entering numbers or formulas. Ctrl-b works exactly like clicking the Bold button on the Formatting Toolbar. To make an entire cell bold after it's been selected, you can simply press Ctrl-b. 

  • Formatting you don't want, Excel automatically applies number formatting in certain situations, sometimes inappropriately.  For example, you enter 1/1, not intending to get a date, and Excel puts in that date value, and automatically formats it as date.  You can stop this behavior you can (1) before entering the data, change the number formatting (Format - Cells - Number tab) to Text, or (2) precede each entry with an apostrophe ('). , For the automatically applied formatting, you can either (1) Remove all cell formatting with Edit - Clear - Formats (Alt-e -a - f) (note that the Delete key does not remove formatting), or (2) manually change the number formatting (either with number formatting buttons on the formatting toolbar ( $, %, comma ) or Format - Cells - Number. Use the second option if you already have other formatting (cell color, borders, etc.) you want to keep.

Entering formulas:

  • Formulas always start with =.
  • It's not necessary to type cell addresses into formulas.  While entering a formula, selecting a cell (either by clicking or via the arrow keys) switches Excel to Point mode, and puts a reference to that cell in the formula. Very handy.
    NOTE:  If you haven't used an operator (=, +, -, etc.), then you may not get the cell reference inserted into your formula.
    NOTE: Therefore, you must press only Enter or Tab when done with a formula -- not an arrow key or clicking a cell, as you can do when entering text or numbers.
  • If you want your cell reference to be absolute, you can press F4 right then and there (as long as the cursor is in it or touching it). Press F4 repeatedly for all combinations of row/column absolutes.  See "Cell references - absolute/relative" in Miscellaneous section below.
  • If you want to move the cursor via the arrow keys, such as backing up to correct an error, switch to Edit mode (press F2).  Now the arrow keys move the cursor.  Switch back to Enter mode if you want to be able to again use the arrow keys for cell references in a formula.

 

 

 

Ready/Enter/Edit modes     

  • Awareness of Enter or Edit mode will enable you to know what will happen when you click cells, use arrow keys, and prevent unexpected behavior of Excel.

  • F2 switches you between Enter and Edit modes at any time as you type.

  • Most menu bars, toolbars, and keyboard shortcuts are not active in Enter or Edit mode.

  • As you read this stuff, be aware of the difference between the cursor and the cell pointer.

Ready Mode

  • Excel is normally in Ready mode. Use menu commands, toolbar buttons, etc.

  • There is no cursor on the screen  (unless you're in a text box or other control).

Enter Mode:

Used to put something into a cell, replacing any prior contents

  • Excel goes into Enter Mode when you begin to type anything into a cell.

  • There is a cursor in the cell.

  • The prior contents of the cell are replaced.

  • Many menu bar items, toolbars, and keyboard shortcuts are not active.

  • The arrow keys (if number or text) move the cell pointer, and end Enter mode (back to Ready mode).  Same thing if you click a cell.  Useful to end a cell, and go directly to another.

  • The arrow keys, (if a formula) switch you to Point mode which puts a reference to that cell in your formula right where the cursor is. Same thing if you click a cell.  This is useful for quickly putting cell references into your formula without having to type the cell address.  For a range of cells, you can also drag (or Shift-ArrowKey).

Remember:

When finished with a formula, press only Enter, Tab, or click the green checkmark next to the formula bar when done. Don't click a cell or use the Arrow keys.  It may put an unwanted cell reference into your formula and make you crazy (unless you've switched to Edit Mode, below).

Edit Mode

Used to change existing contents of a cell.

  • Double-clicking a cell or F2 puts Excel in Edit mode.  Also clicking in the formula bar. You can also press F2 at any time while in Enter mode to switch to Edit mode.

  • There is a cursor in the cell.

  • You start with the existing (prior) contents of the cell; it's not replaced.

  • The arrow keys move the cursor within the cell.

  • Many menu bar items, toolbars, and keyboard shortcuts are not active.

  • If you're entering text or a number, selecting a cell (either by clicking or with the arrow keys) selects that cell and puts Excel back into Ready mode. Same as with Enter mode

  • If you're entering a formula, selecting a cell by clicking it behaves the same way as with Enter mode (switches to point mode -- puts a cell reference into the formula).

Tip:

If you want to correct a mistake while typing into a cell, press F2 to switch to Edit mode.  Now you can use the arrow keys to move the cursor back (otherwise it will move you to the adjacent cell) and make the correction.

 

Tip:

Get in the habit of pressing Enter when done entering information into a cell.  Don't leave Excel in Enter or Edit mode -- many menu bar items, toolbars, and keyboard shortcuts are not active.  Or you can use an alternative to Enter, such as Tab, a direction key, or clicking another cell, where appropriate. 

Remember:

When in a formula, before clicking a cell or using Arrow keys, check for Enter/Edit mode.  Change as necessary with F2.  You can inadvertently cause cell references to be inserted into your formula if in Enter mode. 

If you want to use the arrow keys to move the cursor, you want Edit mode.
This applies in other situations, such as any time there are cell references, such as with the many RefEdit text boxes in Excel dialog windows like in Insert - Name - Define, Chart series source data, etc.

 

Dragging techniques

There are really three kinds of dragging with cells; it doesn't do to just say "drag from here to there," or "drag down."

 

1. Select-dragging -- selects cells

  • This is done by starting the drag inside a cell.  The mouse pointer looks like a cross.

  • You can do this with the keyboard instead with Shift and an arrow key.

2. Move-dragging -- moves cells.

Select cell(s) to be moved first

  • Done by starting the drag at the border of the selection.
    Avoid the lower right corner (the Fill Handle).
    The mouse pointer is an arrow (or four arrows in later versions of Excel).

  • Hold Ctrl for a copy instead of move. Doesn't involve the clipboard.

  • Drag with right button for options.

  • Hold Alt to move (or copy) into another sheet, via its sheet tab.  Ctrl for copy.

  • You can drag directly into most other applications, or to the task bar, then into another application.  Ctrl for copy. You can probably undo the move in the other app, but not in Excel -- it's gone.

  • Move-dragging can be disabled in Tools - Options - Edit - uncheck "Allow cell drag and drop."

  • "Do you want to replace the contents of the destination cells?" happens if the cell(s) you're dragging into aren't empty. This is controlled by Tools - Options - Edit - "Allow cell drag and drop" - "Alert before overwriting cells."

  • Cut/Paste achieves the same objective -- an effective move.  .

  • Hold Shift to reorder cells, rows, or columns in one move.

  • Formula references follow moved cells.

 
 

Tip:

Move-dragging changes formula references, and should be exercised carefully in sheets with formulas.

Excel changes cell references in formulas when the referred cells are moved, where possible.  This means you can move a cell, and the formula now refers to it in its new location.  This allows easy layout changes while designing a worksheet, as the formulas "follow" the referred cells  you've moved.  Sheet protection does not stop this. Making the reference absolute does not  stop it -- that affects copying only.  Nothing stops it, short of pulling the power plug. You can disable move-dragging altogether (Allow cell drag and drop, in Tools - Options - Edit).  Note that turning off this option does not disallow Cut/Paste, an effective move, which also changes cell references in the same way as drag-moving.

Move dragging is a design tool, not a data entry tool.   Ensure that data entry people are aware of this.  After the sheet design is complete, you may want to turn off "Allow cell drag and drop" (Tools - Options - Edit).  Note that this is Excel-wide, not just one sheet or one workbook. Users can copy instead of move (copy/paste or Move-Drag with Ctrl held), then delete the original to get around this problem.

 
3. Fill Handle dragging -- copies or fills cells.

This makes consecutive copies (Jim Jim Jim...), or a series (Jan, Feb, Mar or Dept1, Dept2, Dept3...)

  • Done by starting the drag at the Fill Handle (button in the lower right corner of the selection).
  • The mouse pointer is a cross-hair.
  • Use the Ctrl key to modify the behavior of the Fill Handle (stop incrementing, etc.)
  • Drag with the Right mouse button for options on Fill handle behavior.
  • This is also disabled with Tools - Options - Edit - uncheck "Allow cell drag and drop."
  • For a series (2,4,6 8, Jan1, Jan 8, Jan 15, etc.) type the first two, select both cells (above) then copy with fill handle.

 

  

Shortcuts

For a complete list of shortcuts, use Help (F1 or Help - Microsoft Excel help).  Enter "Keyboard shortcuts"

Data entry shortcuts:  

Current date:  Ctrl - ;   (Ctrl - semicolon)
Current time: Ctrl - : (Ctrl - Shift - colon)
Open Validation dropdown: Alt - Down.
Pick from list of currently entered data:  Alt - Down
Reenter from cell above:    Ctrl-'    (Ctrl - apostrophe)

 

Worksheet design shortcuts:

Beginning of row Home
Beginning of worksheet Ctrl Home
Clear Formats Alt e a f
Copy Ctrl c
Cut Ctrl x
Delete (not clear) Ctrl -      (Ctrl minus)
Dropdown open Alt Down
End of worksheet (Used range) Ctrl End
Find Ctrl f
Insert Ctrl +      (Ctrl plus)
Paste Ctrl v
Pick from list Alt Down
Save Ctrl s
Select all: Ctrl Shift Spacebar   or   Ctrl a
Select column: Ctrl Spacebar
Select row: Shift Spacebar
Select sheets Ctrl PageUp or PageDown (add Shift for consecutive sheet selections)

 

Miscellaneous

Cell references --  Absolute or relative.  Absolute cell references ($A$1) appear in formulas, and when copied, the copies are not changed.  With relative cell references (A1) they are changed.  Other than that, they behave identically. Note that moving a cell to which a formula refers will cause the formula reference to change regardless of absolute or relative.

Date intervals

Example:  You want Friday dates, down a column.

This works with other things, like Section 1, Section 11, Section 21, etc).

 

Dates stay dates,  even after deleting.  See "Formatting won't go away."

 

Formatting won't go away.  Example: You put a date into a cell, then delete it with the Delete key.  Any other number subsequently typed into the cell appears as a date.  This is because (1) Excel automatically formatted the cell for date, and (2).the delete key does not remove any formatting (only cell contents).  Change the number formatting to a non-date number format (Formatting toolbar or Format - Cells - Number). Or remove all formatting with Edit - Clear - Formats.  (Alt-e, a, f -- a good one to remember).

Intervals - Week or month (Jan, Feb, or Mon, Tue, etc)

Note:  If you wanted to repeat Jan (or Mon, etc.), hold Ctrl as you use the Fill Handle.

Moving a cell without affecting formulas with reference to it.

In many cases, moving a cell to which a formula has a reference will cause the reference to change, "following" the cell.  There is no option to stop this behavior.  To prevent this, copy the cell instead, then delete the original.

Numbers in cells.  Numbers in cells should generally be labeled (usually in the cell above or to left).  It's good design practice.  Unlabeled numbers should not by laying around in a worksheet, unless it's very clear what they are.  It's a good habit to put the label into a cell first, then the number into a cell.  In a table of numbers, use column and row labels.

Numbers in formulas. Numbers can be used in formulas, but this can lead to ambiguity. 
Where possible, use a named cell or value (Insert - Name - Define).
E.g.: "TaxRate"

Reordering entire columns (or rows, or cells).

This technique is essentially a move-drag of an entire column or row with the Shift key (above). It automatically inserts columns to make room for the moved data, and deletes the space left behind.  The move-drag with Shift does this all in one step.

 

Home