Recap
- Produce quick summaries and autoamtion in our workflows
Intro
- Pivot Tables - create dynamic reports and charts can be customized on the run
- Summarize and filter large amounts of info to produce useful reports
Creating and Modifying a Pivot Table
Table Design
-> Change Table Name toSales
- The data does not need to be in a table, but it can help when you update the data.
- Create Pivot Tables
- Total Sales by Customer Type
-
If we add some more data that ahd different customer types, just hit
Refresh button
-
easily get pivoted or changed to get different views on the data
-
Insert Another New Pivot Table - Sales by Account Manager
-
A field that contains text data, automatically adds that to the
Rows
area, each of those becomes row label -
A field that contains numeric data, automatically adds to
Values
area and summed -
Total by State - Column Label
-
Total by Year
Value Field Settings
- Summarize Values By
- Show Values As (e.g.: percentage as grand total/ column total, original - No calculation)
- Number Format
- Sales by State with subtotals
Sorting and Filtering a Pivot Table
- Quarterly Sales Trends by region for 2015 and 2016
- Ungroup and then group by quarter and year based on
Order Date
- Expand/ Collapse Field
-
Pivot automatically sort data by Row & Column
-
Filter data both by Row and Column Label
-
Quarterly Sales Trends for 2015, 2016 only VIC, WA
Reporting Filter Pages
- Filter data by
Account Manager
- Filter the entire pivot to only show a specific account manager
- Show 3 total values if you filtered with 3 account managers
Show report Filter Pages
-
Rename Pivot Table to
Regional Pivot
-
Take a deeper look at sales figures , you can just
double-click
the special/low value, Excel will show the snapshot of all values that we used to create that value -
Field List
- On/Off Pivot Tables
Pivoting Charts
- Pivot Chart by Customer Type (Pie Chart)
- Pivot Chart + Interactive Dashboard
Pivoting Slicers
- Slicer - another tool to filter data, easy to use
Analyze
->Insert Slicer
- Add Slicers - State, Customer Type, Account Manager
- Hold down
CTRL
key and multi-select
-
Connect slicers to multiple pivots
-
Slicer Options
-
Make both charts showing data when slicer is clicked
-
Interactive visual reports & dashboards
-
Report Connections
under Slicer
- You can connect a slicer to a pivot table whilst having clicked the slicer.
- A Slicer is a filter that has been made user-friendly and now anyone can use it with ease by just clicking on what they want the spreadsheet to filter by and the pivot table and charts (dashboard) display accordingly.
Final Assessment
- Analyzing Student Marks
=AVERAGE('Marks Term 1:Marks Term 4'!E4)
- SHIFT through sheetsCTRL + SHIRT + F3
- Create from Selection=COUNTIFS(Grade,L10)
=CONCAT(PROPER(B4)," ",PROPER(C4))
=CONCAT(LOWER(LEFT(B4,1)),LOWER(C4),"@newcollege.com")
=CONCAT("20",RIGHT(A4,2))
- move back and forth through sheets, use
CTRL + PGUP OR PGDOWN
Consolidate Tool
- Top Row or Left COlumn (Sum, Count, others)- Format Weird Consolidated Data to General
- Conditional Formatting and find out who has absent MORE THAN 15 DAYS