FUNDAMENTALS: 








IMPORTANT AND HANDY SHORTCUTS:

 

Move within a range: Ctrl + Directional Arrow
Select cells:Shift + Directional Arrow
Select a range:Ctrl + Shift + Directional Arrow
Fill/Copy Formula across range: Ctrl + R; Ctrl + D
Beginning / End of sheet: Ctrl + Home / Ctrl + End
Cycle between sheets: Ctrl + Page Up / Ctrl + Page Down
Bold / Italicize / Underline:Ctrl + B / Ctrl + I / Ctrl + U
Find / Replace:Ctrl + F / Ctrl + H
Undo / Redo: Ctrl + Z / Ctrl + Y
Format Cells Menu:Ctrl + 1
Select Row / Column:Shift + Spacebar / Ctrl + Spacebar

 

Alternative Fill/Copy formula across range

  1. Copy cell with formula
  2. Click in name box (box to left of formula bar)
  3. Write the range to which you want to apply the formula (e.g A2: A500)
  4. Press enter
  5. The range of cells will be selected
  6. Ctrl V to paste the formula across the range

 

USING THE FUNCTION KEYS:


F1  Help!
F2  Inside Cell (useful when wanting to edit a formula inside a cell)
F4  Repeat Previous action (useful when you want to repeat same action such as formatting or deleting etc.over and over)

F4 (while in a cell) 

 Lock cell references* - A1, $A$1, A$1, $A1 (very helpful when dragging formulas across big datasets)
F7  Spell Check
F9  Recalculate (useful when working in large spreadsheets, can set to manual calculate and then calculate as necessary)

*Note: In Excel there are two types of cell references:  Relative and Absolute. An absolute range will have a $ character before the column portion of the reference and/or the row portion of the reference. A relative range does not use a $ character and is not fixed in any way.  The $ character indicates to Excel that it should not increment the column and/or row reference as you fill a range with a formula or as you copy a formula across a range. 

Example of Relative range: A1:A14.
If this was to be dragged to the next column, it would become A2:A15.
If instead it was  dragged to the next row, it would become B1:B14

Example of Absolute Range: This is defined by where the $ is located - if in front of the row number, then row will be constant, column will change. If in front of letter, then the column is constant and the row will change. 

$A$1Both the column and row reference are fixed. Neither will be incremented or changed during a copy or fill operation.
$A1Only the column reference is fixed. It will not change during a fill or copy, but the row will change.
A$1Only the row reference is fixed. It will not change during a fill or copy, but the column will change
                                                                                           
 
  • Cell: E5
  • Row: 9
  • Column: B
  • Range: D11:E12
  • Worksheet: Sheet1
  • Workbook: Book1
  • No labels