Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a simple Excel table containing a formula that is referencing columns in another table, and is using the automatic table-range-names (not sure what they're correctly called)

=A1 + SUM(MyOtherTable[ColumnA]) * SUM(AnotherTable[AnotherA])

The problem is that when I copy that formula to the right, the column references all shift right too, so becoming:

=A1 + SUM(MyOtherTable[ColumnA]) * SUM(AnotherTable[AnotherA])
=B1 + SUM(MyOtherTable[ColumnB]) * SUM(AnotherTable[AnotherB])
=C1 + SUM(MyOtherTable[ColumnC]) * SUM(AnotherTable[AnotherC])

How can I stop this? I can't find an equivalent to the dollar-references like $A$A. Note that I do want the normal behaviour for the RC references.

The only alternative I can think of is to created named ranges for the columns in the other tables, although this seems to defeat the object to some extent of the table-ranges.

share|improve this question
up vote 3 down vote accepted

Instead of dragging, try copy-and-pasting. That works in Excel 2007/2010.

share|improve this answer
Oh. Simple. Thanks. – Cylindric May 16 '12 at 13:59

The way to specify a whole column is with the reference


That will give you a fixed whole-column reference.

Personally, I find Excel much easier to work with when I switch to the R1C1 reference format (Tools/Options/General). Then you can refer to a fixed column as:


Or a relative column as


This mode makes it very easy to refer to the current row or column:


would be the cell on current row, column 2.

share|improve this answer
Thanks, I'm aware of using the traditional addressing methods, I was more curious about using the "new way" effectively. Table ranges seem a bit buggy though. I had a named-range set to AnotherTable[AnotherA], but when I use that in a formula, dragging the formula changes the named range – Cylindric May 16 '12 at 11:02
Oh, doh! I didn't spot you were referring to table references - silly me - sorry. The names in tables come from the column headers & I don't think you get any control at all as you've discovered. I think that this is because they originally came from Pivot Tables where you can't arbitrarily drag cells around, only whole data columns, for formulas you have to create pivot table formulas. – Julian Knight May 16 '12 at 11:18

If you still need the answer modify your formula as below:

A1 + SUM(MyOtherTable[[ColumnA]: [ColumnA]]) * SUM(AnotherTable [[AnotherA]: [AnotherA]])

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .