Chem 311 Help — Cell Addressing

Cell Addressing in Spreadsheets

Every cell in a spreadsheet has a unique cell address. A cell address consists of a column letter followed by a row number. For example, the address B3 refers to the cell in column B and row 3.

Cell addresses allow formulas to use the values stored in other cells. For example, if cells A1 and A2 contain numbers, the formula


  =A1+A2

adds the values stored in those two cells.

Relative References

By default, cell references are relative. This means that when a formula is copied to another cell, the references change automatically to maintain the same relative positions.

Original Formula	Copied One Row Down
`=A1+B1`	`=A2+B2`

Relative references are convenient because the same formula can be copied to many rows or columns without rewriting it.

Absolute References

Sometimes a formula should always refer to the same cell, even after it is copied. In this case, use the $ symbol to create an absolute reference.

Reference	Meaning
`A1`	Column and row are both relative.
`$A$1`	Column and row are both fixed.
`$A1`	Column is fixed; row changes when copied.
`A$1`	Row is fixed; column changes when copied.

Examples

Suppose cell B1 contains the value of the gas constant, R. The following formula calculates the pressure for the first sample:


  =A2*$B$1/C2

If this formula is copied down the worksheet, the references to A2 and C2 change automatically, but the reference to $B$1 always points to the gas constant stored in cell B1.

When should you use each type?

Use relative references when the referenced cells should change as the formula is copied.
Use absolute references when every copied formula should refer to the same cell.
Use mixed references ($A1 or A$1) when only the column or only the row should remain fixed.

Compatibility: Microsoft Excel and LibreOffice Calc use exactly the same cell addressing system and the same $ notation for absolute and mixed references.

Big idea: Relative references automatically adjust when formulas are copied, while absolute references remain fixed. Knowing when to use each allows you to build formulas once and reuse them throughout an entire worksheet.

Named Cells

Instead of referring to a cell by its address (such as $B$2), a spreadsheet allows you to give the cell a name. Named cells make formulas easier to read and reduce the chance of errors when a value is used repeatedly throughout a worksheet.

Named cells are especially useful for storing constants such as physical constants, calibration factors, or experimental parameters. Instead of writing


  =A2*$B$1

you can write


  =A2*GasConstant

The second formula is much easier to understand because the purpose of the value is immediately obvious.

Creating a named cell in Microsoft Excel

Select the cell you wish to name.
Click in the Name Box (to the left of the formula bar).
Type a descriptive name.
Press Enter.

For more control, choose Formulas → Define Name. This dialog allows you to specify the name and its scope.

Creating a named cell in LibreOffice Calc

Select the cell you wish to name.
Choose Sheet → Named Ranges and Expressions → Define… (or Insert → Named Range or Expression in older versions).
Enter a descriptive name.
Select the desired scope.
Click Add (or OK).

Although the menus differ slightly, named cells behave the same way in LibreOffice Calc and Microsoft Excel.

Understanding scope

The scope of a name determines where that name can be used.

Scope	Where the name can be used
Workbook (Excel) Document (Calc)	Available from every worksheet in the spreadsheet.
Worksheet (Excel) Sheet (Calc)	Available only on the worksheet where it was created.

For most scientific calculations, constants such as GasConstant, AtmosphericPressure, or CellLength are best given workbook/document scope so they can be used anywhere in the file.

Using named cells in formulas

Once a cell has been named, simply use its name wherever you would normally use a cell address.


  =A2*GasConstant
  =(B4-Blank)/PathLength
  =-LOG10(H_conc)

Spreadsheet software automatically replaces the name with the value stored in the corresponding cell when performing the calculation.

Naming rules

Names must begin with a letter or an underscore.
Names may contain letters, numbers, periods, and underscores.
Spaces are not allowed.
Choose descriptive names such as GasConstant, SampleMass, or CellLength.

Big idea: Named cells make spreadsheets easier to read, maintain, and debug. They allow formulas to describe the calculation being performed rather than the locations of the cells involved.

Chemistry 311

Cell Addressing