Spreadsheets

Why Use Spreadsheets?

Repetitive calculations› Frequency› Complexity› Different views

Analysis of data› Explorative› Manipulation

Basic StructureSpreadsheet (Worksheet)

cell (letter-number)

Column (letters)

Row (numbers)

workbook = collection of worksheets

Cells

What Can Be In a Cell

Label – identification for people Constant – any format

› Text, number, picture, hyperlink, …› Value for computer› Format for people

Formula – uses cells & constants› Always begins with =› “takes the value of”

Formulas

Simplest Formula

=cell Why do you use it? Fundamental Principle:

› Never have to change anything in two places

› Variant of DRY (Don’t Repeat Yourself)

Copy-paste› Fine if you really want a snapshot› Does not work if data will change

Formulas Referencing Cells

Once you define the formula› Can change the values as often as you like› Automatically re-computes

Treats cells as variables› Defined by location, not value› Each cell constant or another formula

Example› Pay = hourly rate * hours worked

Values can change Formula remains the same

Copying formulas Want the same information for different data

› Example: min, max, avg grades for each assignment

Can use copy or fill Copying a formula moves it relatively

Dragging (Fill)

Bottom right corner› One cell copies

Value exact Formula changes cells

› Multiple cells extrapolate

What if Want SAME Place

Absolute positioning› Can lock the cell, column or row

Cell: $A$1 Column: $A1 Row: A$1

› To change a reference to absolute Insert $ PC: Use F4 Mac: Cmd-T

Referencing Cells Across Spreadsheets

Can simply click on a cell on another sheet› Check the results; easy to get the wrong

cell as you navigate between sheets. Reference it using

SheetName!cell

Constants

Formulas Using Constants

Use constants when they will not change

Values that won’t change:› Computing the area of a circle

Π r2

› Computing the area of a triangle ½ base*height

What about…› Minutes in an hour?› Days in the year?

Constants

Use directly in formulasOr

Place in cells› Used frequently› Precision: make sure it’s right!

Names

To Name Cells or Ranges

Name Box

Named elements are fixed locations

Name Manager

Can use to name or manage names

Accessing:› PC

Formulas -> Define Name› Mac

Insert -> Name -> Define

Tips

Finding functions

Useful Keys and Practices

Paste specials› Values› Transpose› With and without formatting

ESC: This key is your friend Changing cell sizes Deletions and additions

Best Practice

Name spreadsheets (rename) Delete unused spreadsheets Separate input, computation, and

output› Input: only page that you change› Output: readability› Computation: everything else