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: March 4, 2024
11 Reviews (23 ratings)

Reviews

3
Anonymous Author
I liked that it went through a lot of the functions of Power query and a step by step process of how everything comes together. I would have liked more time spent on how to go through the process for using power query, rather than the creation of the dashboard. I will use this as a baseline on what is possible to manipulate in power query, but I will most likely need to lookup more training to implement for my own data.

3
Anonymous Author
Too much copy and pasting in this module. Power query seems like too advanced of a tool to use without coding background. I feel like a general overview of what happened would be good as well on top of discussion on when and why you would want to use power query over just sticking with excel. Most of what was done seems like it could have been done in strictly excel.

5
Member's Profile
Definitely an advanced course. Prereqs were outlined in the first video and were definitely needed. I didn't have them and struggled. The course was very hands on and went though an example from start to finish.

1
Anonymous Author
This was not a good course. It was basically a guy narrating while he creates power queries and dashboards without really explaining or teaching along the way.

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
Member's Profile
Good information on a new tool for me. Generally use Access to link tables, gives another tool that is more helpful for end users.

3
Anonymous Author
This class was pretty challenging. I would be interested in a course with more focus on creating variations of dashboards.

2
Member's Profile
There are too many unexplained VBA codes in use, I feel woefully unprepared to build dashboards.

5
Member's Profile
great! i learned a lot and appreciate the explanations and detailed instructions

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 .
Instructor for this course
Course Syllabus
INTRODUCTION AND OVERVIEW
  Introduction to Excel Power Query: Create Automated Business Dashboard4:01
  Getting Started4:06
  Planning our Dashboard4:56
  Preparing and Cleansing Our Data16:17
  Creating our Dashboard5:36
  Designing our Dashboard12:30
  Future - Proofing our Dashboard14:31
  Finishing our Dashboard5:54
  Conclusion00:38
CONTINUOUS PLAY
  Excel Power Query: Create Automated Business Dashboard 1:08:29
SUPPORTING MATERIALS
  Slides: Excel Power Query: Create Automated Business DashboardPDF
  Excel Power Query: Create Automated Business Dashboard Glossary/IndexPDF
  Exercise Files: Excel Power QueryZIP
REVIEW AND TEST
  REVIEW QUESTIONSquiz
 FINAL EXAMexam