Study Guide: Spreadsheets

When we refer to a cell we identify the cell by its address or location on the worksheet. This name is the address of the cell such as A1, B10... Cell addresses can be one of three different types: relative, absolute, or mixed. The vast majority of cell addresses are written as relative.

A cell might be known as B6. B6 is a relative address, which means that it is an address that CAN change and it will change when copied.

If the formula = D4 + 365 is stored in cell F7 and the contents of F7 is copied to F8 (or down one row), the formula will change, relative to the direction it is copied, by one row. The new formula will be = D5 + 365.

If the formula = D1 + 365 is stored in cell B2 and the contents of B2 is copied to C2 (or over one column), the formula will change, relative to the direction it is copied, by one column. The new formula will be = E1 + 365.

Notice that when copied, the relative address A1 changes as it is copied.

The Rules of Relative Addresses:

1. If the address is copied down n number of rows, the row portion of the address changes by n. If G7 is copied down 7 rows, the new address will be G14.
2. If the address is copied over n number of columns, the column portion of the address changes by n. If G7 is copied over 7 columns, the new address will be N7.

A cell address might be known as B6. If this address is written as $B$6, it is an absolute address. An absolute address is a permanent address. It will not change when copied.

Use an absolute address when one cell affects many other cells. Think of it as a constant. It could be a tax rate and every sale would use that same tax rate or the contents of the cell that contained the tax rate.

Notice that when copied, the absolute address $C$1 does not change.

If an address is a mixed address (part relative address and part absolute address) it would look like B$6 or $B6. The relative portion of the address follows the rules of relative addresses and can change. The absolute portion of the address follows the rules of absolute addresses and cannot change.