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 Well-Known 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.
  3. Waleed Saleem

    Waleed Saleem Member

    Feb 1, 2019
    Likes Received:


    I am a little confused with the use of offset function in lesson 4.10 and lesson 4.12. Why did in one instance we used =offset($B$6, row()-6) and in another instance row()-15?

Share This Page