Table of Contents

Formulas are equations that perform calculations on values in your worksheet or workbook. A formula start with an “Equal to” sign (=).

Entering, Moving and Copying a Formula

Steps to Enter a Formula include: 

  1. Select a cell in which formula is to be entered.
  2. Enter a formula. For eg.: Type =A2+A3 in A4. You can also enter formula in the formula bar.
  3. Press enter.
  4. The formula calculates the result and displays it in the cell where you entered it.

Steps to Move a Formula include:

  1. Select the cell containing the formula you want to move.
  2. Cut the formula using one of these methods:
    • Use shortcut key “Ctrl+X”, or
    • Go to the Home Tab and click the “Cut” icon, or
    • Right-Click on the selected cell and choose “Cut” from the context menu.
  3. Select the destination cell where you want the formula to be.
  4. Paste the formula using one of these methods:
    • Use shortcut key “Ctrl+V”, or
    • Go to the Home Tab and click the “Paste” icon, or
    • Right-Click on the destination cell and choose paste.

The Formula is moved to your desired destination.

Alternatively, you can use drag and drop method:

  1. Select the cell containing the formula.
  2. Position your mouse pointer on the border of the selected cell until it changes to a move pointer (a four-headed arrow).
  3. Click and hold the left mouse button.
  4. Drag the cell to its new location and release the mouse button to drop the formula.

The Formula is moved to your desired location.

Steps to Copy a Formula include:

  1. Select the cell with the formula you want to copy.
  2. Copy the formula by:
    • Pressing Ctrl + C on your keyboard, or
    • Clicking the Copy button on the Home tab, or
    • Right-clicking the cell and choosing Copy from the context menu.
  3. Select the destination cell(s) where you want to paste the formula.
  4. Paste the formula by:
    • Pressing Ctrl + V on your keyboard, or
    • Clicking the Paste button on the Home tab, or
    • Right-clicking the destination cell and choosing Paste.

The formula is copied successfully.

Alternatively, use can use fill handle method:

This method is ideal for copying a formula to adjacent cells in a row or column.

  1. Select the cell containing the formula.
  2. Hover your mouse over the small square at the bottom-right corner of the selected cell until it turns into a plus sign (+).
  3. Click and drag this fill handle to the cells where you want to copy the formula. Excel will automatically adjust the cell references for the new locations.

Cell Referencing

A cell reference in Excel is an alphanumeric address (column letter and row number, e.g., A1) that identifies a specific cell on a worksheet, used in formulas to locate and use cell values in calculations.

Cell Referencing Procedure:

  1. Enter data in the cells.
  2. Enter formula in the formula bar using Cell Name instead of values (=C3+D3).
  3. Press enter.
  4. Result will automatically appear in the required cell.

Types of Cell Reference:

  1. Relative Reference: This is the default type. When a formula with a relative reference is copied to another cell, the reference automatically adjusts to the new location. Example: If you copy =B4*C4 from cell D4 to D5, the formula becomes =B5*C5.
  2. Absolute Reference: Indicated by a dollar sign ($) before the column letter and row number (e.g., $A$1). This type of reference does not change when the formula is copied. Example: If you copy =$B$4*$C$4 from D4 to D5, the formula stays exactly the same, =$B$4*$C$4.
  3. Mixed Reference: This fixes either the column or the row. Example: $B4 fixes the column, while C$4 fixes the row.

To change reference types, select the reference in the formula bar and press the F4 key to cycle through relative, absolute, and mixed references.

Naming Range

A range is a group or block of cells in a worksheet that has been selected or highlighted.

Steps to name a range include:

  1. Select the cell or range of cells you want to name.
  2. Click in the Name Box, located at the far left of the formula bar.
  3. Type the desired name for your cell or range. (Note: Names must start with a letter and cannot contain spaces. Use an underscore instead of spaces for multi-word names.)
  4. Press Enter.

The name is applied.

Alternatively,

  1. Select the cell or range of cells.
  2. Go to the Formulas tab on the ribbon.
  3. In the Defined Names group, click Define Name.
  4. In the New Name dialog box, enter your desired name in the "Name" box.
  5. Click OK to save the name.