Forum Discussion
Locking a column in a table formula
in a normal excel formula (not in a table), I can lock a cell reference with a $, e.g. $A$1 locks in the cell to copy the formula. How do I accomplish this within a table formula to lock in a column, say column A in a table?
Timothy_Neumann I believe you can find the answer to your question here:
https://exceloffthegrid.com/excel-tables-absolute-references/
5 Replies
Timothy_Neumann I believe you can find the answer to your question here:
https://exceloffthegrid.com/excel-tables-absolute-references/
- For anyone reading this: using this trick has massive performance downfalls when used repeatedly in big(-ish) tables. Just spent 2-3 hours fighting performance issues with a file that had a ~500-row table and ~100 columns, where even just opening the file took about 2 min. Removing those "fake range" references eliminated the issue completely.
Timothy_Neumann You can read more about locking cells https://support.microsoft.com/en-us/office/switch-between-relative-and-absolute-references-981f5871-7864-42cc-b3f0-41ffa10cc6fc#:~:text=If%20you%20want%20to%20maintain,This%20is%20an%20absolute%20reference.:
$A$1 locks columns and rows
$A1 locks columns only
A$1 locks rows only
