Why don't Kaohsiung and Hsinchu need to re-enter the formula after dragging the N2 formula down?
Because the cell references in the formula are relative. When the formula is copied down using the fill handle, the row indices in the range automatically shift to match the new row. Thus, =AVERAGE(B2:M2) becomes =AVERAGE(B3:M3) for Kaohsiung and =AVERAGE(B4:M4) for Hsinchu without manual re-entry.
Conditions
- The original formula uses relative references (no dollar signs locking rows or columns).
- The data structure in adjacent rows is identical (same columns for the same metric).
- The formula is copied vertically using the fill handle.
Reasoning, step by step
- Enter the formula =AVERAGE(B2:M2) in cell N2.
- Move the cursor to the bottom-right corner of N2 until it becomes a cross (fill handle).
- Drag the fill handle down to N3 and N4.
- The spreadsheet automatically adjusts the row numbers in the references: B2:M2 shifts to B3:M3 and B4:M4.
- Press Enter or release the mouse to finalize the copy.
Example
After copying down, the formula in N3 is =AVERAGE(B3:M3), resulting in 25.57, and N4 is =AVERAGE(B4:M4), resulting in 23.13.
Common misconceptions
- Believing that one must manually type the formula for every row.
- Thinking that the column letters will also change when dragging down (they remain B to M because the drag is vertical).
Watch the explanation
0:53 – 1:05Watch this moment ↗
Explore next
Answers are generated from source material and independently checked. Consult the original video or creator if something is unclear.