Why structured data enables analysis in Excel - Formula Trace Breakdown

Sample Data

This table shows a list of people with their names, ages, and cities. The data is structured in columns with headers.

Cell	Value
A1	Name
B1	Age
C1	City
A2	Alice
B2	30
C2	New York
A3	Bob
B3	25
C3	Los Angeles
A4	Carol
B4	35
C4	Chicago
A5	David
B5	40
C5	New York

Formula Trace

=AVERAGE(B2:B5)

Step 1: B2:B5

Step 2: AVERAGE(30, 25, 35, 40)

Cell Reference Map

    A       B       C
1 | Name  | Age   | City     |
2 | Alice | 30    | New York |
3 | Bob   | 25    | Los Angeles |
4 | Carol | 35    | Chicago  |
5 | David | 40    | New York |

Formula uses cells B2 to B5 for ages.

The formula references the Age column cells B2 through B5 to calculate the average age.

Result

    A       B       C
1 | Name  | Age   | City     |
2 | Alice | 30    | New York |
3 | Bob   | 25    | Los Angeles |
4 | Carol | 35    | Chicago  |
5 | David | 40    | New York |
6 |       | 32.5  |          |

Cell B6 shows the average age calculated from B2:B5.

The average age 32.5 is displayed in cell B6, showing how structured data allows easy calculation.

Sheet Trace Quiz - 3 Questions

What does the formula =AVERAGE(B2:B5) calculate?

A The oldest age in the list

B The average of the ages in cells B2 to B5

C The total number of people

D The sum of all ages plus city names

Key Result

AVERAGE(range) calculates the mean of numbers in a continuous cell range.