Dynamic formulas improve quality and development speed

In the Excel Core module we focused on functionality available in the Excel menus and on building dynamic workbooks using Excel Tables. The next step is working with dynamic formulas, which form the foundation of the Formulas module.

Tables make it possible to move beyond traditional cell-based formulas and instead build formulas that automatically adapt to changes in the data.

Structured References make dynamic formulas possible

Most people who use Excel know how to write formulas. A typical formula starts with an equals sign followed by a mathematical expression, often using functions such as SUM or VLOOKUP.

Traditional formulas typically reference individual cells (e.g. A1) or ranges (e.g. A:E). These are known as A1 references. While A1 references work well for simple formulas, they become difficult to read and maintain in larger models. They are also static, meaning that structural changes to the workbook can easily break formulas.

Instead of using A1 references, this course focuses on Structured References, which are used together with Excel Tables. Structured References refer to table columns rather than individual cells. When new data is added to the table, formulas automatically adapt to include the additional data.

This makes formulas easier to read, easier to maintain, and more robust as workbooks evolve.