Spreadsheet cells, formulas and relative copies

A spreadsheet cell is in column B and row 3. A reference writes the column letter first and the row number second. Which reference locates it?

Keep the letter and number in the stated order

A cell address is not its stored value

This synthetic sheet records completed-item counts for two days. Column letters and row numbers locate cells; the displayed cell contents are separate

B3 is the cell containing 5, not a multiplication of B and 3. A3 contains the text label Elm

Row 1 contains headings such as Monday. The counts begin in row 2

In this spreadsheet notation, a leading = starts a formula. The formula =B2+C2 adds the two referenced counts

Read first, then add

This complete synthetic spreadsheet records counts of completed items. The letter at the top identifies a column and the number at the left identifies a spreadsheet row

A reference such as B2 means the value in column B, row 2. Row 1 contains text headings; the numeric data are in rows 2 to 5

A formula starts with =. The + operator adds the referenced values; SUM(first:last) adds every cell in the rectangular range, including both endpoints

Evaluate this formula without changing any input cell: =B2+C2

What numeric result does the formula calculate?

The referenced cells are B2, C2

B2 = 3; C2 = 2

Add the referenced counts: 3 + 2 = 5 items

Use both references in the requested row

Choose a row from 2 to 5. Predict the result of adding that row's B and C values, then check the readout

With row 5 selected, which calculation gives the displayed total?

A colon includes the complete range

SUM(B2:B5) adds B2, B3, B4 and B5. It includes the endpoints and every cell between them, not just B2 and B5

SUM(B3:C4) covers the rectangle B3, C3, B4 and C4

Use the exact range requested. SUM(B3:B5) omits B2 because its starting row is 3

Every referenced cell in these calculations contains a supplied whole-number count. Other data types and spreadsheet error rules are outside this lesson

Add every referenced count

This complete synthetic spreadsheet records counts of completed items. The letter at the top identifies a column and the number at the left identifies a spreadsheet row

A reference such as B2 means the value in column B, row 2. Row 1 contains text headings; the numeric data are in rows 2 to 5

A formula starts with =. The + operator adds the referenced values; SUM(first:last) adds every cell in the rectangular range, including both endpoints

Evaluate this formula without changing any input cell: =SUM(B2:B5)

What numeric result does the formula calculate?

The referenced cells are B2, B3, B4, B5

B2 = 3; B3 = 5; B4 = 2; B5 = 4

Add the referenced counts: 3 + 5 + 2 + 4 = 14 items

Keep the correct column

Respect the starting row

Copy relative references by the same displacement

The references in this lesson have no $ signs and are relative. When a formula is copied down one row, every referenced row number increases by one

Copying down keeps the column letters unchanged. Copying down two rows adds two to every relative row reference

A copy is not a move, an edit to the original formula or a change to the input cells

Keep the supplied operator order and range structure when predicting the direct copy. This is not a task to invent a different equivalent formula

Copy both references

Cell D2 contains the formula =B2+C2. Copy that formula to D3 in the same column; do not move the original cell or edit its input cells

All references shown are relative: there are no $ signs. Copying down by a number of rows adds that number to every referenced row number and leaves the column letters unchanged

Preserve the original operator or SUM range structure; choose the direct copied formula rather than rewriting it as an equivalent expression

Which formula appears in D3 after this copy?

The downward shift is 3 − 2 = 1 row

The first reference changes from B2 to B3; the second changes from C2 to C3

Keeping the original structure gives =B3+C3

Apply the shift to the next row

Use the full displacement

Both range endpoints are relative references

Copying =SUM(B2:B3) down one row gives =SUM(B3:B4). Both endpoint row numbers increase

A horizontal or rectangular range follows the same rule: change every relative row reference by the displacement and leave its column letter unchanged

The formula's new references determine its new result. A copied formula is not merely a pasted display value

Shift a horizontal range

Shift both endpoints of a rectangle

A cell contains =B2+C2 and displays 5 because B2 is 3 and C2 is 2. Under this model's automatic recalculation rule, B2 is changed to 4 and the formula is left unchanged. What does the formula cell display now?

Read the updated referenced value and recalculate the unchanged formula

Locate, calculate and shift references

Use column letters and row numbers to locate the actual stored values

A SUM range includes every cell inside its stated endpoints

When copying a relative formula down, increase every row reference by the same displacement without changing column letters