Study Notes #4

Spreadsheets 1: Getting Started

Range – A group of cells selected or addressed together. It is defined by the cells in the upper left and lower right corners of the range.

Relative Addressing – cell locations are relative to the answer cell that references them (This is the common.)

Absolute Addressing – fixed cell or range address that doesn’t change when copied. (We add a $ sign.)

Pro Tip: Use the F4 key to quickly toggle from relative to absolute addresses in Excel

Cell Formulas

Cell Formula – An expression, beginning with an equal sign (=), that defines the operations which calculate the value for the cell.

  • constant
  • may contain mathematical operations
  • may contain functions
  • may reference a single cell, a range of cells, or no cells

Function – a standard-defined routine that can be used in formulas (i.e. SUM, TRIM, AVERAGE…)

Parameter – value or expressions required by a function to determine a result, defined by the function

  • constant
  • cell reference
  • range reference
  • other expression
  • another function

Sample Functions


- Converts a text string to upper case letters.

- Returns the logical value TRUE.

- Estimates standard deviation based on a sample (ignores logical values and text).

- Returns the number of characters in a text string.
=SUM(A1, A2)

- Adds all the numbers in a range of cells.
=TRIM(A1, A2)

- Removes all spaces from a text string except single spaces between words.

Text String – a string of letters, numbers, and punctuations that are not treated numerically.

=SUBSTITUTE({text}, {old_text}, {new_text})

- where {text} is the cell to change, {old_text} is the string sequence to be replaced, and {new_text} is the new string in place of the old one.

=SUBSTITUTE(A14, "fox", "cat")

While the SUBSTITUTE function sounds similar to find/replace, it is used for different purposes. Find/replace gets rid of the old data, while SUBSTITUTE will not change the original cell, instead showing the transformed data in a new cell.

  • different than find/replace
  • does not change the original cell
  • transformed in new cell

    Leave a Reply