Guys,I am totally confused..* need more explanat...

Discussion in 'General Discussions' started by Maha_3, Jun 26, 2018.

  1. Maha_3

    Maha_3 Customer

    Jun 22, 2018
    Likes Received:
    I am totally confused..
    * need more explanation about Index & Offset functions.
    * when to add ($) when to not ?
  2. Shohini_1

    Shohini_1 Active Member
    Simplilearn Support

    Sep 24, 2018
    Likes Received:
    Hi Maha,


    Please find the explanations below:

    The OFFSET function in Excel returns a cell or range of cells that is a specified number of rows and columns from a cell or range of cells.


    The OFFSET function below returns the cell that is 3 rows below and 2 columns to the right of cell A2. The OFFSET function returns a cell because the height and width are both set to 1.


    The INDEX array form returns the value of an element in a table or an array based on the row and column numbers you specify.

    Syntax: INDEX( array, row_num, [column_num])
    1. array - is a range of cells, named range, or table.
    2. row_num - is the row number in the array from which to return a value. If row_num is omitted, column_num is required.
    3. column_num - is the column number from which to return a value. If column_num is omitted, row_num is required.
    For example, the formula =INDEX(A1: D6, 4, 3) returns the value at the intersection of the 4throw and 3rd column in the range A1: D6, which is the value in cell C4.

    A dollar sign $ is called a locked cell reference.

    Locked cell references are one of the building blocks of Excel modeling. Without them, you will waste endless amounts of time re-writing formulas.

    Locking cell references will anchor formulas in specific ways so that we can copy and past them without having to rewrite them.

    Here is a lesson on the three types of locked cell references and how you can use them to copy & paste your formulas.

    There are three ways to lock a cell reference:
    • Locked column
    • Locked row
    • Locked column & row

    Lock the column: =$D2 -> =$D9

    The column stayed locked as D, but the unlocked row moved down to row 9
    So the output changed to 0 (empty cell)

    Lock the row: =D$2 -> =E$

    The column shifted right to column E, but the row stayed locked at row 2
    So the output changed to 0 (empty cell)

    Lock the cell: =$D$2 -> $D$2

    The locked cell did not change at all
    Output did not change

    Lock the range: =SUM($D$2:$D$6) -> =SUM($D$2:$D$6)

    The locked range did not change at all
    Output did not change

    If you still get stuck somewhere, please let me know.

Share This Page