Explore

Excel Power Tools for Data Analysis

Approx. 15 hours to complete

Save Course

Go to Course

Course Summary

This course teaches you how to effectively use Excel's power tools to analyze and manipulate data. You'll learn how to use macros, pivot tables, and Power Query to simplify your workflow and save time.

Key Learning Points

Learn advanced Excel techniques to analyze and manipulate data
Become proficient in macros, pivot tables, and Power Query
Save time and streamline your workflow with Excel's power tools

Job Positions & Salaries of people who have taken this course might have

- USA: $67,000
- USA: USD
- India: ₹4,80,000
- India: INR
- Spain: €32,000
- Spain: EUR
- USA: $67,000
- USA: USD
- India: ₹4,80,000
- India: INR
- Spain: €32,000
- Spain: EUR
- USA: $61,000
- USA: USD
- India: ₹4,20,000
- India: INR
- Spain: €28,000
- Spain: EUR
- USA: $67,000
- USA: USD
- India: ₹4,80,000
- India: INR
- Spain: €32,000
- Spain: EUR
- USA: $61,000
- USA: USD
- India: ₹4,20,000
- India: INR
- Spain: €28,000
- Spain: EUR
- USA: $81,000
- USA: USD
- India: ₹6,00,000
- India: INR
- Spain: €38,000
- Spain: EUR

Learning Outcomes

Master advanced Excel techniques for data analysis
Streamline your workflow and save time with Excel's power tools
Become proficient in macros, pivot tables, and Power Query

Prerequisites or good to have knowledge before taking this course

Basic knowledge of Excel
Access to Microsoft Excel 2016 or higher

Course Difficulty Level

Intermediate

Course Format

Online self-paced course
Video lectures
Hands-on exercises

Similar Courses

Excel for Business Analysts
Advanced Excel for Data Analytics
Excel Skills for Business: Intermediate I

Related Education Paths

Notable People in This Field

Chandoo
Mynda Treacy
Jon Acampora

Related Books

Description

Welcome to Excel Power Tools for Data Analysis. In this four-week course, we introduce Power Query, Power Pivot and Power BI, three power tools for transforming, analysing and presenting data.

Excel's ease and flexibility have long made it a tool of choice for doing data analysis, but it does have some inherent limitations: for one, truly "big" data simply does not fit in a spreadsheet and for another, the process of importing and cleaning data can be a repetitive, time-consuming and error-prone. Over the last few years, Microsoft have worked on transforming the end-to-end experience for analysts, and Excel has undergone a major upgrade with the inclusion of Power Query and Power Pivot. In this course, we will learn how to use Power Query to automate the process of importing and preparing data for analysis. We will see how Power Pivot revolutionises the actual analysis process by providing us with an analytical database inside the Excel workbook, capable of storing millions of rows, and a powerful modelling language called DAX which allows us to perform advanced analytics on our data. We will finish off by venturing out of Excel and introducing Power BI, which also uses the Power Query and Power BI architecture but allows us to create stunning interactive reports and dashboards. This is the third course in our Specialization on Data Analytics and Visualization. The previous courses: Excel Fundamentals for Data Analysis and Data Visualization in Excel, cover data preparation, cleaning, visualisation, and creating dashboards. To get the most out of this course we would recommend you do the previous courses or have experience with these topics. In this course we focus on Excel Power Tools, join us for this exciting journey. Please note that Power Query, Power Pivot and Power BI Desktop are only available on the Windows platform, so Mac users will require Bootcamp running Windows or a Virtual machine with a Window O/S. While Power Query is available as an add-in Excel 2010 and 2013, the tools have changed significantly, and this course has only been designed and tested for Excel 2016 and later. For an optimal experience, we recommend Office 365.

Outline

Welcome and critical information
Welcome to Excel Power Tools for Data Analysis
Course goals and weekly learning objectives
Important information about versions and regions

