SOLVED

Locking a column in a table formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2028085%22%20slang%3D%22en-US%22%3ELocking%20a%20column%20in%20a%20table%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2028085%22%20slang%3D%22en-US%22%3E%3CP%3Ein%20a%20normal%20excel%20formula%20(not%20in%20a%20table)%2C%20I%20can%20lock%20a%20cell%20reference%20with%20a%20%24%2C%20e.g.%26nbsp%3B%20%24A%241%20locks%20in%20the%20cell%20to%20copy%20the%20formula.%26nbsp%3B%20How%20do%20I%20accomplish%20this%20within%20a%20table%20formula%20to%20lock%20in%20a%20column%2C%20say%20column%20A%20in%20a%20table%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2028085%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2028160%22%20slang%3D%22en-US%22%3ERe%3A%20Locking%20a%20column%20in%20a%20table%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2028160%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F881994%22%20target%3D%22_blank%22%3E%40Timothy_Neumann%3C%2FA%3E%26nbsp%3B%20You%20can%20read%20more%20about%20locking%20cells%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fswitch-between-relative-and-absolute-references-981f5871-7864-42cc-b3f0-41ffa10cc6fc%23%3A~%3Atext%3DIf%2520you%2520want%2520to%2520maintain%2CThis%2520is%2520an%2520absolute%2520reference.%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Ehere%3C%2FA%3E%3A%3C%2FP%3E%3CP%3E%24A%241%20locks%20columns%20and%20rows%3C%2FP%3E%3CP%3E%24A1%20locks%20columns%20only%3C%2FP%3E%3CP%3EA%241%20locks%20rows%20only%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2028321%22%20slang%3D%22en-US%22%3ERe%3A%20Locking%20a%20column%20in%20a%20table%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2028321%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F881994%22%20target%3D%22_blank%22%3E%40Timothy_Neumann%3C%2FA%3E%26nbsp%3BI%20believe%20you%20can%20find%20the%20answer%20to%20your%20question%20here%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceloffthegrid.com%2Fexcel-tables-absolute-references%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceloffthegrid.com%2Fexcel-tables-absolute-references%2F%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2029179%22%20slang%3D%22en-US%22%3ERe%3A%20Locking%20a%20column%20in%20a%20table%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2029179%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20that%20link%2C%20exactly%20what%20I%20was%20looking%20for%2C%20great%20explanation%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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? 

4 Replies

@Timothy_Neumann  You can read more about locking cells here:

$A$1 locks columns and rows

$A1 locks columns only

A$1 locks rows only

best response confirmed by Timothy_Neumann (New Contributor)
Solution

@Timothy_Neumann I believe you can find the answer to your question here:

https://exceloffthegrid.com/excel-tables-absolute-references/ 

@Riny_van_Eekelen 

 

Thanks for that link, exactly what I was looking for, great explanation

@adversi 

 

The $ doesn't work in a table formula, structured reference