MS Excel - how to fix a column in the formula when in named table

Question

In usual formula, we can fix like this:

=SUM($A$1)
=SUM($A:$A)

But how can I fix the entire column when I refer to named table? The formula without fix looks like:

=SUM(Table1[Field1])

I've tried to insert a dollar sign in different places, but Excel hasn't accepted it. How to fix?

Solution 1

Named ranges, like those used in data tables, are fixed (absolute) ranges by definition.

Let's assume the data in your table covers cells A2 to A5. In that case, the formula SUM(Table1[Field1]) is equivalent to SUM($A$2:$A$5).

The $ sign can only be used in relative addresses, like $A3:$X56.

Solution 2

I found this question while asking myself exactly the same question but the accepted answer appears to not be completely right.

There is a solution to lock a column in a formula, just force excel to believe it's treating your column like a range containing only itself:

=SUM(Table1[[Field1]:[Field1]])

Comments

[Laser42]

I thought too, that columns on named tables are fixed. But in my Office Excel 2016 maybe is a bug: when I drag a formula to next column, the column names in formula are changed relatively (this is not normal, probably a bug); when I copy and do special paste "formulas", column names in the formula don't change, while links to cells move (so, this is normal, as expected). If you can test both approaches in your Excel 2016, please let me know if you will reproduce this strange behavior from the first way (dragging formula).

[piko]

It is actually the normal behaviour of tables, and it differs from the relative ranges (i.e. A1:B5). Let's say your table has 2 columns (i.e. 'Units' and 'Sales'). If you drag a cell (=SUM(Table1[Units])) to the right, it will update to the next column of your table (i.e. =SUM(Table1[Sales])). However, if you copy and paste, the formula will remain the same. The range itself remains fixed, but by dragging the formula on the left/right, Excel automatically updates the formula. Try dragging it down: the formula won't be updated!

[Laser42]

Ok, thanks. But Microsoft could add a dollar sign in the formula, too

[piko]

That would be convenient in some occasions, indeed! The fallback would be to use relative ranges, but then you lose the advantage of the named ranges.