In this lecture we teach you about using spreadsheets for data analysis and visualization. Please follow the links in the side menu to view individual episodes.

Requires: Nothing, but the lectures use Excel.

Introduces: Spreadsheets

Motivating questions:

1. My data is in a table format.  How can I analyze if efficiently?
2. I have always used Excel, but how can I use it more effectively?

Lectures:

1. Introduction
   • A spreadsheet is a grid of cells that is well suited for manipulating numeric data.
   • Formulas allow one to perform many kinds of operations on cells in a spreadsheet.
   • Formulas can be made to use relative and/or absolute references to perform a calculation over sets of cells.
2. Aggregation
   • Aggregation is the combination of several values into a single result.
   • The colon operator can be used to specify a range of cells for aggregation functions like SUM
   • Blank cells are treated as missing in aggregation functions rather than as meaningful cells with value 0.
3. Display
   • Excel includes ways to format the visible region and the individual cells.
   • Rows and columns can be frozen so that they always appear (i.e. headers or legends.)
   • Cells can be reformatted to display more significant figures or to align digits.
4. Sorting
   • Excel allows the user to sort a highlighted region by a particular column or set of columns.
   • A ranking of any column can be created using the RANK function.
   • Tracing can be used to identify problems by visually showing all cells on which a formula depends.
5. Conditionals
   • Use SUMIF and related functions to operate only on values that satisfy some conditions.
   • Equivalent to if/then/else in programming languages.
6. Lookup
   • Use LOOKUP to select values from tables
7. Charting
   • Can easily create charts that are updated as data changes
   • Bewildering variety of options, many of which don’t actually give any more insight into data
8. Pivot Tables
   • Used to summarize data on two or more axes
9. Named Ranges
   • Refer to a range of cells using a symbolic name
   • Saves typing, but also prevents mistakes and makes spreadsheets easier to manipulate

Exercises:

• Exercises