A week of CIS 105.

Why copying a formula changes it

CIS 105 · How spreadsheets think · about 690 words

A spreadsheet looks like a table, but it behaves more like a calculator that remembers its work. The first popular spreadsheet program, VisiCalc, came out in 1979, and some people bought Apple II computers just to run it. Excel and Google Sheets still work on the same basic ideas, and once you understand them, most of what a spreadsheet does stops feeling like magic.

The grid is made of cells. Each cell has an address made from its column letter and row number, so B3 means column B, row 3. A cell can hold a value you type, like a number or a word, or a formula that calculates a value. Every formula starts with an equals sign. Type =4+5 into a cell and it shows 9.

Formulas get useful when they point to other cells. Picture a weekly budget. Column A lists items and column B lists amounts: Gas is 40 in B2, Food is 60 in B3, Phone is 35 in B4, and Savings is 65 in B5. The formula =B2+B3+B4+B5 shows 200. Change gas to 50 and the total updates to 210 on its own. The cell stores the instructions, not just the answer.

Typing out every cell gets old fast, so spreadsheets include built-in functions. =SUM(B2:B5) adds everything from B2 through B5; the colon means "through." =AVERAGE(B2:B5) adds the same cells and divides by how many there are, giving 50. Other common functions include MIN, MAX and COUNT. Say we put =SUM(B2:B5) in B6, so B6 holds the total, 200.

Now comes the idea that surprises most people. When you copy a formula to another cell, the spreadsheet does not copy it word for word. It copies the relationship. Suppose you want the cost of each item over four weeks, so in C2 you type =B2*4. The spreadsheet reads this as "take the cell one column to my left and multiply by 4."

Copy C2 down to C3 and the formula becomes =B3*4. Copy it to C5 and it becomes =B5*4. That is exactly what you want, and it is why you can fill a whole column by dragging one cell. These shifting addresses are called relative references. Moving a formula down one row adds one to each row number in it, and moving it right one column shifts each column letter over by one.

Sometimes that shifting breaks things. Suppose you want each item's share of the total. In D2 you type =B2/B6, which gives 40/200, or 0.2, which is 20 percent. Copy it down to D3 and both addresses move: =B3/B7. B7 is empty, which the spreadsheet treats as zero, so you get a #DIV/0! error. The B2 part was supposed to move. The B6 part was not.

The fix is the dollar sign. Writing $B$6 tells the spreadsheet to keep that column and that row fixed no matter where the formula is copied. This is an absolute reference. So D2 should be =B2/$B$6. Copied down, it becomes =B3/$B$6, then =B4/$B$6, and every share comes out right: 20, 30, 17.5 and 32.5 percent, which add up to 100.

You can also lock only part of an address. $B6 locks the column but lets the row change, and B$6 locks the row but lets the column change. These are mixed references, and they matter when you copy a formula both down and across. In Excel on Windows, pressing F4 while your cursor is on an address cycles through the options.

A good habit is to ask one question before you copy any formula: which parts should move with it, and which should stay put? Anything that points to a single fixed cell, like a total, a tax rate or a goal, usually needs dollar signs. Anything that should follow along row by row should not have them.

Spreadsheet mistakes are easy to make and hard to spot, because a wrong number looks just as neat as a right one. After you fill a column, click into a cell or two near the bottom and read the formula. If the result looks off, the cause is very often a reference that moved when it should have stayed, or stayed when it should have moved.

Sources: Microsoft Support, "Switch between relative, absolute, and mixed references"; Microsoft Support, "SUM function"; Microsoft Support, "AVERAGE function."