Excel Pivot Tables For Management Consultants & Analysts

A practical guide on how to do data and business analyses using Pivot Tables during consulting projects

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 fanciest or sophisticated one, yet it is universally understood by everybody especially your boss and your customers.     Excel is still a pretty advanced solution with a countless number of features and functions. One of the most useful Excel tools is the Pivot Tables that help you do fast and efficient 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 the 80/20 rule and I want to teach you the most useful (from a 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 a pretty good business analyst that knows how to use Pivot Tables Excel in a smart way. It is based on my 15 years of experience as a consultant in top consulting companies and as a Board Member responsible for strategy, improvement, and turn-arounds in the biggest companies from FMCG, SMG, B2B sectors 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 110 000 students including people working in EY, McKinsey, Walmart, Booz Allen Hamilton, Adidas, Naspers, Alvarez & Marsal, PwC, Dell, Walgreens, Orange, and many others    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 formulas 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). In the end, I will show full-blown analyses using Pivot Tables that use the tricks that I show in the lectures.    For 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 modifications, be applied by you in your work.        Why have I decided to create this course?   I have done a 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 a 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 into the following sections:   Introduction. We begin with a little intro to the course     Basic Usage of Pivot Tables. In this section, I will show you the 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 predefined options such as sorting, filtering, changing source data, adding new variables, etc. We will discuss them in this section and will show you some examples of how to use them in practice.    Improving the look and feel of the table. In this 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 courseLinks to additional presentations and moviesLinks 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 TablesUnderstand the main challenges in analyzing data with Pivot TablesDo calculation directly in the Pivot Table without impacting the original data setPerform the analyses in a very effective mannerBuild Dashboards using Pivot Tables and ChartsWho should take this course? Who should not?Business analystsConsultantsStudents that want to work in Management ConsultantsResearchersStudents that want to work in Private EquityControllersSmall and medium business ownersStartups founders

Overview

Section 1: Introduction

Lecture 1 Introduction

Lecture 2 About Me

Lecture 3 How the course is organized

Lecture 4 How to make the most of the course

Lecture 5 What to do if a blurry image appears

Lecture 6 How to find additional resources

Section 2: Basic Usage of Pivot Tables

Lecture 7 Basic Usage of Pivot Tables – introduction

Lecture 8 Pivot tables - introduction

Lecture 9 How to use pivot tables?

Lecture 10 How to use pivot charts?

Lecture 11 How to combine pivot tables with regular calculations?

Lecture 12 How to use slicers?

Lecture 13 How to replace pivot tables with SUMIFS and COUNTIFS

Lecture 14 Going from general to specific with pivots

Lecture 15 Example of analyses using pivot tables- Analysis of sales channels for FMCG

Lecture 16 Conditional Formating used on Pivot Tabels

Section 3: Calculation in the Pivot Tables

Lecture 17 Calculation in the Pivot Tables – Introduction

Lecture 18 How to create a calculation field – Average Item Price

Lecture 19 How to create a calculation field – Average Weighted Sales Density – Part 1

Lecture 20 How to create a calculation field – Average Weighted Sales Density – Part 2

Lecture 21 Modifying Calculated fields – Recalculate to USD

Lecture 22 How to create averages for calculated fields – Average Front Margin

Lecture 23 Switching the basic calculation to different options

Lecture 24 Show values as Option – Introduction

Lecture 25 Show values as % of Total / Row / Column

Lecture 26 Show values as % of Parent

Lecture 27 Show values as a Difference From

Lecture 28 Show values as Running Total

Lecture 29 Show values as a Rank

Section 4: Other useful pre-defined options

Lecture 30 Other useful pre-defined options – Introduction

Lecture 31 Copying Pivots

Lecture 32 Moving Pivots to new sheets

Lecture 33 How to change the source of data to more general one

Lecture 34 How to hide and show the lists related to the Pivot Tables

Lecture 35 How to add new fields & refresh data in the Pivot Tables

Lecture 36 Sorting data in Pivot Tables

Lecture 37 Filtering – General Options

Lecture 38 Filtering Text

Lecture 39 Filtering Numbers

Lecture 40 Report Filter Pages

Section 5: Improving the look and feel of the table – Design Tab

Lecture 41 Improving the look and feel of the table – Introduction

Lecture 42 Formatting the numbers in the Pivot Tables

Lecture 43 Changing the colors and the layout of the Pivot Tables

Lecture 44 Conditional Formating used on Pivot Tabels

Lecture 45 How to customize headers

Lecture 46 Layout of the Pivot Table

Section 6: PivotCharts – Details

Lecture 47 PivotCharts – Introduction

Lecture 48 Pivot Chart – Example

Lecture 49 Combining slicers with Pivot Charts

Lecture 50 Building a Dashboard – Part 1

Lecture 51 Building a Dashboard – Part 2

Lecture 52 Look & Feel of Pivot Chart

Section 7: Examples of analyses using Pivot Tables

Lecture 53 Examples of analyses using Pivot Tables

Lecture 54 Online Storechecks – Cosmetics – Case Introduction

Lecture 55 Online Storechecks – Cosmetics – Available Data

Lecture 56 Online Storechecks – Cosmetics – Analysis by Brands

Lecture 57 Online Storechecks – Cosmetics – Analysis by Applications

Lecture 58 Online Storechecks – Cosmetics – Analysis by Size & Customer Group

Lecture 59 Online Storechecks – Yogurts – Case Introduction

Lecture 60 Online Storechecks – Yogurts – Case Solution

Lecture 61 Using Customer reviews – Case Introduction

Lecture 62 Using Customer reviews – Available Data

Lecture 63 Using Customer reviews – Analysis by Industry & Size of Firm

Lecture 64 Using Customer reviews – Customer Segment Size

Section 8: Conclusions

Lecture 65 Bonus Lecture

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