Get and Transform (Power Query)
Week 1 Introduction
Introduction to Power Query
Get and transform data from Excel
Get data from a database
Get data from a folder
Append queries with 2 datasets
Append queries with 3 or more datasets
Merge queries — Left Outer join
Merge queries — Inner and Anti joins
Read me before you start: Quizzes and Navigation
Download the Week 1 workbooks
Week 1 Practice Challenge
Week 1 Toolbox
Introduction to Power Query
Get and transform data from Excel
Get data from a database
Get data from a folder
Append queries with 2 datasets
Append queries with 3 or more datasets
Merge queries — Left Outer join
Merge queries — Inner and Anti joins

Transforming data in the Query Editor
Week 2 Introduction
Applying transformations with the Query Editor
Unpivoting data
Pivoting data in the Query Editor
Grouping data
Working with columns in Office 365
Working with columns in Excel 2016
Download the Week 2 workbooks
Working with columns — difference in versions
Week 2 Practice Challenge
Week 2 Toolbox
Applying transformations with the Query Editor
Unpivoting data
Pivoting data in the Query Editor
Grouping data
Working with columns
Test your skills: Weeks 1 and 2

Power Pivot and the Data Model
Week 3 Introduction
Adding data to the Data Model
Data Modelling
Analysing data with Power Pivot
Visualising data with Power Pivot and Cube Functions
Introduction to DAX measures
Applying Time Intelligence and CALCULATE
Download the Week 3 workbooks
Week 3 Practice Challenge
Week 3 Toolbox
Adding data to the Data Model
Data Modelling
Analysing data with Power Pivot
Visualising data with Power Pivot and Cube Functions
Introduction to DAX measures
Applying Time Intelligence and CALCULATE
Test your skills: Week 3

Visualising Data with Power BI
Week 4 Introduction
Introducing Power BI
Adding a Calendar using DAX
Creating a Power BI report
Working with reports
Adding time intelligence
Sharing Power BI reports
Download the Week 4 workbooks
Week 4 Practice Challenge
Week 4 Toolbox
Introducing Power BI
Adding a Calendar using DAX
Creating a Power BI report
Working with reports
Adding time intelligence
Sharing Power BI reports
Test your skills: Week 4

Summary of User Reviews

This Excel Power Tools course has received high praise from many users for its comprehensive content and practical application. One key aspect that many users thought was good is the course's focus on real-world examples and exercises that help learners apply the tools and techniques in their work. Overall, the course has received positive reviews from users.

Pros from User Reviews

Comprehensive content that covers a wide range of Excel tools and techniques
Real-world examples and exercises that help learners apply the tools and techniques in their work
Engaging and knowledgeable instructors who provide clear explanations and demonstrations

Cons from User Reviews

Some users found the course to be too basic and not challenging enough
The course is quite lengthy and may require a significant time commitment
The course may not be suitable for advanced Excel users who are already familiar with many of the tools and techniques covered

Recommended for you

Power BI Desktop Masterclass - Data Analysis for Everyone

Master Power BI Desktop- Get, Transform, Model and visualize data like never before - Self Service Business Intelligence Updated May 2017...

Save Course

QuickBooks Pro 2017 Training: Manage Small Business Finances

The Easiest, Most Convenient and Surefire Way to Master QuickBooks Pro 2017 Get Confident in Using QuickBooks Pro to Efficiently Manage Your Accounts...

Save Course

Microsoft Excel and PowerPoint Training With Certificate

Learn the most essential and valuable skills for personal and professional success About the class: This is a combined certificate training of Microsoft Excel and Microsoft Powerpoint....

Save Course

Microsoft Power BI Certification: DA-100 Exam Prep

Complete, hands-on guide to passing the DA-100 Power BI Exam and becoming a Microsoft Certified Data Analyst Associate See why Maven Analytics courses are among the TOP-RATED and BEST-SELLING courses on Udemy:...

Save Course