Skip to content

Latest commit

 

History

History

Week 6

Folders and files

NameName
Last commit message
Last commit date

parent directory

..
screenshot
screenshot
 
 
workbook
workbook
 
 
README.md
README.md
 
 

README.md

Week 6

Pivot Tables, Pivot Charts, and Slicers

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 to Sales
  • 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 sheets
  • CTRL + 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