Microsoft Excel - Data Analysis with Excel Pivot Tables
4.7 (550 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.
3,749 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Microsoft Excel - Data Analysis with Excel Pivot Tables to your Wishlist.

Add to Wishlist

Microsoft Excel - Data Analysis with Excel Pivot Tables

Master Excel Pivot Tables with a best-selling Microsoft Excel instructor. Excel 2010, Excel 2013, Excel 2016
Best Selling
4.7 (550 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.
3,749 students enrolled
Created by Chris Dutton
Last updated 7/2017
English
English
Curiosity Sale
Current price: $10 Original price: $175 Discount: 94% off
30-Day Money-Back Guarantee
Includes:
  • 6 hours on-demand video
  • 16 Articles
  • 5 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Skills to take you from ZERO to PRO with Excel's most powerful data analysis tools
  • How to use PivotTables and PivotCharts to streamline and absolutely revolutionize your workflow
  • Unique tips, tools and case studies that you won't find in ANY other course, guaranteed
  • Fun, interactive, and highly effective lessons from a best-selling analytics instructor
  • Lifetime access to reference materials and practice files, quizzes and homework exercises, and 1-on-1 instructor support
View Curriculum
Requirements
  • Microsoft Excel (2010, 2013, 2016), ideally for PC
  • Mac users are welcome, but note that the Pivot Table interface varies across platforms
  • Basic experience with Excel functionality (charts & formulas a plus!)
Description

*** Course access includes downloadable resource files and case studies, quizzes and homework exercises, 1-on-1 instructor support, LIFETIME access and a 100% money-back guarantee! ***

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

Pivots allow you to quickly explore and analyze raw data, 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 Boston condo prices have trended over the past 15 years, Excel Pivot Tables provide fast, accurate and intuitive solutions to even the most complicated questions.

This course gives you a deep, 100% comprehensive understanding of Excel Pivot Tables and Pivot Charts. I'll show you when, why, and how to use Pivot Tables, introduce advanced sorting, filtering, and calculation tools, and guide you through interactive, hands-on demos and 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
  • Table layouts & styles
  • Design & formatting options
  • Sorting, filtering, & grouping tools
  • Calculated fields, items & values
  • Pivot Charts, slicers & timelines
  • Interactive dashboards

__________

We'll then explore and analyze 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
  • And more...

__________

Whether you're looking for a quick 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. I'm here to help you become an analytics ROCK STAR.

Full course includes downloadable resources and project fileshomework and course quizzeslifetime access and a 30-day money-back guarantee. Compatible with Excel 2007, Excel 2010, Excel 2013 or Excel 2016.

Who is the target audience?
  • ANYONE who works with Excel on a regular basis
  • 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
Students Who Viewed This Course Also Viewed
Curriculum For This Course
82 Lectures
05:58:50
+
Before We Dive In
4 Lectures 08:01

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

In this lecture I'll show you how to download the resources that we'll be using throughout the course

Downloading the Course Resources
01:00

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.

Setting Expectations
01:14
+
PivotTable 101
11 Lectures 43:13

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 PivotTables?
04:10

A PivotTable 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.

Structuring Your Source Data
02:00

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

Inserting Your First PivotTable
03:13

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 with ease.

Navigating the Field List
07:43

KNOWLEDGE CHECK: Manipulating PivotTable Views
00:10

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

PivotTable Options: "Analyze" & "Design"
01:33

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

Selecting, Clearing, Moving & Copying Pivots
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 source data range.

Refreshing & Updating Pivots
07:30

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

PRO TIP: Dealing with Growing Source Data
06:49

For those looking to dig a bit deeper, this lecture demonstrates how PivotTable values are actually calculated and displayed based on given field settings.

How PivotTables ACTUALLY Work
03:39

QUIZ: PivotTable 101
5 questions

HOMEWORK: PivotTable 101
01:12
+
PivotTable Formatting
8 Lectures 22:56

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

Number Formatting
02:12

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

PRO TIP: Automatically Formatting Empty Cells
01:41

There are several options when it comes to 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.

Table Layouts & Styles
05:30

PivotTables can be an incredibly useful tool for creating brand new tables from existing source data, 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 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 Headers & Labels
01:16

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

Preview 06:51

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

PRO TIP: Data Bars with Invisible Text
02:13

QUIZ: PivotTable Formatting
4 questions

HOMEWORK: PivotTable Formatting
00:53
+
Sorting, Filtering & Grouping
11 Lectures 42:06

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

Sorting Options
05:01

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

PRO TIP: Why is my Alphabetical Sorting Wrong?
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 PivotTable views.

Label Filters & Manual Selections
08:15

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

PRO TIP: Using 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 PivotTable views.

Value Filters
03:10

Learn how to adjust PivotTable settings to allow you to apply multiple filters 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 the most common automatic and manual grouping techniques.

Grouping Data
03:23

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

PRO TIP: Automatic Date Grouping
04:25

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

Using Slicers & Timelines to Filter Data
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.

Breaking Out Report Filter Pages
04:13

QUIZ: Sorting, Filtering & Grouping
6 questions

HOMEWORK: Sorting, Filtering & Grouping
00:50
+
Calculated Values, Fields & Items
17 Lectures 01:12:26

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

"Summarize Values By" Options
04:45

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

PRO TIP: Avoiding the "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 the most common and powerful options, including % of Column, % of Parent, Difference From, Running Total, and more.

Preview 03:35

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

Show Values As: % of Column/Row
03:47

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

Show Values As: % of Parent
03:02

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

Show Values As: Difference From
04:42

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

Show Values As: Running Total
02:44

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

Show Values As: Rank
02:36

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

Show Values As: Index
08:06

KNOWLEDGE CHECK: Show Values As
00:19

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 based on existing fields.

Inserting Calculated Fields
06:30

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

Calculations in Pivots vs. Raw Data
04:32

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

PRO TIP: Calculating Using Counts
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.

PRO TIP: Calculating Using Counts (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 PivotTables.

Inserting a Calculated Item (not recommended!)
06:11

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

The Solve Order & List Formulas Tools
04:54

QUIZ: Calculated Values & Fields
7 questions

HOMEWORK: Calculated Values & Fields
01:15
+
PivotCharts
12 Lectures 55:20

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

Intro to PivotCharts
02:26

In this lecture, we'll practice creating a PivotChart in the form of a Clustered Column chart.

PivotChart Demo: Column Chart
04:13

In this lecture, we'll practice creating a PivotChart in the form of a Pie or Donut chart.

PivotChart Demo: Pie/Donut Chart
05:46

In this lecture, we'll practice creating a PivotChart in the form of a Clustered Bar chart.

PivotChart Demo: Clustered Bar Chart
03:25

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

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

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

Changing Chart Types on the Fly
03:18

In this lecture, we'll practice creating a PivotChart in the form of a Stacked Area chart.

PivotChart Demo: Stacked Area Chart
05:46

In this lecture we'll practice customizes the look and feel of a PivotChart using layout and style options.

PivotChart Layouts & Styles
04:03

In this lecture I'll demonstrate the pros and cons of moving a PivotChart to its own separate worksheet.

Moving PivotCharts to New Sheets
02:09

In this lecture, we'll practice inserting slicers and timelines and applying them to multiple PivotTables and PivotCharts.

Applying Slicers & Timelines to Multiple Charts
05:01

In this demo, we'll build a dynamic dashboard using PivotCharts, slicers and timelines.

Building a Dynamic Dashboard
13:22

QUIZ: PivotCharts
6 questions

HOMEWORK: PivotCharts
01:06
+
PivotTable Case Studies
17 Lectures 01:52:42

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

In this 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.

U.S. Voters (2012)
10:33

HOMEWORK: U.S. Voters (2012)
00:47

In this 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.

San Francisco Salaries
13:06

HOMEWORK: San Francisco Salaries
00:43

In this 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.

Shark Attack Records
10:31

HOMEWORK: Shark Attack Records
00:55

In this 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: Stock Market Data
00:37

In this 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).

Baseball Team Stats
14:16

HOMEWORK: Baseball Team Stats
00:50

In this 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.

San Diego Burrito Ratings
16:40

HOMEWORK: San Diego Burrito Ratings
00:45

In this 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.

Daily Weather Conditions
12:41

HOMEWORK: Daily Weather Conditions
00:47

In this 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).

Spartan Race Facebook Posts
14:37

HOMEWORK: Spartan Race Facebook Posts
00:41
+
Wrapping Up
2 Lectures 02:51

In this lecture I'll leave you with some additional resources to dive into, including the other two courses in this series: Advanced Excel Formulas & Functions and Excel Charts & Graphs.

Resources & Next Steps
02:27

Bonus Lecture: More from Excel Maven
00:24
About the Instructor
Chris Dutton
4.7 Average rating
2,485 Reviews
12,522 Students
3 Courses
Best-selling Udemy Instructor & Founder, Excel Maven

Chris Dutton is a certified Microsoft Excel Expert, analytics consultant, and best-selling Udemy instructor with more than a decade of experience specializing in business intelligence, marketing analytics and data visualization. 

He founded Excel Maven in 2014 to provide high-quality, applied analytics training and consulting to clients around the world, and now mentors 10,000+ students in more than 130 countries. He has developed award-winning data analytics and visualization tools, which have been featured by Microsoft, the New York Times, and the Society of American Baseball Research.

Current Udemy courses include Advanced Excel Formulas & Functions, Data Visualization with Excel Charts & Graphs, and Data Analysis with Excel Pivot Tables.