Jez asked in Computers & InternetSoftware · 3 months ago

In excel what's the difference between absolute, relative and mixed?

In excel what's the difference between the cell reference relative, mixed and absolute?

1 Answer

Relevance
  • 3 months ago
    Favourite answer

    Relative reference:  When you copy a cell, the reference moves as well.  

           For example, if you have a formula in cell D10 which says "=A1+B2" and you copy D10 to D12, the formula in D12 would automatically change to "A3+B4".  (you moved 2 rows down in the same column).       

           If you copied D10 to F12, F12 would say "=B3+C4". (You moved 2 rows down AND one column to the right.)

    Absolute reference:  You hold the reference to an exact cell regardless of where you copy it to.  

          D10 formula says, "=$A$1 + $B$2".  No matter where you copy D10 to, the resulting formula will always say "=$A$1 + $B$2".

    Mixed:  You use both in one formula so that some references move and some don't.

         D10 formula says, "=$A1 + B$2".  Copy D10 to D12, the formula in D12 would change to "$A3+B$2". (The column stayed the same b/c of where D10 was copied to.  The second row reference stayed because '$2' means don't move that row reference.)

           If you copied D10 to F12, F12 would say "=$A3+C$2". ($A and $2 don't move b/c of the absolute reference, but the column and row with them without the $ do as they are relative references.)

Still have questions? Get answers by asking now.