Microsoft Excel - Data Analysis with Excel Pivot Tables
4.6 (19,354 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
104,936 students enrolled

Microsoft Excel - Data Analysis with Excel Pivot Tables

Master Excel Pivot Tables & data analysis with real-world cases from a best-selling Excel instructor (Excel 2010-2019)
Bestseller
4.6 (19,354 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
104,936 students enrolled
Last updated 6/2020
English
English [Auto], French [Auto], 5 more
  • German [Auto]
  • Indonesian [Auto]
  • Italian [Auto]
  • Portuguese [Auto]
  • Spanish [Auto]
Current price: $121.99 Original price: $174.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 6.5 hours on-demand video
  • 22 articles
  • 6 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Take your data analysis skills from ZERO to PRO with Excel Pivot Tables
  • Learn how to use Pivot Tables and Pivot Charts to streamline and absolutely revolutionize your workflow in Excel
  • Master unique Pivot Table tips, tools and case studies that you won't find in ANY other course, guaranteed
  • Explore fun, interactive, and highly effective lessons from a best-selling Excel instructor
  • Get LIFETIME access to project files, quizzes, homework exercises, and 1-on-1 expert support
  • Test your skills with 10 real-world Pivot Table case studies (weather, shark attacks, wine tastings, burrito ratings, and more!)
Requirements
  • Microsoft Excel 2010-2019 or Office 365 (ideally for PC/Windows)
  • Mac users are welcome, but note that the Pivot Table interface varies across platforms
  • Basic experience with Excel functionality (charts & formulas a plus!)
Description

Hear why this is one of the TOP-RATED Excel courses on Udemy, and the #1 Excel Pivot Table course:

"This is a great Excel course. You can feel confident putting these skills on your resume, and the lectures are in-depth and easy to follow. I would highly recommend this course to anyone who wants to not just LEARN Excel Pivot Tables, but become a true Pivot Table EXPERT"

-Monique C.

"Chris provides clear, detailed explanations and shows how to use Excel Pivot Tables to extract hidden details and facts using raw, real-life data sets in Excel. The quizzes and homework reinforce the lessons and the course layout makes it easy to go back to sections that need revision. This course is exactly what I was looking for to master Excel Pivot Tables. Thanks, Chris!"

-Paul M.

"One of the very best Excel classes I've taken - great instructor, awesome production, very comprehensive and exceptionally useful. I have added other Excel and Pivot Table courses from Chris & Maven Analytics and look forward to viewing them as well."

-Ernie A.

__________

FULL COURSE DESCRIPTION:

Excel Pivot Tables are an absolutely essential tool for anyone working with data in Excel.

Pivots allow you to quickly explore and analyze raw data in Excel, revealing powerful insights and trends otherwise buried in the noise. In other words, they give you answers. Whether you're exploring product sales, analyzing which marketing tactics drove the strongest conversion rates, or wondering how condo prices have trended over the past 10 years, Excel Pivot Tables provide fast, accurate and intuitive solutions to even the most complicated analytics questions.

This course gives you a deep, 100% comprehensive understanding of Excel Pivot Tables and Excel Pivot Charts. I'll show you when, why, and how to use Excel Pivot Tables, introduce advanced sorting, filtering, and calculation tools, and guide you through interactive, hands-on demos and Excel exercises every step of the way.

__________

We'll start by covering everything you need to know to get up and running with Excel Pivot Tables, including:

  • Raw data structure in Excel

  • Pivot Table layouts & styles

  • Design & formatting options

  • Sorting, filtering, & grouping tools

  • Calculated fields, items & values

  • Pivot Charts, slicers & timelines

  • Interactive Excel dashboards

__________

We'll then explore and analyze Excel datasets from a number of real-world case studies, including:

  • San Diego burrito ratings

  • Shark attack records from 1900-2016

  • Facebook Post data from Spartan Race

  • Major League Baseball team statistics

  • San Francisco employee salaries

  • Daily stock market data

  • IMDb movie ratings

  • Apple Store mobile app ratings

  • Wine ratings and descriptions

__________

Whether you're looking for a quick Excel Pivot Table primer, trying to diversify your Excel skill set, or hoping to step up your analytics game in a major way, you've come to the right place. In today's increasingly data-driven world, analytics skills are in short supply and incredibly high demand, and those with the ability to transform data into insight are leading the charge. Enroll today and start your journey to becoming an Excel analytics pro!

See you there!

-Chris (Founder, Maven Analytics)

__________

Most students in this course enroll in our full EXCEL LEARNING PATH, designed to help you build a deep, expert-level Excel skill set:

  1. Advanced Excel Formulas & Functions

  2. Data Visualization with Excel Charts & Graphs

  3. Data Analysis with Excel PivotTables (you are here)

  4. Excel Power Query, Power Pivot & DAX

  5. Excel Pro Tips for Power Users

Looking for the full business intelligence stack? Search for "Maven Analytics" to browse our full course library, including Excel, Power BI, MySQL, and Tableau courses!

*NOTE: Full course includes downloadable resources and Excel project files, homework and course quizzes, lifetime access and a 30-day money-back guarantee. Most lectures compatible with Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019 or Office 365.

Who this course is for:
  • ANYONE who works with Excel on a regular basis (even if you've never used a Pivot Table!)
  • Excel users who have basic skills but would like to become more proficient in data exploration and analysis
  • Students looking for a comprehensive, engaging, and highly interactive approach to training
  • Anyone looking to pursue a career in data analysis or business intelligence
Course content
Expand all 90 lectures 06:50:07
+ Getting Started
5 lectures 09:07

In this lecture we'll get familiar with the course materials and curriculum, including core topic areas, downloadable resources, homework exercises and quizzes.

Preview 03:26
READ ME: Important Notes for New Students
01:54
DOWNLOAD: Course Resources
00:11

In this lecture I'll introduce you to the IMDb Movie Database, which we'll be working with through the first several sections of the course.

Preview 02:21

In this lecture, I'll outline some key assumptions and expectations to keep in mind before diving in.

Preview 01:14
+ Excel Pivot Table 101
13 lectures 49:37

Learn how PivotTables are used, and why they are one of the most powerful and versatile tools for analyzing and exploring raw data in Excel.

Why Pivot Tables?
04:10

A Pivot Table is only as strong as the data behind it. Let's take a few minutes to talk about some key DO's and DON'Ts of preparing raw data for analysis using Excel Pivot Tables.

Structuring Source Data for Analysis in Excel
02:00

In this lecture we'll explore a few different ways to easily insert PivotTables in Excel.

Creating Your First Pivot Table
03:13
IMPORTANT: Add to Data Model
00:29

When you analyze data with a PivotTable, think of yourself as a pilot and the Field List as your cockpit. In this lecture, we'll learn how to use the field list to slice, dice, and filter our data in Excel with ease.

Navigating the Pivot Table Field List
07:43
KNOWLEDGE CHECK: Manipulating the Pivot Table Field List
00:28

In this lecture, we'll review some of the tools available in the Excel PivotTable "Analyze" and "Design" tabs, including slicers and timelines, calculated fields, PivotCharts and table styles.

Exploring Pivot Table Analyze & Design Options
01:33

Learn how to clear, select, move and copy Excel PivotTables using options from the "Analyze" tab.

Selecting, Clearing, Moving & Copying Pivot Tables
05:14

Learn how easy it is to refresh and update PivotTables as your source data changes, and understand the difference between changes made within or outside of your Excel source data range.

Refreshing & Updating Pivot Tables
07:30

Learn how to use Excel tables or column-only PivotTable source references to deal with data that consistently grows over time.

PRO TIP: Dealing with Growing Source Data
06:49

In this lecture I'll explain the purpose of Excel's PivotTable cache, describe how it can be used to reduce file sizes by storing data in memory, and demonstrate how to revive source data from cache using the "show details" option.

Preview 05:41

For those looking to dig a bit deeper, this lecture demonstrates how PivotTable values are actually calculated and displayed based on your table layout.

PRO TIP: How Excel Pivot Tables ACTUALLY Work
03:39
QUIZ: Excel Pivot Table 101
5 questions
HOMEWORK: Excel Pivot Table 101
01:08
+ Formatting Data with Pivot Tables
9 lectures 29:45

In this lecture, we'll discuss a few different ways to customize number formats in Excel PivotTables (dates, currency, percentages, etc.)

Changing Pivot Table Number Formats
02:12

Learn how to adjust your PivotTable settings to apply a default value to blank cells.

PRO TIP: Formatting Empty Pivot Table Cells
01:41

There are several options when it comes to Excel PivotTable report layouts, and a number of ways to customize the look and feel. In this lecture, we'll review these options and discuss the pros and cons of each.

Configuring Pivot Table Layouts & Styles
05:30

PivotTables can be an incredibly useful tool for creating brand new tables from existing source data in Excel, whether you want to aggregate your data at a different level of granularity, eliminate certain fields, or add new calculated metrics. This lecture demonstrates how this can be done using specific table layouts and design tools.

PRO TIP: Using Tabular Pivot Tables to Create New Source Data
02:20

In this lecture, we'll learn how to change and customize labels and headers in Excel pivots.

Customizing Pivot Table Headers & Labels
01:16

In this lecture, we'll demonstrate how to use Excel's conditional formatting tools (like color scales and icon sets) to visualize patterns in your data and draw attention to notable trends within your PivotTable.

Applying Basic Conditional Formats to Pivot Tables
06:51

In this lecture I'll show you how to hide text to prevent it from overlapping with data bars, using a custom Excel formatting rule.

PRO TIP: Adding Data Bars with Invisible Text
02:13

In this lecture I'll show you how to change the scope of your conditional formatting rules to customize how they react to changes in your Excel PivotTable layout.

Preview 06:51
QUIZ: Excel Pivot Table Formatting
4 questions
HOMEWORK: Excel Pivot Table Formatting
00:51
+ Sorting, Filtering & Grouping Data with Pivot Tables
11 lectures 42:12

In this lecture, we'll review basic Excel PivotTable sorting options, including manual, alphabetical and value-based sorting.

Sorting Data with Pivot Tables
05:01

In this lecture, we'll explore cases where sorting may give unexpected results due to Excel's deafult "custom lists".

PRO TIP: Fixing Incorrect Alphabetical Sorting
02:01

Label filters allow you to include or exclude items using text-based criteria (i.e. begins with, ends with, contains, does not contain, etc). This lecture demonstrates several ways to use these label filters in your Excel PivotTable views.

Filtering Data with Pivot Table Label & Selection Filters
08:15

Wildcards allow you to create more complex and flexible label filters within Excel PivotTables. In this lecture we'll practice using two variations of these wilcards: the asterisk (*) and question mark (?). 

PRO TIP: Pivot Table Label Filters with Wildcards
03:15

Value filters allow you to include or exclude items using numerical or value-based criteria (i.e. greater than, less than, equal to, etc). This lecture demonstrates several ways to use these value filters in your Excel PivotTable views.

Filtering Data with Pivot Table Value Filters
03:10

Learn how to adjust Excel PivotTable settings to allow you to apply multiple filters (label + value) to the same field. 

Preview 02:42

Grouping options allow you to combine or aggregate data however you choose. In this lecture we'll walk through some of Excel's most common automatic and manual grouping techniques.

Grouping & Segmenting Data with Pivot Tables
03:23

In this demonstration we'll show how daily data can be automatically grouped in Excel to summarize data by month, quarter, year, etc.

PRO TIP: Enabling & Disabling Automatic Date Grouping
04:25

In this lecture, we'll practice using Excel slicers and timelines to add visual filtering tools to a PivotTable. 

Filtering Data with Pivot Table Slicers & Timelines
04:51

If you need to generate multiple copies of a PivotTable view with different filter settings (i.e. a view of product sales for each region or country), the "Report Filter Pages" option is a lifesaver. In this lecture, we'll see how this tool can be used to instantly break out multiple views into their own Excel worksheets.

Breaking Out Pivot Table Report Filter Pages
04:13
QUIZ: Sorting, Filtering & Grouping Data with Excel Pivot Tables
6 questions
HOMEWORK: Sorting, Filtering & Grouping Data with Excel Pivot Tables
00:56
+ Enriching Data with Pivot Table Calculated Values & Fields
17 lectures 01:12:31

In this lecture, we'll explore different ways to summarize values within Excel PivotTables, including Sum, Count, Average, Max, Min and more.

Aggregating & Summarizing Data with Pivot Tables
04:45

In this demo, we'll talk about the difference between "Sum Of" and "Count of", and why Excel PivotTables sometimes default to counting values instead of summing them.

PRO TIP: Avoiding the Pivot Table "Count Of" Trap
03:49

One of the most powerful PivotTable features is the ability to display values in multiple ways. This lecture introduces some of Excel's most common and powerful options, including % of Column, % of Parent, Difference From, Running Total, and more.

Defining Value Calculations with Pivot Tables
03:35

In this lecture, we'll practice using "% of Column" and "% of Row" value calculations in our Excel PivotTable.

Calculating Pivot Table Values: % of Column/Row
03:47

In this lecture, we'll practice using "% of Parent" value calculations in our Excel PivotTable.

Calculating Pivot Table Values: % of Parent
03:02

In this lecture, we'll practice using "Difference From" and "% Difference From" value calculations in our Excel PivotTable.

Calculating Pivot Table Values: Difference From
04:42

In this lecture, we'll practice using "Running Total" and "% Running Total" value calculations in our Excel PivotTable.

Calculating Pivot Table Values: Running Total
02:44

In this lecture, we'll practice using "Rank" value calculations in our Excel PivotTable.

Calculating Pivot Table Values: Rank
02:36

In this demo, we'll transform PivotTable values into index numbers and explain how the approach can be used as a powerful  analytical tool in Excel.

Calculating Pivot Table Values: Index
08:06
KNOWLEDGE CHECK: Pivot Table Value Calculations
00:20

Calculated fields are one of the most powerful PivotTable tools. In this lecture we'll explore some common ways to create new values and calculations in Excel based on existing PivotTable fields.

Defining Calculated Fields with Pivot Tables
06:30

In this demo, we'll see why you should always create calculated "rate" metrics in your Excel PivotTable, as opposed to your raw data range.

Creating Calculated Fields in Pivot Tables vs. Source Data
04:32

In this lecture we'll demonstrate how adding a simple "Counting Column" in your raw Excel data can enable powerful PivotTable calculations and analysis tools.

PRO TIP: Pivot Table Calculations Using Count Columns (Part 1)
07:20

In this lecture we'll explore a second example of using a "Counting Column" in the raw data to enable more complicated calculated fields in Excel.

PRO TIP: Pivot Table Calculations Using Count Columns (Part 2)
04:19

Calculated items are essentially the text equivalent of calculated fields. In this lecture we'll explore why (and why NOT) to use calculated items in your Excel PivotTables.

Inserting Pivot Table Calculated Items (USE WITH CAUTION!)
06:11

In this lecture, we'll demonstrate how to customize the solve order for fields that are part of multiple calculations within an Excel PivotTable.

Generating Solve Order & List Formulas Reports
04:54
QUIZ: Enriching Data with Pivot Table Calculated Values & Fields
7 questions
HOMEWORK: Enriching Data with Pivot Table Calculated Values & Fields
01:19
+ Visualizing Data with Excel Pivot Charts
12 lectures 55:19

In this lecture, we'll introduce some of the pros and cons of using Excel Pivot Charts for data analysis.

Intro to Excel Pivot Charts
02:26

In this lecture, we'll practice creating an Excel Pivot Chart in the form of a Clustered Column chart.

Pivot Chart Demo: Column Chart
04:13

In this lecture, we'll practice creating an Excel Pivot Chart in the form of a Pie or Donut chart.

Pivot Chart Demo: Pie & Donut Charts
05:46

In this lecture, we'll practice creating an Excel Pivot Chart in the form of a Clustered Bar chart.

Pivot Chart Demo: Clustered Bar Chart
03:25

In this lecture, I'll explain how to prevent Excel charts from resizing and distorting when the underlying rows or columns are changed.

PRO TIP: Preventing Charts from Resizing with Cells
04:45

Changing Excel Pivot Chart types is incredibly easy. In this lecture, we'll practice modifying our charts using the Pivot Chart Design tools.

Changing Pivot Chart Types
03:18

In this lecture, we'll practice creating an Excel Pivot Chart in the form of a Stacked Area chart.

Pivot Chart Demo: Stacked Area Chart
05:46

In this lecture we'll practice customizes the look and feel of Excel Pivot Charts using layout and style options.

Customizing Pivot Chart Layouts & Styles
04:03

In this lecture I'll demonstrate the pros and cons of moving an Excel Pivot Chart to its own separate worksheet.

Moving Pivot Charts to New Excel Worksheets
02:09

In this lecture, we'll practice inserting slicers and timelines and applying them to multiple Pivot Tables and Pivot Charts in Excel.

Applying Slicers & Timelines to Multiple Pivot Charts
05:01

In this demo, we'll practice building a dynamic Excel dashboard using Pivot Charts, slicers and timelines.

Preview 13:22
QUIZ: Visualizing Data with Excel Pivot Charts
6 questions
HOMEWORK: Visualizing Data with Excel Pivot Charts
01:05
+ Excel Pivot Table Case Studies
22 lectures 02:30:46

In this lecture, I'll introduce the Case Study section of the course and lay out some expectations before we dive in.

Setting Expectations
01:45
DOWNLOAD: Case Study Resources
00:10

In this Excel PivotTable case study we'll explore U.S. Census Bureau data from the 2012 presidential election, including population and registered voter counts broken down by state and age group.

Pivot Table Demo: Analyzing U.S. Voter Demographics
10:33
HOMEWORK: Analyzing U.S. Voter Demographics
00:43

In this Excel PivotTable case study we'll review salary information for government employees in San Francisco, including employee names and titles as well as base and overtime pay from 2011-2013.

Pivot Table Demo: Analyzing San Francisco Salary Data
13:06
HOMEWORK: Analyzing San Francisco Salary Data
00:40

In this Excel PivotTable case study we'll explore recorded shark attack records from 1900-2016. Dimensions include the date and location of the attack, victim demographics and activity, shark species, and whether or not the attack was provoked.

Pivot Table Demo: Exploring Shark Attack Records
10:31
HOMEWORK: Exploring Shark Attack Records
00:54

In this Excel PivotTable case study we'll explore a 3-month sample of US stock market data, including Open, High, Low and Close prices as well as trading volume for 500 individual stocks.

Preview 12:25
HOMEWORK: Analyzing Stock Market Data
00:34

In this Excel PivotTable case study we'll take a look at Major League Baseball team statistics from 1995-2015, including games played, wins, losses and post-season results, along with hitting and pitching statistics (R, HR, RBI, ERA, etc).

Pivot Table Demo: Analyzing Major League Baseball Teams
14:16
HOMEWORK: Analyzing Major League Baseball Teams
00:55

In this Excel PivotTable case study we'll explore customer-submitted burrito ratings from a number of Mexican restaurants in San Diego. Data includes the burrito and restaurant name as well as 0-5 ratings based on tortilla quality, temperature, meat volume, uniformity, synergy, and more.

Pivot Table Demo: Exploring San Diego Burrito Ratings
16:40
HOMEWORK: Exploring San Diego Burrito Ratings
00:43

In this Excel PivotTable case study we'll analyze daily weather conditions for Jan-Dec 2016, including max, min, and mean temperature, wind speed, total precipitation and weather conditions.

Pivot Table Demo: Tracking Daily Weather Conditions
12:41
HOMEWORK: Tracking Daily Weather Conditions
00:40

In this Excel PivotTable case study we'll analyze actual post-level Facebook data from Spartan Race. Data includes the date and time of the post, post type and copy, and number of engagements (likes, comments, shares, etc).

Pivot Table Demo: Analyzing Spartan Race Facebook Posts
14:37
HOMEWORK: Analyzing Spartan Race Facebook Posts
00:39

In this Excel PivotTable case study we'll explore ratings, prices, and common attributes of 7,000+ mobile apps available in the Apple App Store.

Pivot Table Demo: Analyzing Apple App Store Data
18:39
HOMEWORK: Analyzing Apple App Store Data
01:17

In this Excel PivotTable case study we'll analyze information from 130,000+ wine tastings, including average point ratings, prices, and written reviews.   

Preview 17:13
HOMEWORK: Analyzing Wine Tasting Scores
01:02
+ Wrapping Up
1 lecture 00:47
More from Maven Analytics
00:47