DATA ANALYSIS WITH EXCEL

• What is Data Analysis?
• Excel Pro Tips for Power Users
• Advanced Excel Formulas & Functions
• Structuring Source Data for Analysis in Excel
• Excel tables
• Advantage of Excel tables over regular ranges
• Filter, sort and see totals
• Use tables to add columns to the existing data in the Excel table
• Raw data structure in Excel
• Design & formatting options
• Sorting, filtering, & grouping tools
• Calculated fields, items & values
• Interactive Excel dashboards

PIVOT TABLES

• Why Pivot Tables?
• Create pivot tables
• Data Analysis with Excel PivotTables
• Use pivot tables and pivot charts to create dashboards
• Connect multiple slicers to the pivot tables
• Configuring pivot table layouts & styles
• Pivot Table layouts & styles
• Pivot Charts, slicers & timelines

USING PIVOT TABLES FOR ADVANCED CALCULATIONS

• The full power of pivot tables
• Filter pivot table data to achieve interesting subsets of the data
• Apply calculated fields with pivot tables
• Calculate profitability and find anomalies

AGGREGATE DATA

• Use formulas to aggregate data
• Aggregate data as an alternative to pivot tables
• Flexible reporting layouts
• Pivot charts using more than one table

LAB ENVIRONMENT & INSTALLATIONS

• Set the lab environment up with Office applications
• Data analysis in Excel using tools:
  
  • pivot tables
  • pivot charts and slicers
• Excel data models
• Power Pivot add-in
• DAX expressions for calculated columns and measures

QUERIES

• What are the queries?
• Power Query for Excel 2010 and 2013
• Excel data model built from a single flat table
• Import multiple tables from a SQL database
• Create an Excel data model using imported data
• Create a collated result with data from text-files and data from a SQL database
• Up & running with Power BI Desktop
• Publishing to Power BI Service
• SQL database analysis for beginners

DATA FILTERS & MEASURES

• How to establish measures to calculate each cell
• Filter context for calculation
• DAX functions
• Enhanced queries to import data from a formatted Excel sheet
• Queries beyond the standard user interface

DATA VISUALISATIONS

• Data visualisations in Excel
• Cube functions for year-over-year comparisons
• Create timelines, hierarchies, and slicers
• Excel working together with Power BI
• Excel workbooks with Power BI service
• Excel on the mobile platform

CASE STUDIES

• Facebook Post data from Spartan Race
• IMDb movie ratings
• Apple Store mobile app ratings
• Shark attack records from 1900-2016