Introduction to cell references

Introduction to Google Sheets

James Chapman

Curriculum Manager, DataCamp

Recap: cell addresses

  • Each cell is uniquely identified by its cell address, e.g., D4

 

  • Current process to use a cell's value:
    1. Identify cell using its address
    2. Look at the value contained in the cell
    3. Manually input this value into another cell

 

  • This is a lot of work, and prone to errors!
  • Cell references can avoid much of this work
Introduction to Google Sheets

Cell references

A city map with overlaid with a spreadsheet-like grid.

Introduction to Google Sheets

Cell references

A city map with overlaid with a spreadsheet-like grid, where map reference E3 is highlighted.

Introduction to Google Sheets

Cell references

  • Cell references point to cells and retrieve their values
  • Example: E2 point to A3

A city map with overlaid with a spreadsheet-like grid. Cell E2 is highlighted and pointing to cell A3.

Introduction to Google Sheets

Cell references

  • Cell references point to cells and retrieve their values
  • Example: E2 point to A3

  A spreadsheet showing the value, 12, in cell A3.

Introduction to Google Sheets

Cell references

  • Cell references point to cells and retrieve their values
  • Example: E2 point to A3

  A spreadsheet showing the value, 12, in cell A3.

  • Cell references can only be created inside formulas
Introduction to Google Sheets

Cell references

  • Cell references point to cells and retrieve their values
  • Example: E2 point to A3

  A spreadsheet showing the value, 12, in cell A3, and a cell reference to A3 in the cell, E2.

Introduction to Google Sheets

Cell references

  • Cell references point to cells and retrieve their values
  • Example: E2 point to A3

  The cell reference to A3 returned 12 - the same as the value in A3.

Introduction to Google Sheets

Cell references

  • Cell references point to cells and retrieve their values
  • Example: E2 point to A3

  Updating the cell value in A3 to 13.

Introduction to Google Sheets

Cell references

  • Cell references point to cells and retrieve their values
  • Example: E2 point to A3

  Cell E2 updated to the changes in cell A3 - now both cells display 13.

  • Cell references can be used to update cells in real-time
Introduction to Google Sheets

Circular references

  • What happens when a cell references itself?
  • Example: Update A3 to reference A3

The cell, A3, contains a cell reference to itself.

Introduction to Google Sheets

Circular references

  • What happens when a cell references itself?
  • Example: Update A3 to reference A3

When cell A3 referenced itself, it returned a reference error.

Introduction to Google Sheets

Circular references

  • What happens when a cell references itself?
  • Example: Update A3 to reference A3

The reference error showing that a circular dependency was detected.

Introduction to Google Sheets

Circular references

A diagram showing how cell A3 was referring to itself, which only contained a reference to itself and no value.

Introduction to Google Sheets

Circular references

  • What happens when a cell references itself?
  • Example: Update A3 to reference E2

The cell reference in A3 has been updated to E2, which contains a reference to A3.

Introduction to Google Sheets

Circular references

  • What happens when a cell references itself?
  • Example: Update A3 to reference E2

A circular dependency was detected when A3 used the reference E2, as E2 contained the reference, A3.

Introduction to Google Sheets

Circular references

  • What happens when a cell references itself?
  • Example: Update A3 to reference E2

 

  • Circular reference: a cell reference that directly or indirectly references itself
  • Formulas using circular references can't be computed

A diagram showing that any chain of references that results in a cell referring to itself will return create a circular dependency error.

Introduction to Google Sheets

Copying references

Copying cells into different rows, then different columns.

Introduction to Google Sheets

Copying references

A city map with overlaid with a spreadsheet-like grid. Cell E2 is highlighted and pointing to cell A3.

Introduction to Google Sheets

Copying references

A city map with overlaid with a spreadsheet-like grid. Cell E2 is highlighted and pointing to cell A3. When E2 is copied down by one, it will point to A4.

Introduction to Google Sheets

Copying references

A city map with overlaid with a spreadsheet-like grid. Cell E2 is highlighted and pointing to cell A3. When E2 is copied to the right by one, it will point to B3.

Introduction to Google Sheets

Copying references - vertically

  • Copying a cell reference vertically will shift the reference up or down
  • Example: Copy cell reference B5 from C5 to C4

Cell C5, containing a reference to B5, is being copied vertically, which shifts the reference by the same number of cells.

Introduction to Google Sheets

Copying references - horizontally

  • Copying a cell reference horizontally will shift the reference left or right
  • Example: Copy cell reference B5 from C5 to D5

Cell C5, containing a reference to B5, is being copied horizonally, which shifts the reference by the same number of cells.

Introduction to Google Sheets

Let's practice!

Introduction to Google Sheets

Preparing Video For Download...