Purpose
By the end of this lesson, you will be able to describe how a spreadsheet is organized and use correct cell and range references in business work.
Lesson Explanation
A spreadsheet is organized into workbooks (the whole file), worksheets (individual tabs within it), and cells (the individual boxes identified by a column letter and row number, like A1 or C15).
A range is a group of cells, written with a colon between the first and last cell: B2:B10 means every cell from B2 down through B10. Ranges can also span columns, like A1:D5, a rectangular block of 20 cells.
Cell references can be relative or absolute. A relative reference like B2 shifts when copied to a new cell (copying it one row down becomes B3). An absolute reference, marked with dollar signs like $B$2, stays locked on that exact cell no matter where the formula is copied – essential when a formula needs to always point to the same value, like a tax rate stored in one fixed cell.
Worksheets within the same workbook are referenced by name followed by an exclamation mark: Sheet2!A1 refers to cell A1 on a tab named “Sheet2.” This matters constantly in real business work, where data often lives on one tab and a summary or report lives on another.
Practice Questions
1. A formula in cell C3 is =B3*1.1. If this formula is copied down to C4, what does it become, and why?
View Answer
=B4*1.1 – because B3 is a relative reference, it shifts by the same amount the formula was moved (one row down).
2. A company stores its tax rate in cell $B$1 and wants every row’s formula to always reference that same cell, even when copied down 50 rows. What kind of reference should be used, and why?
View Answer
An absolute reference ($B$1) – the dollar signs lock the reference so it doesn’t shift when copied, unlike a relative reference.
3. What does the range A1:D5 refer to?
View Answer
A rectangular block of cells spanning columns A through D and rows 1 through 5 – 20 cells in total.
4. A formula on the “Summary” tab needs to pull a value from cell A1 on a tab named “RawData.” How would this cross-sheet reference be written?
View Answer
RawData!A15. A formula =A2+B2 is copied from row 2 down to row 10. Will it correctly calculate =A3+B3, =A4+B4, and so on for each row? Why?
View Answer
Yes – both A2 and B2 are relative references, so both shift consistently as the formula is copied down each row.
6. A financial analyst accidentally used a relative reference instead of an absolute one for a company-wide discount rate, and every row now points to a different, mostly blank cell after copying. What is the most likely fix?
View Answer
Change the reference to an absolute reference (adding dollar signs, e.g., $D$1) so it stays locked on the correct cell regardless of where the formula is copied.
7. What is the difference between a workbook and a worksheet?
View Answer
A workbook is the entire file; a worksheet is one individual tab within that file.
8. A formula needs to reference cell B5 on the current sheet, and that reference should shift normally when copied to other cells. Should this use a relative or absolute reference?
View Answer
A relative reference (B5, no dollar signs) – since it should shift naturally with each copy.
9. A report combines data from three different tabs: “Q1”, “Q2”, and “Q3.” Write a reference to cell C10 on the “Q2” tab.
View Answer
Q2!C1010. A user wants a formula in cell E1 to always multiply by the value in cell A1, no matter where the formula is later copied to. What should the reference to A1 look like?
View Answer
$A$1 – an absolute reference locks the cell so it does not shift when copied.
11. A spreadsheet has row headers in row 1 and data starting in row 2. Write the range reference that would select all the data in column B, from the first data row through row 100.
View Answer
B2:B10012. Why does understanding the difference between relative and absolute references matter specifically when copying a formula down many rows in a large dataset?
View Answer
Using the wrong type can cause formulas to reference the wrong cells after copying, producing silently incorrect results across potentially hundreds of rows – a large-scale accuracy risk that’s easy to introduce and easy to miss.