Excel Cookbook

Chapter 2. References and Structured Data

References are intrinsic to nearly all spreadsheets since most Excel features depend on them. For example, formulas rely on cell references and ranges to perform calculations, tables use structured references that automatically expand and contract as rows are added and deleted, and using absolute, relative, or mixed references can affect how features such as Auto Fill, conditional formatting, and data validation behave.

This chapter covers when and how to use these different types of references and techniques for working with structured data. Recipes include formula-based conditional formatting, custom data validation, using static and dynamic named ranges to create cascading drop-down lists, adding groups and subtotals, and tables.

2.1 Using Relative and Absolute References

Problem

You want to control whether a cell reference in a formula can change when you copy the formula to another cell.

Solution

Relative, absolute, and mixed cell references control how a formula changes when it’s copied.

A relative cell reference—for example, A1—can change when you copy it to another cell, and it’s useful, for example, if you want to perform a calculation for each row or column in a range. So if cell C1 contains the formula =A1*B1 and you use Auto Fill (see Recipe 1.12) to copy it to the range C2:C3, this puts the formula =A2*B2 in C2 and =A3*B3 in C3. Similarly, if cell D1 contains the formula =SUM(A1:B1), copying it to D2:D3 puts =SUM(A2:B2) ...