Description:

Information about using spreadsheets.

Purpose:  

Spreadsheets are useful for storing and evaluating data, using basic statistical functions, and creating charts and graphs to present data visually.

Procedure:

1. Populate Data

   1. Enter Data into a spreadsheet, using rows as single records, columns as fields, and each individual cell as a single data point containing the field value for that record.
   2. Auto-fill and auto-complete functions streamline population of common data sets, such as a series of numbers, days of the week, times, months, or a series of letters. Auto-complete enters data based on previously data in that column or row.
   3. Rows, columns, and cells can be formatted to display certain types of data, such as text, numbers, currency, special numbers like ZIP code, phone, or dates and times, in a consistent format.

2. Evaluate Data

   1. Use formulas and functions to analyze data you have entered or to enter entirely new data automatically. Formulas begin with an equal sign and contain a combination of operators, cell references, and functions in order to retrieve or create the data you desire.
      1. Operators are symbols that spreadsheets use to perform basic mathematical or textual functions such as + – * / & for adding, subtracting, multiplying, dividing and concatenating.
      2. Cell references can be done on an individual cell basis (A2 or B26), for an entire column (C:C) or row (3:3), or for an area (A2:D52).
         1. Absolute references mean that the cell referenced in a formula will reference exactly the same cell when it is copied. Absolute references are noted with a dollar symbol ($) and can be used for an absolute row reference (A$3), an absolute column reference ($A3) or an absolute cell reference ($A$3).
         2. Relative references mean that when the formula is copied, the cell referenced in the formula will adjust to the new location, so if the cell referenced is two rows up and one column down, that relative location will be referenced rather than the original cell. For example if a formula in D6 references cell A3, that formula when copied to E6 would reference cell B3.
      3. Functions are built-in data processing routines that are generally formatted as the function name followed by a set of parenthesis with the parameters of the function inside. Most spreadsheet programs have guides to using their functions, and provide comprehensive help via their documentation. Functions can be nested within each other, and when used in combination with operators, can allow you to evaluate or retrieve almost any type of data relevant to your needs.
   2. Sort your data alphanumerically using menu or shortcut commands. Warning:  sorting a single column independent of the rest of your data set can result in mis-matched data and header rows data (titles of columns) sorted into the rest of your data. Until you are familiar with sort and filter functions, keep a backup or your original, unsorted data.
   3. Filter data by specified values or conditions, only displaying the records that contain the specified data, and hiding the rest until you remove the filter.

3. Visualize Data

   1. Charts and graphs can represent your data visually.
   2. Pivot tables/charts let you see relationships between different types of data on the fly.
   3. Conditional formatting changes the appearance of a cell or cells based on a pre-set condition.

Considerations:  

• Each spreadsheet software package (Excel, Calc, Google Sheets, etc.) has different functionality, so something that is possible in one, may not be possible in another.  If students are not using a common spreadsheet application, it can be difficult to create assignments around spreadsheets.
• Spreadsheet software is not intuitive to most students.  Students using spreadsheet software need guidance and clear expectations.

Level: intermediate