Big Data Analysis With Excel

This training is intended for anyone with an intermediate knowledge of Excel who wants to analyze and visualize data in order to get results. It focuses on understanding the underlying structure of big data so that the most appropriate tools can be used to analyze it. The training is targeted at an average person who wants to leverage his or her Excel skills to analyze large datasets.

Learning Outcome

This training will enable candidates to:-

• Understand the use of Excel as Database and Data Aggregator

• Create and manipulate Pivot Tables and Pivot Charts

• Build Data Model

• Use SQL in Excel

• Design Reports with Power View

• Create calculation with Data Analysis Expressions (DAX)

• Import Data using Power Query

• Create maps with Power Map

• Perform Statistical Calculations

Prerequisite

Participants must be well versed with Excel Formulas and functions.

Training Course Outline

Module 1: Excel As Database and Data Aggregator

• From Spreadsheet to Database

• Interpreting File Extensions

• Using Excel As a Database

• Importing from Other Formats

• Using the Data Tab to Import Data

• Data Wrangling and Data Scrubbing

• Input Validation

• Working with Data Forms

• Selecting Records

Module 2: Pivot Tables and Pivot Charts

• Recommended Pivot Tables in Excel 2013

• Creating a Pivot Chart

• Adjusting Subtotals and Grand Totals

• Analyzing Sales by Day of Week

• Creating a Pivot Chart of Sales by Day of Week

• Using Slicers

• Adding a Time Line

• Importing Pivot Table Data from the Azure Marketplace

Module 3: Building a Data Model

• Enabling PowerPivot

• Relational Databases

• Database Terminology

• Creating a Data Model from Excel Tables

• Loading Data Directly into the Data Model

• Creating a Pivot Table from Two Tables

• Creating a Pivot Table from Multiple Tables

• Adding Calculated Columns

• Adding Calculated Fields to the Data Model

Module 4: Using SQL in Excel

• SQL Syntax

• SQL Aggregate Functions

• Subtotals

• Joining Tables

• Importing an External Database

• Specifying a JOIN Condition and Selected Fields

• Using SQL to Extract Summary Statistics

• Generating a Report of Total Order Value by Employee

Module 5: Designing Reports with Power View

• Elements of the Power View Design Screen

• Considerations When Using Power View

• Types of Fields

• Understanding How Data Is Summarized

• A Single Table Example

• Viewing the Data in Different Ways

• Creating a Bar Chart for a Single Year

• Column Chart

• Displaying Multiple Years

• Adding a Map

• Using Tiles

• Relational Example

• Customer and City Example

• Showing Orders by Employee

• Aggregating Orders by Product

Module 6: Calculating with Data Analysis Expressions (DAX)

• Understanding Data Analysis Expressions

• Updating Formula Results

• Using the SUMX Function

• Using the CALCULATE Function

• Calculating the Store Sales for 2009

• Creating a KPI for Profitability

• Creating a Pivot Table Showing Profitability by Product Line

• Summary

Module 7: Power Query

• Installing Power Query

• Working with the Query Editor

• Key Options on the Query Editor Home Ribbon

• A Simple Population

• Performance of S&P 500 Stock Index

• Importing CSV Files from a Folder

• Importing JSON

Module 8: Power Map

• Installing Power Map

• Plotting a Map

• Key Power Map Ribbon Options

• Troubleshooting

Module 9: Statistical Calculations

• Recommended Analytical Tools in 2013

• Customizing the Status Bar

• Inferential Statistics

• Review of Descriptive Statistics

• Charting Data

• Excel Analysis ToolPak

• Using a Pivot Table to Create a Histogram

• Scatter Chart