To achieve the same with a Table, it is necessary to add more square brackets, a colon ( : ) and repeat the column name. If using the standard A1 style referencing we could add the $ signs and change the range from G2:G9 (a relative reference) to $G$2:$G$9 (an absolute reference). If copied to the right, it would revert to the first column in the table and would change to: =SUM(myTable) If copied to the left, it would change to: =SUM(myTable) If this were dragged or copied to another column, the formula would change automatically. Using the example data, to sum the Total column the formula would be: =SUM(myTable) When using structured references, whole columns are referenced with this syntax: tableName The Table name is myTable, whilst it’s not a great name, it will work for this example. It shows the costs a Safari Park might incur for owning different types of animals (I went to a zoo with the kids recently, so it’s on my mind, but I’ve made up the data purely for these examples). The examples in this post all use the following Table. But don’t worry, by the end of this post, you will learn that it is possible to switch between relative and absolute references even when using a Table. As a result, the $ symbol approach won’t work.
However, structured references don’t follow the same principles as the standard A1 style referencing system we usually use. With the introduction of Tables came a different (and more semantic) way to reference cells, called structured references. It freezes the row or column, so when copying a formula, the cell reference does not change. One of the first things we learn in Excel is the magic of the $ symbol.
0 kommentar(er)
