How to copy and lock structured references

Structured references behave differently from other references when copied.

Let’s look at some examples.

This is true even if I copy and paste outside the table.

Excel adds the Table name to the formula, but the column name remains the same.

If you better lock a column reference in a table, how do you do it?

One trick is to use the multiple-column syntax with a single column.

The simplest way to do this is to select two columns when creating theformula,then edit as needed.

Then I’ll change “Tax” to “Total”.

This effectively locks the column.

When I drag to the right, the formula calculates the same results.

Back in the table, it works the same way.

Let’s look at a more useful example.

This table shows regions in the first column and current sales numbers in the second column.

The next three columns are meant to hold sales forecasts.

If I make a run at copy and paste the formula, neither reference changes.

To allow drag copying, I need to lock the sales column by using the multiple column syntax.

Then I can drag the formula to the right and get correct results.

This is a nice benefit of structured references.

Excel tracks changes to table and column names and adjusts formulas automatically as needed.