home & schedule | syllabus | contact | grades

Spreadsheets can be flat file databases.
The tools in Excel can manipulate such databases to array the data exactly as needed.

Using spreadsheets as databases

According to Microsoft

A PivotTable is an interactive way to quickly summarize large amounts of data. You can use a PivotTable to analyze numerical data in detail, and answer unanticipated questions about your data.

Pivot Tables

A PivotTable is especially designed for:

1. Querying large amounts of data in many user-friendly ways.
2. Subtotaling and aggregating numeric data, summarizing data by categories and subcategories, and creating custom calculations and formulas.
3. Expanding and collapsing levels of data to focus your results, and drilling down to details from the summary data for areas of interest to you.
4. Moving rows to columns or columns to rows (or "pivoting") to see different summaries of the source data.
5. Filtering, sorting, grouping, and conditionally formatting the most useful and interesting subset of data enabling you to focus on just the information you want.
6. Presenting concise, attractive, and annotated online or printed reports.

But why?

A pivot table is a table of statistics that summarizes the data of a more extensive table (such as from a database, spreadsheet, or business intelligence program). This summary might include sums, averages, or other statistics, which the pivot table groups together in a meaningful way.
Pivot tables are a technique in data processing. They enable a person to arrange and rearrange (or "pivot") statistics in order to draw attention to useful information.

It begins with deciding what to display.

1. Select the cells you want to create a PivotTable from.

Your data shouldn't have any empty rows or columns. It must have only a single-row heading.

2. From the ribbon, insert the pivot table.

3. Under Choose the data that you want to analyze select Select a table or range.

4. In Table/Range, verify the cell range.
5. Under Choose where you want the PivotTable report to be placed, select New worksheet to place the PivotTable in a new worksheet or Existing worksheet and then select the location you want the PivotTable to appear.

09 April lecture | preps | sorting | filtering | pivot tables