Copy and Paste is magical in a spreadsheet when dealing with formulas.

Let's look at the following snippet of a spreadsheet

NOTE:Need a table

	A	B	C	D
1	Salespeople And Units Sold
2
3	Person	Cars	Trucks	Total
4	Bill	6	11	=B4+C4
5	Mike	11	19
6	Molly	6	13

The logic of the spreadsheet is very simple; we have two columns of numbers (Cars and Trucks) that we want to add together to get a total number of vehicles sold.

The formula in cell D4 is correct; it adds together the right data. Now let's see what happens when we copy that cell and paste it into cell D5.

	A	B	C	D
1	Salespeople And Units Sold
2
3	Person	Cars	Trucks	Total
4	Bill	6	11	=B4+C4
5	Mike	11	19	=B5+C5
6	Molly	6	13

Subsection(s)

What Happened?

Something odd, that's for sure.

The cell references inside the formula changed when we copied the cell down a row. In fact, if you'll notice, the formula changed so it will work on the new row the same way it worked on the old row.

Useful, that. What happens if we copy it down to the next row?

	A	B	C	D
1	Salespeople And Units Sold
2
3	Person	Cars	Trucks	Total
4	Bill	6	11	=B4+C4
5	Mike	11	19	=B5+C5
6	Molly	6	13	=B6+C6

Excellent. Just what we wanted.

Just for kicks, even though it won't help our spreadsheet, what happens if we move it over a column?

	A	B	C	D	E
1	Salespeople And Units Sold
2
3	Person	Cars	Trucks	Total
4	Bill	6	11	=B4+C4	=C4+D4
5	Mike	11	19	=B5+C5
6	Molly	6	13	=B5+C5

Hmmmm. Well, that's not good for much, but the pattern remains: we copied it over one column, and the cell references changed so all their columns went up by one.

What's going on?

The formulas we're dealing with use what we call relative addressing. That means that the formula really deals with spatial relationships instead of absolute relationships.

When we copy a formula, it preserves the spatial relationship (e.g., two cells over) and not the absolute relationship (e.g., cell B4).

Q. 9

What?????????

Thus, in our example above, the formula in cell D4 really says "Take the value of the cell two over from me, and add it to the value of the cell one over from me". When we copied around the formula, those relationships were preserved and the formulas were changed.

Why on earth does it work that way?

Spreadsheets are set up this way because 99.99% of all spreadsheets are a lot like the one in the short example above: Very repetitive data, all of which you want to handle in basically the same way.

Can you imagine if you had 200 salespeople and had to type in all of those formulas by hand? Forget it! -- you might as well still be using paper and a calculator!

By using relative cell addressing, you can fill in those 199 extra cells simply and easily.

Steps for using formulas

Decide what you need the formula to do

This sounds obvious, but it's the first step in the STAIRS technique for a reason. Make sure you know what you're up to; randomly typing in numbers won't get you very far.

Write the formula once

Write the formula in the first spot you need it.

Convince yourself it works

Make sure it works. Try it out with weird data. Make sure you fully understand what it's doing and what data it's working with.

Copy the living tar out of it

Once you've written a formula that works, abuse it!

• Select the cell with the good formula
• Choose Edit:Copy to put it on the clipboard
• Use the mouse to select all the other cells where you need that formula
• Choose Edit:Paste