Structured References in Excel
When working with tables in Excel, structured references help make formulas easier to understand and use. It helps you refer to any parts of the table using its name instead of traditional cell referencing.
For a structured reference in Excel example, we have the following table.
💎 How to use structured references
1. Select E1, type Bonus, and hit Enter. Excel automatically formats the column for you.
2. Select cell E2 and type =0.02*[
3. Structured references in Excel tables are displayed as a list. Select Sales from the list.
4. Finish with a square bracket and press Enter.
Result: Excel automatically fills the formula into all the cells in the column.
Note: Click AutoCorrect Options, choose Undo Calculated Column, and the formula will be added only to cell E2.
You can add this table to a formula by following the steps below.
5. First, select a cell inside the table. Next, go to the Table Design tab. Under the Properties group, the table name appears as Table1.
6. Type the formula as shown below in cell E18.
Explanation: This formula sums all numbers in the Sales column. Excel will generate a list of structured references when you type =SUM(Table1[. Adding or deleting records will automatically update this formula.
2/7 Completed! Learn much more about tables ➝
Next Chapter: What-If Analysis