Learning Objectives

We want to become able to:

• Use conditional formatting
• Use conditional formulas
• Define names (named cells or ranges)
• Filter data
• Explore scenarios (“what if”)
• Work with multiple sheets

Notes from Snyder, Chapter 14

We can do these things; how do we do them?

• We can insert and edit comments (Insert / Comment)
• We can conditionally format cells
  • For example, to highlight out-of-range values
  • Compare the cell value (<, <=, =, not =, >, >=) to a constant, a cell value, a formula, etc.
  • Using one or more rules
  • This is tricky?
• Conditional formulas
  • Using the IF function: =if(condition,value1,value2)
    • [But avoid =if(condition,TRUE,FALSE); why not just =(condition)?]
• Defining names
• “What if” analysis: scenarios
  • Tools / Scenarios
  • Do we really need a Scenario to do this?
• We can formulate models and use the spreadsheet to evaluate them
• Filtering data
  • Autofilter
  • Custom (“advanced”) filters

Other Comments

Here are some other useful functions

• =lookup(value,keys,values)
  • keys must be sorted
• =average(values)
• =stdev(values)

A “workbook” may contain multiple “sheets”

• Sometimes useful for dividing the work or hiding things
  • University: accounts for each child
  • “Off-page” tables used by the lookup function
• Inserting, renaming, and deleting sheets
• Addressing cells in another sheet: SheetName!B5 or SheetName.B5??