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’.
Did you find this tip usefull? Make sure to leave a comment or discover more during open Excel-trainings. In februari 2020, you can follow an exclusive Excel and Power Bicourse in Ghent.
