Excel: How to lock a column in table formulas

Can’t get enough of Excel? Good! In this blog post, we’ll teach you another Excel trick. When Microsoft introduced tables in Excel in 2007, it became impossible to use the dollar sign ($) to lock rows or columns. This isn’t much of a problem for rows, but what about columns? Read on – I’ll explain how you can still lock these.

Every Excel user knows what absolute/relative references are and what the dollar sign ($) does. It’s an Excel staple, just like F4 – the most well-known function key. If you want to build a decent formula, you’ll need to lock rows or columns. For new Excel users, it may be strange to use the dollar sign at first, but as soon as you understand its purpose, it’ll help you drive your Excel solutions to the next level.

When Microsoft introduced tables in Excel (2007), the formula notation system changed significantly. Before this change, dollar signs were used in cell references or to block rows and columns, but this doesn’t work anymore. Square brackets ([, ]) and the @ sign were added to increase the readability of formulas for end users. Tables now update rows in formulas automatically with the @ sign, but there’s no standard solution for columns. Luckily, you can also block columns with an alternative notation.

The usual formula notation has several ways to include cell references.

  • The A1 cell can be used in a formula with the following notations: A1 / A$1 / $A1 / $A$1
  • The function key F4 enters the dollar signs in the correct spots after selecting a cell (or several cells). You can do this by pressing the key several times.

To illustrate, here’s an example of the old notation compared to the new notation. After that, we’ll look at the solution to block columns when working with tables.

The cells under the yellow column headers have to multiply the relevant cells under the green column headers by a fixed factor for the city mentioned in each row.

In this solution, C6 should be blocked on the column level, which results in $C6.

This lets you copy the formula to all cells in the destination area without further adaptations.

Tip: Select the entire destination area (H6:K9) and start typing the formula. Use CTRL+ENTER when you’re finished. This copies the formula to all cells in the destination area automatically without changing the formatting of these cells.

If you apply a table to the area and then use the same example, you’ll notice there’s no straightforward way to block column C.

The new notation for tables only works by clicking the relevant cell.

After entering the formula, Excel automatically copies your formula up to the last cell of the column. This is a major improvement compared to the old method.

A disadvantage of this new notation arises when you try to copy the formula to the neighbouring columns. As you can see in the example below, the reference for each cell changes as well, while the [@Factor] reference should’ve been blocked. This can’t be solved with a dollar sign.

The desired result is =[@Summer]*[@Factor], but both cells have shifted the same way.

You can solve this by including the ‘Factor’ reference in the formula in another way.

Instead of [@Factor], you’ll need to write tblSeason[@[factor]:[Factor]].

In this notation, you start with the table name. Excel will automatically correct this if you should forget the table name. Just open a square bracket and use the @ sign for the row reference (context). After that, indicate the column name followed by a colon (:), and enter the column name in the formula again.

If you drag the formula to the right now, the reference to the ‘Factor’ column will stay locked, while the ‘Spring’ column will change to ‘Summer’, ‘Fall’ or ‘Winter’.

Discover more tips during one of our Excel courses.

Share this blogpost
Categories: Excel
Tags: Excel

Also interesting for you

Comments

David moreno
12/02/2020

how to lock especific Ranges without losing the table reference, if I enter new data, the table continues and the cell range remains blocked?

Caue
03/11/2020

What if I'm using a SUMIF formula referencing another table?

pieter
03/11/2020

Hi Caue,
when reffering to another table, the table name is introduced in front of the column name(s). It should stay the same .
=SUMIFS(Table1[[Jan]:[Jan]];Table1[[Code]:[Code]];$D17)

Andreyepo
15/11/2020

удалите,пожалуйста!

Svetlanaswi
16/01/2021

urenrjrjkvnm

Ivanpou
18/01/2021

news

Svetlikf
19/01/2021

Novyny

Igorxtr
21/01/2021

Ukraine

Evayxg
23/01/2021

Med

Davidvqj
24/01/2021

coin

Davidvqj
24/01/2021

coin

Jannaejb
11/02/2021

coin

Veronajrf
11/02/2021

urenrjrjkvnm

Serzxcq
12/02/2021

coin

Ilushikcqp
12/02/2021

urenrjrjkvnm

