Sunday, December 1, 2013

Excel Formula Shortcuts

·      The first shortcut is for summing cells.  This shortcut automatically inserts =SUM( ) in the formula bar, so you only have to select the cells you want to sum to complete the formula.  (Use this shortcut in place of the Autosum  tool or where the Autosum tool  isn't appropriate.)

  1. Click in the cell where you want the formula.
  2. Press ALT+ = (hold down the Alt key while pressing and releasing the Equals key)  This action inserts =SUM( ) in the formula bar. 

  3. Select the cells you wish to sum. 
  4. Press ENTER.

·     The second shortcut automatically inserts $ (dollar sign) in front of a cell name to make that cell reference an “absolute” (constant) cell reference. (The cell reference won’t change if you copy the formula).  Example: =$A$1* B1.  If you copy this formula to the cell below, the formula would change to: =$A$1*B2.  Only the “relative” portion of the formula changes.

  1. Click in the cell and type the formula.
  2. Click in the formula bar and select the cell reference that you don't want to change when copied. 
  3. Press F4 (function key)   The $ is inserted automatically before each selected cell reference.
  4. Copy the formula. (Position your mouse over the fill handle in the lower right corner of the cell containing the formula and either double-click or drag to the other cells.) When you copy the formula in cell C1, down to cells C2 and C3, the formula will always remain $A$1.  The formula in C2 will be: $A$1*B2.  The formula in C3 will be: $A$1*B3.