Excel Pivot Tables for Management Consultants & Analysts
4.5 (329 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.
4,324 students enrolled

Excel Pivot Tables for Management Consultants & Analysts

Practical guide how to do data and business analyses using Pivot Tables during consulting projects
4.5 (325 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.
4,324 students enrolled
Created by Asen Gyczew
Last updated 7/2020
English
English [Auto]
Current price: $69.99 Original price: $99.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 2.5 hours on-demand video
  • 2 articles
  • 79 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
  • You will master the most crucial functions and features of Excel Pivot Tables
  • Understand the main challenges in analyzing data with Pivot Tables
  • Do calculation directly in the Pivot Table without impacting the original data set
  • Perform the analyses in a very effective manner
  • Build Dashboards using Pivot Tables and Charts
Requirements
  • Basic Excel
  • Basic knowledge of economics or finance
Description

What is the aim of this course?  

Excel is the most often used first-choice tool of every business analyst and consultant. Maybe it is not the most fancy or sophisticated one, yet it is universally understood by everybody especially your boss and your customers.  

Excel is still pretty advanced solution with countless number of features and functions. One of the most useful Excel tools is the Pivot Tables that help you do fast and efficiently data analyses. In this course I will show you how to use the full potential of Pivot Tables during consulting projects. This course is organized around 80/20 rule and I want to teach you the most useful (from business analyst / consultant perspective) features of Pivot Tables as fast as possible. I want you also to acquire thanks to the course good habits in Excel that will save you loads of time.  

If done properly, this course will transform you in 1 day into pretty good business analyst that knows how to use Pivot Tables Excel in the smart way. It is based on my 14 years of experience as a consultant in top consulting companies and as a Board Member responsible for strategy, improvement and turn-arounds in biggest companies from FMCG, SMG, B2B sector that I worked for.  On the basis of what you will find in this course I have trained in person over 100 business analysts who now are Investment Directors, Senior Analyst, Directors in Consulting Companies, Board Members etc. On top of that my courses on Udemy were already taken by more than 34 000 students.  

I teach step by step on the basis of Excel files that will be attached to the course. To make the most out of the course you should follow my steps and repeat what I do with the data after every lecture. Don’t move to the next lecture if you have not done what I show in the lecture that you have gone through.  

I assume that you know basic Excel so the basic features (i.e. how to write formula in Excel) are not explained in this course. I concentrate on intermediate and advanced solutions and purposefully get rid of some things that are advanced yet later become very inflexible and useless (i.e. naming the variables). At the end, I will show full blown analyses using Pivot Tables that use the tricks that I show in the lectures.  

To every lecture you will find attached  (in additional resources) the Excel files shown in the lectures. In this way, as a part of this course, you will also get a library of ready-made analyses that can, with certain modification, be applied by you in your work.  

   

Why I decided to create this course?  

I have done number of courses showing you how to analyze data in Excel. Yet, I have noticed that some students lack the fluency of operations in Excel, especially when it comes to Pivot Tables. This course is designed to fill in the gap and help you fully appreciate my other courses for business analysts and consultants. It can be used also as standalone course that will help you to be smart with Excel Pivot Tables. I recommend it especially to people that have to work in Excel 2-3 hours a day or want to become management consultants.  

                                                       

In what way will you benefit from this course?  

The course is a practical, step by step guide loaded with tones of analyses, tricks, hints that will significantly improve the speed with which you do the analyses using Pivot Tables as well as the quality of the conclusions coming out of available in your company data. There is little theory – mainly examples, a lot of tips from my own experience as well as other notable examples worth mentioning. Our intention is that thanks to the course you will know:  

  • How to use Pivot Tables in practice?  

  • How to calculate things directly in Pivot Tables  

  • How to draw conclusions from analyses – chosen examples of analyses  

  • How to improve the look & feel of Pivot Tables and Pivot Charts  

  • How to use Pivot Charts to present the data & conclusions  

You can also ask me any question either through the discussion mode or by messaging me directly.  

   

How the course is organized?  

The course is divided currently in 7 sections and will be adding new section to address other important issues. Currently you will find the following sections:  

  • Introduction. We begin with little intro into the course  

  • Basic Usage of Pivot Tables. In this section I will show you most      important features of Pivot Tables that will drastically increase the      speed of your data analysis. We will discuss how to build Pivot Table,      Pivot Chart, how to replaces Pivot Tables with some other functions. We      will also go through an example of an analysis done using Pivot Table  

  • Calculation in the Pivot Tables. Quite often you want to do a lot of      calculations on the basis of the original data. You can do it in the      original table or directly in the Pivot Table. In this section you will      learn how to do it in the Pivot Tables. We will discuss the calculation      field, “show as…” option and many others.  

  • Other useful pre-defined options. Pivot tables have many pre-defined options      such as sorting, filtering, changing source data, adding new variable etc.      We will discuss them in this section and will show you some examples how      to use them in practice.  

  • Improving the look and feel of the table. In the 5###sup/sup### section      I will show you how to improve the look and feel of the Pivot Tables.  

  • Pivot Charts. In this section I will go into details when it comes to Pivot Charts.      I will also show you how to create a Dashboard using Pivot Charts and      slicers.  

  • Examples of analysis using Pivot Tables. Here I will show examples of real life      data analyses using Pivot Tables

We will be adding new sections in the coming months  

   

You will be able also to download many additional resources  

  • Excels with analyses shown in the course

  • Links to additional presentations and movies

  • Links to books worth reading

   

At the end of my course, students will be able to…  

  • You will master the most crucial functions and features of Excel Pivot Tables

  • Understand the main challenges in analyzing data with Pivot Tables

  • Do calculation directly in the Pivot Table without impacting the original data set

  • Perform the analyses in a very effective manner

  • Build Dashboards using Pivot Tables and Charts

Who should take this course? Who should not?  

  • Business analysts

  • Consultants

  • Students that want to work in Management Consultants

  • Researchers

  • Students that want to work in Private Equity

  • Controllers

  • Small and medium business owners

  • Startups founders

Who this course is for:
  • Business analysts
  • Management Consultants
  • Students that want to work in Management Consultants
  • Researchers
  • Students that want to work in Private Equity
  • Controllers
  • Small and medium business owners
  • Startups founders
Course content
Expand all 63 lectures 02:35:10
+ Introduction
6 lectures 11:23

Excel is still pretty advanced solution with countless number of features and functions. One of the most useful Excel tools is the Pivot Tables that help you do fast and efficiently data analyses. In this course I will show you how to use the full potential of Pivot Tables during consulting projects. This course is organized around 80/20 rule and I want to teach you the most useful (from business analyst / consultant perspective) features of Pivot Tables as fast as possible. I want you also to acquire thanks to the course good habits in Excel that will save you loads of time.

Preview 02:13

A few words about your humble teacher

Preview 01:47

Here I will show you how the course is organized

Preview 01:54

Here I give you some tips how you can get the best out of the course

Preview 02:20

Here I will show you what to do if a blurry image appears

What to do if a blurry image appears
02:04

Here I will show you how to find additional resources attached to the coruse like Excel files, presentations, links etc.

How to find additional resources
01:05
+ Basic Usage of Pivot Tables
10 lectures 28:48

In this section I will show you most important features of Pivot Tables that will drastically increase the speed of your data analysis. We will discuss how to build Pivot Table, Pivot Chart, how to replaces Pivot Tables with some other functions. We will also go through an example of an analysis done using Pivot Table

Preview 01:51

Pivot Tables help you group data and analyze them fast. You can go from general to specific within seconds thanks to pivot tables. I will show you in this lecture how to use pivot tables, what you can use instead

Preview 01:24

We start with basic usage of pivot tables

Preview 05:28

A cousin of pivot tables is pivot chart. I will show you how to use the pivot charts and how does it relate to pivot tables

Preview 02:39

It is not easy to get data from pivot tables. I will show you how to do it without any complications and special formulas

How to combine pivot tables with regular calculations?
02:17

Slicers are nice add-on to pivot tables and pivot charts that enable you fast filtering without any knowledge of pivot tables. They can be successfully used to create i.e. dashboards

Preview 02:07

Sometimes you need more Excel like to build on the basis of this. I will show you how to get the same results without pivot tables

How to replace pivot tables with SUMIFS and COUNTIFS
03:17

Pivot tables enables you to go from general to specific. I will show you how to do it 

Going from general to specific with pivots
01:57

Most producers / brand owners operate using many sales channels: wholesalers, own shops, e-commerce, marketplace etc. It makes sense to check how profitability looks across channels and what can e done to improve overall situation. I will show you how it cane be done with a simple Excel analysis and a pivot table. I will go also try to demonstrate what kind of conclusions can you draw and what should be your next steps, on the basis of the results you are getting form the analyses


Example of analyses using pivot tables- Analysis of sales channels for FMCG
03:06

Conditional Formating helps you data be more understandable to people. This is great tool for creating dashboards. I will show you how to use them

Conditional Formating used on Pivot Tabels
04:42
+ Calculation in the Pivot Tables
13 lectures 35:57

Quite often you want to do a lot of calculations on the basis of the original data. You can do it in the original table or directly in the Pivot Table. In this section you will learn how to do it in the Pivot Tables. We will discuss the calculation field, “show as…” option and many others.

Preview 01:24

In Pivot Table you can directly calculate new values and present them without impacting the original table. In this lecture I will show you how to use the calculated filed in Pivot tables. We will calculate Average Item Price

Preview 03:26

In Pivot Table you can directly calculate new values and present them without impacting the original table. In this lecture I will show you how to use the calculated filed in Pivot tables. We will calculate Average Weighted Sales Density

Preview 02:34

In Pivot Table you can directly calculate new values and present them without impacting the original table. In this lecture I will show you how to use the calculated filed in Pivot tables. We will calculate Average Weighted Sales Density

Preview 02:45

You can change the previously created formula and calculations. Here I will recalculate revenues into USD from EUR and I will show you how to modify this function

Modifying Calculated fields – Recalculate to USD
02:25

If you create the average for calculated filed you need to use a nice trick. I will show you in this lecture how to do it

How to create averages for calculated fields – Average Front Margin
04:31

You can switch the default option in Pivot Tables from sum to count, average etc. I will show you how to do it

Switching the basic calculation to different options
02:38

Pivot Tables allows you to show the values in different format, with different point of reference. I will show you witch options are on the table

Show values as Option – Introduction
01:53

In this lecture I will show you how to show the values in the Pivot table as a percentage of Total in the whole table, total of the row or total of the column

Show values as % of Total / Row / Column
02:52

In this lecture I will show you how to show the values in the Pivot table as a percentage of the total for specific subgroup (Parent)

Show values as % of Parent
01:52

In this lecture I will show you how to show the values in the Pivot table as a difference from the values from specific record of the previous record

Show values as a Difference From
03:36

In this lecture I will show you how to show the values in the Pivot table as a running total

Show values as Running Total
02:56

In this lecture I will show you how to show the values after ranking them

Show values as a Rank
03:05
+ Other useful pre-defined options
11 lectures 24:18

Pivot tables have many pre-defined options such as sorting, filtering, changing source data, adding new variable etc. We will discuss them in this section and will show you some examples how to use them in practice.

Preview 01:07

In this lecture I will show you how to create a copy of already existing Pivot Table

Preview 02:03

In this lecture I will show you how to move the created Pivot into new sheet

Moving Pivots to new sheets
01:33

In this lecture I will show you how to change the source of data for already existing Pivot Tables

How to change the source of data to more general one
03:14

I will show you How to hide and show the lists related to the Pivot

How to hide and show the lists related to the Pivot Tables
01:09

I will show you How to add new fields & refresh data in the Pivot Tables

How to add new fields & refresh data in the Pivot Tables
01:39

I will show you how to Sort data in Pivot Tables

Sorting data in Pivot Tables
03:26

Here I will talk about filtering options available in the Pivot Tables

Filtering – General Options
02:19

Here I will talk about how to filter when you deal with text variables

Filtering Text
02:55

Here I will talk about how to filter when you deal with number variables

Filtering Numbers
01:44

Here I will talk about how to create separate sheets automatically for every value in the filter

Report Filter Pages
03:09
+ Improving the look and feel of the table – Design Tab
6 lectures 14:23

In the 5th  section I will show you how to improve the look and feel of the Pivot Tables.

Preview 00:55

Here I will show you how to format the whole Pivot Table in Excel


Preview 01:58

In this lecture I will show you how to change the colors in the Pivot Table

Changing the colors and the layout of the Pivot Tables
01:11

Conditional Formating helps you data be more understandable to people. This is great tool for creating dashboards. I will show you how to use them

Conditional Formating used on Pivot Tabels
04:42

Now I will show you how to customize headers

How to customize headers
01:41

Layout in Pivot Tables is very important. I will show you how to change it and what consequences it has

Layout of the Pivot Table
03:56
+ PivotCharts – Details
6 lectures 15:03

In this section I will go into details when it comes to Pivot Charts. I will also show you how to create a Dashboard using Pivot Charts and slicers. 

Preview 00:58

A cousin of pivot tables is pivot chart. I will show you how to use the pivot charts and how does it relate to pivot tables

Pivot Chart – Example
02:39

I will show you how to combine Pivot Chart with slicers and how to make the slicer impact more than one Pivot Chart

Combining slicers with Pivot Charts
04:25

Now we will build using the slicers and Pivot Charts from previous lecture a Dashboard

Preview 01:41

Now we will build using the slicers and Pivot Charts from previous lecture a Dashboard

Building a Dashboard – Part 2
01:48

In this lecture I will discuss how to impact the look & feel of Pivot Charts

Look & Feel of Pivot Chart
03:32
+ Examples of analyses using Pivot Tables
10 lectures 24:03

Let's have a look at a few examples of how to use in practice Pivot Tables

Examples of analyses using Pivot Tables
00:14

Let’s see how you can use the online store-checks to analyze the market. This time around we will do a store-check for cosmetics

Online Storechecks – Cosmetics – Case Introduction
01:00

In this lecture we look at the data on cosmetics producers and their products that we will need for this case study

Online Storechecks – Cosmetics – Available Data
02:09

In this lecture we solve the case study on onlie storecheck

Online Storechecks – Cosmetics – Analysis by Brands
03:28

In this lecture we solve the case study on onlie storecheck

Online Storechecks – Cosmetics – Analysis by Applications
02:53

In this lecture we solve the case study on onlie storecheck

Online Storechecks – Cosmetics – Analysis by Size & Customer Group
03:28

Imagine that you were hired by a firm to analyze the emailing software market and to tell them whether there is a niche for them to create a new product. Use the customer reviews

Using Customer reviews – Case Introduction
01:19

Let's see at the data that we have for the case study that we will be solving

Using Customer reviews – Available Data
03:21

In this lecture we will be solving the case study introduced in the previous lectures

Using Customer reviews – Analysis by Industry & Size of Firm
03:09

In this lecture we will be solving the case study introduced in the previous lectures

Using Customer reviews – Customer Segment Size
03:02
+ Conclusions
1 lecture 01:14
Bonus Lecture: up to 90% discounts for all my courses
01:14