Table references in formulas in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a structured reference in Excel tables?

A structured reference is a special way to refer to Excel table parts by using table and column names instead of cell addresses. It makes formulas easier to read and understand.

beginner

How do you refer to an entire column named 'Sales' in a table named 'Orders'?

Use the structured reference: Orders[Sales]. This points to all data in the 'Sales' column of the 'Orders' table.

beginner

What does [@ColumnName] mean in a table formula?

It refers to the value in the 'ColumnName' for the current row where the formula is entered. The '@' symbol means 'this row'.

beginner

How can you refer to the entire table data (excluding headers) in a formula?

Use the table name alone, like Orders. This includes all rows and columns of the table data.

beginner

What is the benefit of using table references instead of regular cell references?

Table references automatically adjust when you add or remove rows. They also make formulas easier to read because they use names instead of cell addresses.

Which symbol is used to refer to the current row in a table formula?

A #

B @

C $

D &

The '@' symbol is used in structured references to mean 'this row' in the table.

How do you write a formula to sum the 'Amount' column in a table named 'SalesData'?

A =SUM(SalesData[Amount])

B =SUM(Amount)

C =SUM(#Amount)

D =SUM(@Amount)

Use the table name and column name inside brackets: SalesData[Amount].

What happens to structured references when you add a new row to the table?

A They ignore the new row

B They break and show errors

C They automatically include the new row

D You must update them manually

Structured references automatically adjust to include new rows added to the table.

Which of these refers to the entire table including headers and totals?

A TableName[#Totals]

B TableName[#Data]

C TableName[#Headers]

D TableName[#All]

The #All specifier includes headers, data, and totals.

In a formula inside a table, what does [ColumnName] without '@' mean?

A Refers to the entire column

B Refers to the current row only

C Refers to the header row

D Refers to the total row

Without '@', the reference points to the entire column in the table.

Explain how to use structured references to refer to a specific column and the current row in an Excel table formula.

Think about how you write formulas inside tables using names instead of cell addresses.

Describe the advantages of using table references in formulas compared to regular cell references.

Consider what happens when you add or remove data in a table.