Structured references behave differently from other references when copied. Let’s look at some examples. First, notice if I copy and paste a formula that contains structured references, column names won’t change. For example, if I add a new column to the end of this table, then copy and paste the tax formula into the new column, Excel brings in the same formula without changes. 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. However, If I drag copy the formula from the tax column, Excel changes the column name like a regular cell reference. If you need to lock a column reference in a table, how do you do it? Normally, you’d use a dollar ($ )sign, but this won’t work in a structured reference. 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 the formula, then edit as needed. For example, in this case, I’ll enter the same formula but select both the total and tax columns. 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. To calculate a forecast, I’ll enter a simple formula that multiplies current sales by the column header + 1. If I try to copy and paste the formula, neither reference changes. And, if I drag copy the formula to the right, the header changes like I want, but the Sales column also changes, which isn’t going to work. 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. Finally, notice that although the formula gets the percentage value directly from the header, I can change the percentages as I like, and the formula continues to work. This is a nice benefit of structured references. Excel tracks changes to table and column names and adjusts formulas automatically as needed.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
