Fixed Reference to a Table Column

By Wyn Hopkins

If you haven’t used Excel Tables before then you’re really missing out!

For those of you that do use them, you may have come across an issue where a formula that references a “Table” column, behaves slightly differently to other formula when being copied.
Here’s two options for copying a formula:

  1. Copying using Copy and Paste (fixed reference)
  2. Copying using Drag Handle (relative reference)

Problem: Sometimes you just want some columns to remain fixed (like adding a $ in front of a reference) and some to be “relative”. The normal $ convention doesn’t work with Tables.

Solution: If you want to fix a reference to say Colour, then rather than entering Table1

[Colour],  you use Table1[[Colour]:[Colour]]. Then when you use the drag handle to copy the formula across the Colour reference will remain fixed.

Fixed Reference to Table Columns_example(1)