Data Modelling and Analysis with Excel Power Pivot
4.3 (208 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
2,148 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Data Modelling and Analysis with Excel Power Pivot to your Wishlist.

Add to Wishlist

Data Modelling and Analysis with Excel Power Pivot

Excel PowerPivot easily analyses millions of rows of data, create relationships between tables & use DAX calculations
Bestselling
4.3 (208 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
2,148 students enrolled
Created by Ian Littlejohn
Last updated 10/2016
English
Current price: $40 Original price: $100 Discount: 60% off
30-Day Money-Back Guarantee
Includes:
  • 2.5 hours on-demand video
  • 9 Articles
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Learn to use the Power Pivot interface and menu options
  • Easily load data into Power Pivot to Analyze
  • Analyse more than 1 million rows of data
  • Easily create relationships between tables of data
  • Create easy to use Dashboard views from PowerPivot
  • Create Pivot Tables and Pivot Charts from Power Pivot
  • Learn the principles of the DAX formula language to create powerful new calculations
  • Create powerful new DAX measures in PivotTables such as Year to Date, Month to Date, Previous Month etc.
  • Learn to use Sets to analyze your data
View Curriculum
Requirements
  • Excel 2010 / 2013/ 2016 with Power Pivot
  • An understanding of Pivot Tables and Pivot Charts
Description

Join the 1% of Excel users who know how to use the most powerful features and tools in Microsoft Excel

I have been in the It industry since 1979, so I am not new to learning new technology along the way to keep up with it all. Ian Littlejohn's course that I completed is one of the best that have helped impress my colleagues and management at my workplace. Ian's precise and very engaging way of teaching has made my work effortless and an very effective. I use less time now to go through my weekly and monthly data analysis and presentation a breeze. Thanks again Ian. - Jeganathan Vettyvelu

_______________________________________________________________________

Power Pivot has been described as the most important new feature in Excel in 20 years. Power Pivot has been developed by Microsoft to help companies analyze and understand the growing quantities of data that are created on a daily basis.

Power Pivot will improve your time to create reports, develop data analysis and gain insight into your your data by at least 80%.

  • Create relationships between tables of data without Vlookups
  • Create powerful Key Metric and Key Performance Indicator calculations
  • Manage and work with data models of millions of records of data
  • Create powerful new dashboards and reports

Power Pivot is a free Add-in for Excel 2010 and is called the Data Model in Excel 2013 that provides capabilities to handle large quantities of data. PowerPivot is a columnar database that is capable of storing and manipulating hundreds of millions rows of data. PowerPivot brings new capabilities to business users to analyse, explore and gain new insight into business and marketing data. PowerPivot has been referred to as the most important feature for Excel in 20 years.

Power Pivot makes it easy to setup relationships between tables of data in the same way as databases and therefore does not require the use of Excel functions such as Vlookup. PowerPivot therefore decreases the time substantially to analyse and gain new insight from business and marketing data.

Power Pivot allows the rapid development of powerful PivotTables and PivotCharts in Excel utilising the latest Slicer technology to easily analyse and view data from multiple different perspectives. PowerPivot also adds a new Data Analysis Expressions (DAX) formula language that provides new and powerful calculations and insight to the business user.

Who is the target audience?
  • Business users who analyse data with Excel or create management reports
  • Excel users who want to analyze large data sets and also create relationships between tables
  • Excel users who know how to Pivot Tables and Pivot Charts
  • Excel users who wish to create interactive dashboards
Curriculum For This Course
37 Lectures
02:44:00
+
Introduction
6 Lectures 16:22
Welcome to Analyze Data with Power Pivot
01:09

In this lesson we will introduce and cover the features and capabilities of Power Pivot

Preview 03:28

In this lesson we will cover how to activate the Power Pivot addin

Activating Power Pivot in Excel
01:25

Please click on the View Resources button and download the training file

Download the Training Data File
00:03

In this lesson we will cover how to load data into Power Pivot.

Loading Data into PowerPivot
05:20

In this lesson we will review how to browse and filter the data that is loaded into Power Pivot.

Browse, Filter and Sort PowerPivot Data
04:57
+
PowerPivot Menu Options
5 Lectures 35:08

In this lesson we will review the Home menu options on the Power Pivot menu

Home Menu Options
09:17

How to use the AutoSum function to create Sum, Average, Count, Distinct Count, Max and Min calculations

The AutoSum Function in Power Pivot
08:33

In this lesson we will review the Design menu options

Design Menu Options
07:44

In this lesson we will review the Advanced menu options in Power Pivot

The Advanced Menu Option
04:58

In this lesson we review the Excel Power Pivot menu options.

PowerPivot Menu in Excel
04:36
+
Creating Pivot Tables with PowerPivot Data
3 Lectures 11:05

In this lesson we will review how to create Pivot Tables with Power Pivot data.

Preview 03:27

In this lesson we will review how to change the method of aggregation from Sum to Count, Distinct Count, Average, Min and Max.

Changing the Method of Calculation
03:46

In this lesson we will review how to create Pivot Table Show As calculations using Power Pivot data.

Preview 03:52
+
Filtering with Slicers
3 Lectures 14:02

In this lesson we will review how to add slicers to filter data in Pivot Tables.

PivotTable Filters and Slicers
03:53

In this lesson we will review how to filter data in Pivot Tables and Pivot Charts using slicers.

Filtering PivotTables and PivotCharts
05:28

In this lesson we will review how to create hierarchies in Power Pivot data and how to use the hierarchies in Pivot Tables.

Creating Hierarchies
04:41
+
Create Relationships with PowerPivot Data
3 Lectures 10:37

Please download the Training Data files to follow the examples.

Download the Training Data Files
00:07

In this lesson we review how to create relationships between tables of data.

Create Relationships to Master Data
07:02

In this lesson we review how to create Pivot Tables with data from tables that have relationships.

Create PivotTables with Table Relationships
03:28
+
DAX Calculated Columns
9 Lectures 36:16

Please download the Training Data file.

Download the Training Data
00:06

In this lesson we will review how to create DAX Calculated columns in Power Pivot.

Create Calculated Columns in DAX
07:36

In this lesson we will review how to create different date orientated formulas such as Year, Month etc.

Date Functions in PowerPivot
07:56

In this lesson we will review how to use the Sort By Column menu option to control how date orientated fields are displayed in Pivot Tables.

Using the Sort By Option
02:31

Please download the Date Master file to use in the training course.

Download the Date Master File
00:03

In this lesson we will review how to create a relationship with a Date Master file and how to use dates in Pivot Tables.

Create a Date Table
07:22

In this lesson we will review how to create Text formulas in Power Pivot.

Text and Logical Calculated Fields
04:15

Please download the Price Increase Excel file to use in the lessons.

Download the Price Increase Excel file
00:05

In this lesson we will review how to use the related calculated column DAX calculation.

Creating Related Calculated Formulas
06:21
+
DAX Measures
4 Lectures 26:32
Review of Calculated Measures
00:25

In this lesson we review how to create DAX Calculated Fields in Power Pivot.

Power Pivot Calculated Fields
12:15

In this lesson we review the Calculate formula and see how this is able to make powerful custom calculations.

The Calculate Formula
08:01

In this lesson we review how to create Dates in Calculated Fields.

Date Measure Examples
05:51
+
Named Sets and KPIs
3 Lectures 13:38
Download the KPI Workbook
00:04

In this lesson we review how to create Sets that can be used in Pivot Tables. Sets are useful for creating your items in a list for a report.

Creating Named Sets
06:04

In this lesson we review how to create Key Performance Indicators.

Creating KPIs
07:30
+
Conclusion
1 Lecture 00:27
Conclusion
00:27
About the Instructor
Ian Littlejohn
4.4 Average rating
1,502 Reviews
9,534 Students
11 Courses
Excel Power Tools, Power BI and Google Data Studio Trainer

Ian is a trainer that specializes in Microsoft Excel Business Intelligence tools and Google Data Studio.  Ian is an experienced trainer that teaches techniques and tools making it easy for learners to harness the power of Excel PivotTables, PowerPivot, Power Query,  Power BI and Google Data Studio.

Ian has been training learners on these powerful technologies for over 10 years making it easy for business users to easily create management reports, develop interactive dashboards and generate new insight and intelligence from business data.

Ian has over 10 years of Management Consulting experience and he brings this knowledge and skills to his training course showing Excel and Google Data Studio users how to easily create sophisticated management reports, perform data analysis and create amazing interactive dashboards without using any complex programming or specialized tools.

Ian has consulted and worked with major organizations in the Banking, Insurance, Manufacturing, Telecommunications and Logistics industries across a number of countries and continents.