- 1Why Do We Use Data Tables in the First Place?
- 2How Normal Data Table References Work
- 2.1Data Table Column References
- 2.2Data Table Example With Multiple References
- 3How to Build Locking References
- 3.1Example of Normal Relative and Absolute (Locked) Cell References
- 3.2Creating Absolute (Locking) References in Data Tables
- 4How to Build Locking References in Data Tables
- 4.1Sample Data
- 4.2Building a Formula that Refers to Locked Data Table Columns and Rows
- 4.31. Locking Entire Data Table Columns
- 4.42. Referencing the Current Row of a Locked Data Table Column
- 4.53. Locking a Data Table Header Reference
- 4.6Completed Data Table Formula
- 4.7Get the Excel Add-In to Map "Locking Data Table References" to F4
One of the most powerful features of Excel formulas is the ability to create absolute references that don’t move around when you drag to extend cell formulas or copy them to different places in your spreadsheet. Most Excel users figure out how to lock these references by either toggling through the options with the F4 key or using the
$ (dollar sign) symbol in front of the column and/or row references.
When you start using data tables in Excel, many users are frustrated to discover that the normal methods for freezing the row or column references don’t work! This can make it time consuming to build your spreadsheets when they use tables. Fortunately, there are some workarounds! We’ll walk through a few of them in this quick tutorial…
Why Do We Use Data Tables in the First Place?
If you are wondering why you’d even want to use data tables instead of just nicely ordered cells in Excel, it’s probably worth a quick refresher…
Data tables in Excel are useful specifically because they grow and change to accept new data when it is added to the table, without needing to update formulas or references. This is hugely valuable when you want to copy and paste data into a spreadsheet from an external source to keep it up-to-date. In a data table, you can create calculated columns that reference other parts of the data table. When new data is added, the calculated columns will automatically update with the new information and new rows!
Summary formulas like
AVERAGE() can be designed to reference the entire table or specific columns, and then automatically change to update their ranges when the tables grow or shrink!
The flexibility of data tables makes them the first choice for working with structured information, so that just means we need to learn how to work with them! Keep reading to find out how!
How Normal Data Table References Work
Data Table Column References
When you are working with data tables in Excel, the cell references look a bit different than the usual A1 letter-number combination for column-rows. This is because each row of the table acts like it is in its own 1-row spreadsheet. That means that the references only need to refer to the table name and the column. The row is assumed to be itself.
Let’s look at a simple table:
If we wanted to add up all of the Sales column in the data table, the formula would look like this:
Output for this formula would be:
Notice that it doesn’t ask for starting or ending row. It just asks for the column and table name.
Now, let’s add data to the table:
The formula for summing the Sales column stays the same:
Now, though, the output for this formula has updated automatically:
Without doing anything, the calculations accommodate extra data! No need to change references in the SUM() formula!
Data Table Example With Multiple References
Let’s show one more formula example using the same table:
Instead of creating a formula outside the table, let’s add a calculated column to add up the total for each Region. Name a new column to the right called Region Total. In the first cell of the column, enter the following formula:
It should look like this:
Note that the final term – the criteria for the SUMIFS() – uses the
@ (“at” symbol). This notation means you are referring to the value of the Region column in the current row. Just press ENTER to populate that formula in the entire column, and you can see the result of this reference in the completed table.
The SUMIF() calculation sums all the Sales for each Region, giving a matching total for every row from the North region (and for each of the others as well).
Now that you can see how powerful Excel data tables can be, you’ll probably want to use them… To build complex formulas quickly and reliably using data tables, you need to be able to lock references, so that’s what we’ll learn next… Click onward!
How to Build Locking References
Normally, it’s easy to build out columns and rows by locking references in formulas using F4 or adding dollar signs to the row and column of the reference… This lets you drag formulas down multiple rows or across multiple columns to quickly build a working spreadsheet.
Example of Normal Relative and Absolute (Locked) Cell References
Take this simple example:
Here, we build a formula to calculate quarterly revenue based on volume and unit price. The volume will change each quarter in the new column, but the unit price will stay the same, so we lock the reference by pressing F4 or typing the dollar signs for
Once the formula is entered, we click in the lower right corner to drag it across the columns for the other quarters.
When we release the click, the formulas for each quarter are calculated. The cell reference for the volume cell (in blue) stays a “relative” reference that moves with the location of the formula cell. The reference for the unit price (in red), however, has become “absolute”, meaning it is locked to it’s original location.
Creating Absolute (Locking) References in Data Tables
Unfortunately, using F4 or adding dollar signs doesn’t lock references in data tables. It doesn’t even calculate the cell, instead throwing an error. Fortunately, there is a way to achieve the same behavior, but it requires using Excel’s data table syntax we spoke about above.
The easiest way to show how this is done is to walk through an example. Let’s combine the two sample data structures we’ve been working with to design a problem to solve in the next section. Click Next to continue!
How to Build Locking References in Data Tables
Let’s expand our Regional Sales table to a full 6 month period. This might be how your data could look if you were importing it from a database or other reporting software. It’s very machine-readable but not very human-readable. In this example, we are going to name the table in Excel as
Now, let’s build a table to pivot the data into a more readable format. In this case, our goal is to have a separate column for each month and a separate row for each region:
This table will be called
PivotTable. To fill this table with data, we are going to use another simple SUMIFS() formula, but we will be working inside a table and looking up information inside another table…
Building a Formula that Refers to Locked Data Table Columns and Rows
Starting in the North column and the Jan-16 row, build this formula:
It will look like this:
We’re locking references in three separate ways in this formula:
- Referring to an entire locked column in a data table.
- Referring to the current row of a locked column in a data table.
- Referring to the locked header row of a data table.
Each of these uses slightly different syntax, so I’ll show each of them in order:
1. Locking Entire Data Table Columns
Normal data table column references look like this when you are inside the same table:
When you are outside the table the column resides in, they look like this:
The trick to locking the reference for the column involves treating it like a range of columns that only includes itself. You must also always reference the table name, even from within the table. Thus, the locked data table column syntax looks like this:
2. Referencing the Current Row of a Locked Data Table Column
Normal current row references for data tables look like this when you are inside the same table:
When you are outside the table the column resides in, they look like this:
Similar to locking the entire column, as seen above, the trick to locking the current row reference is to treat it like a range, but the @ symbol is only used in the beginning of the range reference:
3. Locking a Data Table Header Reference
Working with Excel data table headers is a bit difficult because Excel treats them like fixed elements. That means it won’t remember that they are dates or other special types of data. They become field names. The assumption is that the number of columns in your data table won’t change over time – just the number of rows.
This means that to reference a header in a data table in a formula (and let that formula stay drag-able), you need to lock the reference. Fortunately, the header row of a data table always stays in the same place, so you can lock it like a normal cell reference in Excel. In the example formula above, that’s exactly what we did:
This notation ensures that when the formula is dragged across, it will reference Row 1 in the current column at all times. (The row number is locked, but the column is still relative.)
Completed Data Table Formula
After dragging the formula down and across your new PivotTable data table, you should see the equation change dynamically to populate the rest of the cells.
The new formula in cell E7 reads:
Done correctly, the formula looks very much the same in each cell of the data table! The only thing that will change are references to the header row of the data table, since these are normal locking references for Excel cells.
This is the final data table, complete with pivoted information. It’s much easier to read, and can be used to build charts and other reporting outputs as necessary.
Get the Excel Add-In to Map “Locking Data Table References” to F4
This process of adding ranges to all your data table references to make them absolute might be a hassle if you need to do it often… Fortunately, Jon Acampora over at Excel Campus has built an add-in that will map these locking ranges to the keyboard as F4 so they work just like normal absolute references in Excel.
You can check out his add-in here: Absolute Structured References in Excel Table Formulas
Get the latest Excel tips and tricks by joining the newsletter!
Andrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it.
Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!
24 thoughts on “How to Lock Cell Formula References for Table Columns in Excel”
Hello Jeff! I have an issue with a formula where a cell need to be locked in a formula used to delete duplicates from column [Key].
The formula returns 1 for the duplicates (Except the first one found) and 0 for the non duplicates.
This is the formula:
What I would like to do is to replace the $I$11 with the named column [Key].
What I have so far is:
Which almost does the trick but not quite, because I can’t put 0 for the first duplicate found.
Could you help me with this?
Might try this
Unique values will be 0, duplicates will be 1
Very good explanation. Exactly what I was looking for and the DataTable[[Header]:[Header]] solved my problem when filling formula to the right. Thank your for all this info!
I want to lock coloumn c where exists the formula for adding up coloumn a and b pls help me
There’s a mistake. Locking should be PivotTable[@[Month]:[Month]] not PivotTable[[@Month]:[Month]]
Finally fixed in the article! Thanks for the call out!
There’s a quicker way, if you drag-fill the formula and then choose ‘Fill without formatting’ from the dialog box, it keeps all the column references the same.
i have a formula, =Ordersinhouse!H126, that fills in a cell, i want to fill in the cell with this data but want to block/lock keyboard entries in this cell, is this possible?
Lock the cell containing the formula and protect the sheet.
i have locked the cell and protected the sheet, =Ordersinhouse!H126
problem is that when information is enter a number on Ordersinhouse page the information will not fill in on the second page where i do not want keyboard entry
the Ordersinhouse page is empty when i start, i fill in an order which is to be filled in on the ship page where the formuls =Ordersinhouse!H126 is located, the data does not fill in because the cell is locked
The fact that the cell is locked and the sheet is protected isn’t related to the formula not being calculated. I would suggest you to check that Calculation Mode is set to Automatic. It’s probably now set on Manual (In Office Ribbon >>> Formulas >>> Calculation Options).
i have tried this, the problem still is that once i lock the formula will not fill in from the ordersinhouse page when data is entered on first page.
i have made a short copy of the files i have done, is there a way i could send you a copy of the file so you could show me how to fix the issue i am not smart enough to fix on my own.
i have not locked of protected the cells on this file, i have added some color options when i fill in
Sure, I can take a look if you send me your file, just click on the hyperlink of my name and send it through the email you find in my website.
Please add the summary of the problem to the body of the email when sending the file to me.
So all you have to do is select the header not the entire column and lock it with F4. I am having the same issue currently and saw your comment here. Please help if you can
I am creating a table wich contains information that need to be upgraded each day but I dont want to modify the data for yestarday ( copy exactly) and do continue with tody and the future with the formule Excel
Strangely enough, if you pull the cursor the formulas will not be locked, but if you do a copy/paste they will… so no need for the more complex syntax in most cases!
As pointed out above by Benjamin in 2018 there’s an error –
“There’s a mistake. Locking should be PivotTable[@[Month]:[Month]] not PivotTable[[@Month]:[Month]]”
Please can this article be updated? I’ve just spend a fair bit of time trying to get the incorrect version to work!
Updated! Thanks for calling it out!
I have a range A2:A99 formatted as TABLE with column heads being the names of persons in raw#2: Let’s say they are PersonA, PersonB, .., PersonZ. And my table goes down to row#99. And I want to count in raw#1 how many “X”s each person has in his/her column using the formula =SUBTOTAL(103,Table1[PersonA]) . So far no problem, yet: when I copy that cell to its cells on the right, I would like to see =SUBTOTAL(103,Table1[PersonB]), … (=SUBTOTAL(103,Table1[PersonZ]). Yet, it does copy always the same formula of =SUBTOTAL(103,Table1[PersonA]) to all the neighbouring cells. Is there a way to overcome this, please? Any assistance you can give will be greatly appreciated.
a1=10, b1=20, c1=a1+b1=30.
If I move A1 or B1 Then C1 should be A1+B1 only. i.e. references should not be changed.
This is only article I could find online with a clear explanation of locking table references and I use it daily! I’ve now come across a SUMPRODUCT formula where I would like to lock the last array:
=SUMPRODUCT((tbl_data[Code]=”GRAND TOTAL”)*($C$3:$S$3=I13)*(tbl_data[[A]:[Grand Total]]))
Where I have an snapshot from a pivot table with a list of codes down the left, and letters as headers like this:
Code A B C Grand total
123 5 10 3 18
232 2 6 4 12
223 4 5 5 14
Grand total 11 21 12 44
How would you suggest I lock the last array ([[A]:[GRAND TOTAL]]) without changing it to the cell reference?
Thanks so much,
You’re using the term “LOCKED” when you really mean “ABSOLUTE” … LOCKING is a whole different thing.
I need help with this formula. It works only when the Start Date = 9/1/2022
=IF(Budget3[[#Headers],[9/1/2022]]>=Budget3[@[Start Date]:[Start Date]],Budget3[@[SW Mnthly Spend]:[SW Mnthly Spend]],””)
The cells containing a date are set up as “Date”