Course Overview
Data is the lifeblood of every business and understanding data through descriptive, predictive, and prescriptive analytics is critical to driving business success in a data driven economy. Creating great visualizations is another key to extracting maximum value out of their data. You will learn how to perform robust data analysis and create stunning visualizations using advanced Microsoft Excel tools.
Prerequisites
To ensure success, you should have baseline skill using Microsoft Excel worksheets, particularly in creating workbooks with formulas and functions.
Course Objectives
After completing this course, students will be able to:
- Data Analysis Fundamentals
- How to Visualize Data in Excel
- Using Formulas and Functions to Analyze Data
- Analyzing data with PivotTables
- Presenting Dashboards
- Creating Geospatial Visualizations
- Statistical Analysis
- Getting and Transforming Data
- Modelling Data with Power Pivot
- Presenting Insight with Reports
Outline: Data Analysis and Visualization with Microsoft Excel (D.A.V.E.)
Module 1: Data Analysis Fundamentals
Lessons
- Topic A: Data Analysis Scenarios
- Topic B: Tables
- Topic C: Sort and Filter Data
Module 2: Visualizing Data with Excel
Lessons
- Topic A: Visualize Data with Charts
- Topic B: Modify and Format Charts
- Topic C: Best Practices for Selecting Charts
Module 3: Analyzing Data with Formulas and Functions
Lessons
- Topic A: Analyze Data with Formulas
- Topic B: Analyze Data with Functions
- Topic C: Analyze Data with Data Validation, Forms, and Controls
- Topic D: Create Conditional Visualization with Lookup Functions
Module 4: Analyzing Data with PivotTables
Lessons
- Topic A: Create a PivotTable
- Topic B: Analyze PivotTable Data
Module 5: Presenting Visual Insights with Dashboards in Excel
Lessons
- Topic A: Visualize Data with PivotCharts
- Topic B: Filter Data Using Slicers
- Topic C: Create a Dashboard in Excel
Module 6: Creating Geospatial Visualizations with Excel
Lessons
- Topic A: Create Map Charts in Excel
- Topic B: Format and Customize Map Charts in Excel
Module 7: Performing Statistical Analysis
Lessons
- Topic A: Visualize Trendlines and Sparklines with Excel
- Topic B: Analyze Data with the Data Analysis ToolPak
Module 8: Getting and Transforming Data
Lessons
- Topic A: Connect to Data with Queries
- Topic B: Clean and Combine
- Topic C: Shape and Transform Data
Module 9: Modeling and Analyzing Data with Power Pivot
Lessons
- Topic A: Install Power Pivot in Excel
- Topic B: Create Data Models with Power Pivot
- Topic C: Create Power Pivots
- Topic D: Perform Advanced Data Analysis and Visualization
Module 10: Presenting Insights with Reports
Lessons
- Topic A: Planning a Report
- Topic B: Creating a Report