Serggyj
13/02/2021

Novosti

Ivaneiv
13/02/2021

news

Viktoriywg
13/02/2021

Cinema

Svetlanahdg
13/02/2021

urenrjrjkvnm

Leonxdg
14/02/2021

urenrjrjkvnm

Igoroeq
14/02/2021

Ukraine

Sergafc
15/02/2021

Novosti

Alexliq
16/02/2021

coin

Svetlanastt
16/02/2021

urenrjrjkvnm

Alexcus
16/02/2021

coin

Sergqxu
16/02/2021

Novosti

Svetlanayok
17/02/2021

urenrjrjkvnm

Ivandij
17/02/2021

news

Alexmck
17/02/2021

coin

Sergbul
17/02/2021

Novosti

Serzihd
18/02/2021

coin

Alexvmw
18/02/2021

coin

Igornex
18/02/2021

Ukraine

Veronabih
19/02/2021

Life

Veronazev
19/02/2021

urenrjrjkvnm

Ilushikszq
19/02/2021

urenrjrjkvnm

Evaudg
19/02/2021

Med

Leonbjm
20/02/2021

urenrjrjkvnm

Davidqnk
20/02/2021

coin

Ilushikxum
20/02/2021

urenrjrjkvnm

Svetlmqn
20/02/2021

Novyny

Igorkem
20/02/2021

Ukraine

Viktoriuyu
20/02/2021

Cinema

Viktorihzf
21/02/2021

Cinema

Davidxdf
21/02/2021

coin

Davidisu
21/02/2021

coin

Viktorixpk
21/02/2021

Cinema

Veronawqo
22/02/2021

Life

Davidarh
22/02/2021

coin

Leonqse
22/02/2021

urenrjrjkvnm

Serzyrn
22/02/2021

coin

Veronaahf
23/02/2021

Life

Evabbw
23/02/2021

Med

Svetlajd
24/02/2021

Novyny

Leonzju
25/02/2021

urenrjrjkvnm

Veronakfm
26/02/2021

urenrjrjkvnm

Serzufo
27/02/2021

coin

Veronaavu
27/02/2021

urenrjrjkvnm

Svetljbg
28/02/2021

Novyny

Evadik
01/03/2021

Med

Stasencdc
19/03/2021

urenrjrjkvnm

Stasenpor
19/03/2021

urenrjrjkvnm

Vikiwor
19/03/2021

urenrjrjkvnm

Julibrr
19/03/2021

Novosti

Stasenopb
20/03/2021

urenrjrjkvnm

Juliich
20/03/2021

Novosti

Vikirnl
20/03/2021

urenrjrjkvnm

Stasencla
21/03/2021

urenrjrjkvnm

Juliclr
21/03/2021

Novosti

Vikipox
21/03/2021

urenrjrjkvnm

Julitys
21/03/2021

Novosti

Vikitnk
22/03/2021

urenrjrjkvnm

Vikihze
23/03/2021

urenrjrjkvnm

Tolikjfu
23/03/2021

Novosti

Tolikpgx
23/03/2021

Novosti

Toliksxm
24/03/2021

Novosti

Toliktbu
25/03/2021

Novosti

Tolikdew
26/03/2021

Novosti

Tolikrwy
16/04/2021

Novosti

Julizon
16/04/2021

Novosti

Julilgp
23/12/2021

urenrjrjkvnm

Julilgp
23/12/2021

urenrjrjkvnm

Julisko
24/12/2021

urenrjrjkvnm

Julisko
24/12/2021

urenrjrjkvnm

Julivzs
25/12/2021

urenrjrjkvnm

Julicos
25/12/2021

urenrjrjkvnm

Levdwq
31/12/2021

urenrjrjkvnm

Levdwq
31/12/2021

urenrjrjkvnm

loveawake.ru
22/01/2022

Welcome to the world of adult Dating loveawake.ru

loveawake.ru
28/03/2022

Welcome to the world of adult Dating loveawake.ru

Leave a reply

Your email address will not be published. Required fields are marked.

Bring your knowledge and skills to a higher level

Subscribe for our monthly newsletter and receive:

  • Exclusive tips & tricks
  • Information about our courses
  • Trends on courses
  • Invitations to seminars and events

Most popular courses