Excel Power Query: Create an Automated Business Dashboard 1.5 CPE

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.

Prerequisites

• 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.

Learning Objective

• 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