FINLANDIA SCHOOLPIVOT TABLES AND ADVANCED FORMULAS
A Pivot Table is an interactive data summarization tool that allows you to quickly aggregate, analyse and "pivot" (rearrange) large datasets without using any formulas.
Pivot tables transform a large flat table into a dynamic and concise summary report
Source data requirement: must be clean and in a tabular format
1. Each column must have a unique header e.g. date, amount
2. There must be no empty rows or columns within the data
The Pivot Table Interface
1. Field List: a list of all your column headers
2. Rows Area: Dragging a field here e.g. region creates a unique row for each item in that field.
3. Columns Area: Dragging a field here creates a unique column for each item.
4. Values Area: The "calculation engine". Dragging a numerical field here eg. amount defaults to sum. Dragging a text field defaults to count.
5. Filters Area: Used to filter the entire report by a specific field
How to create Pivot Table
1. Click anywhere inside your clean data
2. Go to the "insert" tab>Pivot table. Excel will auto select your data
3. Choose "New Worksheet" and click ok
4. Drag and drop your fields
Manipulating Pivot Tables
1. Refreshing: Pivot tables do not update automatically. If you change the source data, you must right-click the pivot table and select "Refresh"
2. Grouping:
ADVANCED FORMULAS AND FUNCTIONS
1. Logical functions( The IF statement): used to make your spreadsheet return a different results based on a condition.
E.g.: =IF(A2>=50, "pass", "fail")
2. Nested IF statement: used to handle multiple conditions by placing an IF statement inside another IF statement
E.g. =IF(A2>=70, "A", IF (A2>=60,"B", IF (A2>=50, "C", "F")
3. Lookup Fuctions