Summary  
This chapter covers using formula syntax—combining cell references, operators, and structured table references—to create calculated columns that automatically expand with new rows.  

General domain of usage  
Sales data analysis

The `Sales_Data` sheet will be used throughout the entire course, so let's start by creating the first calculated column inside the dataset.

Download the workbook and open:


Excel Workbook


Move to the first empty column on the right side of the table and create a new column named:

```text id="2"
Revenue
```



Step 1 Create the Revenue Column



Inside the first Revenue cell, type:

```excel id="3"
=G2 * 50
```

* `G2` → units value;
* `50` → fixed product price;
* `*` → multiplication operator.

Every formula in Excel starts with:

```text id="4"
=
```

Press Enter.

Because the dataset is formatted as an Excel Table, Excel automatically fills the formula down the entire column.



Step 2 Create Your First Formula


Replace the formula with:

```excel id="5"
=[@Units] * 50
```
`[@Units]` → units value from the current row.

Structured references are designed specifically for Excel Tables and make formulas easier to read and maintain.



Step 3 Replace the Formula with a Structured Reference



Add several new rows below the table and enter sample data.

Notice that:

* The table expands automatically;
* The Revenue formula is applied automatically to new rows.



Step 4 Test the Table Behavior


Clear the Revenue column values before moving forward.



Step 5 Prepare for the Next Chapter

What triggers Excel to treat a cell as a formula?

Why does the Revenue formula automatically fill down the column?

Build a complete Excel business analysis system using modern formulas, dynamic reporting, financial modeling, and scenario-based analysis. Throughout the course, you will transform raw transactional data into interactive dashboards, KPI engines, and scalable analytical models that reflect real-world business workflows.

Build the core Excel skills used in real business reporting workflows. Work with formulas, lookups, logical functions, conditional aggregation, error handling, and data cleaning while developing a structured sales analysis model.


Build dynamic Excel reporting systems using modern array functions and formula-driven workflows. Work with FILTER, SORT, SORTBY, UNIQUE, TRANSPOSE, spill ranges, interactive inputs, ranking systems, validation lists, and chart-ready data structures that update automatically as the dataset grows.


Build scalable relational Excel models using advanced lookup patterns and parameter-driven logic. Work with XLOOKUP, INDEX, MATCH, XMATCH, approximate matching, dynamic table relationships, scenario-based calculations, category-level aggregation, and flexible lookup architectures used in real analytical workflows.


Build advanced analytical and financial models that respond dynamically to changing data and assumptions. Work with time-based calculations, dynamic ranges, Named Ranges, installment modeling, investment analysis, amortization schedules, weighted aggregations, and scenario-driven financial metrics used in real business reporting systems.

Transform the workbook into a fully interactive business simulation system driven by scenarios, sensitivity analysis, and dynamic KPI logic. Build scenario engines, parameter-driven dashboards, sensitivity grids, advanced text-processing formulas, and centralized reporting layers that respond instantly to user inputs and business assumptions.

Building the Sales Dataset

1. What triggers Excel to treat a cell as a formula?

2. Why does the Revenue formula automatically fill down the column?