Instructor for this course
more

Learn how to explore and analyze your data by creating an automated business dashboard in Excel using pivot tables, Power Query, and a little Visual Basic. Using these lessons, you can create a dynamic, automated template to generate reports and gain insights from your data. Along the way you'll learn how to import and transform external data using using Power Query, design dashboards using Pivot Tables, and add macros and buttons to make the template interactive.

We're going to leverage new functionality in Excel in order to automate data extraction and data cleansing and thus concentrate on turning our data into insights and insights into actions. Let's get started.

Learning Objectives

  • Identify data that is suitable for data analysis and recognize how to transform them as necessary.
  • Discover Excel’s business intelligence capabilities using Power Query, Pivot tables and Pivot Charts
  • Explore Excel’s capabilities to automate data cleansing and transformation using Power Query
  • Explore basic programming concepts using VBA (Visual Basic for Applications) and Power Query
Last updated/reviewed: November 19, 2019

3 Reviews (8 ratings)Reviews

3
Anonymous Author
Good overall intro to power query. But the VBA portion was just copying existing code. OK for this level of course since focused on power query.
4
Anonymous Author
While a bit dry the course effectively covered the stated learning objectives.
4
Member's Profile
Course content was good and the materials provided were helpful.

Prerequisites

Course Complexity: Advanced
  • You should know how to use Excel and are familiar with basic formulas and how to build them. 
  • You’ve created basic charts using the standard ones available in Excel, and you’re familiar with Pivot Tables and Pivot Charts but not an expert.
  • Familiarity with Excel add-ins and how to activate them.
  • Familiarity with Excel VBA is ideal but is not a must. 
  • Excel 2010 or later is needed to create Power Query commands and edit them.

Education Provider Information

Company:
Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact:
For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Course Syllabus
INTRODUCTION AND OVERVIEW
  4:01Introduction to Excel Power Query: Create Automated Business Dashboard
  4:06Getting Started
  4:56Planning our Dashboard
  16:17Preparing and Cleansing Our Data
  5:36Creating our Dashboard
  12:30Designing our Dashboard
  14:31Future - Proofing our Dashboard
  5:54Finishing our Dashboard
  00:38Conclusion
CONTINUOUS PLAY
  1:08:29Excel Power Query: Create Automated Business Dashboard
SUPPORTING MATERIALS
  PDFSlides: Excel Power Query: Create Automated Business Dashboard
  PDFExcel Power Query: Create Automated Business Dashboard Glossary/Index
  ZIPExercise Files: Excel Power Query
REVIEW AND TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM