Excel Pivot Tables For Management Consultants Analysts
Tags: Pivot Tables
A practical guide on how to do data and business analyses using Pivot Tables during consulting projects
Last updated 2022-01-10 | 4.4
- 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
What you'll learn
* 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 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
8 sections • 65 lectures
Introduction Preview 02:11
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.
About Me Preview 01:47
A few words about your humble teacher
How the course is organized Preview 01:51
Here I will show you how the course is organized
How to make the most of the course Preview 02:15
Here I give you some tips how you can get the best out of the course
What to do if a blurry image appears Preview 02:04
Here I will show you what to do if a blurry image appears
How to find additional resources Preview 01:05
Here I will show you how to find additional resources attached to the coruse like Excel files, presentations, links etc.
Basic Usage of Pivot Tables – introduction Preview 01:49
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
Pivot tables - introduction Preview 01:24
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
How to use pivot tables? Preview 05:28
We start with basic usage of pivot tables
How to use pivot charts? Preview 02:39
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
How to combine pivot tables with regular calculations? Preview 02:17
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 use slicers? Preview 02:07
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
How to replace pivot tables with SUMIFS and COUNTIFS Preview 03:17
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
Going from general to specific with pivots Preview 01:57
Pivot tables enables you to go from general to specific. I will show you how to do it
Example of analyses using pivot tables- Analysis of sales channels for FMCG Preview 03:06
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
Conditional Formating used on Pivot Tabels Preview 04:42
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
Calculation in the Pivot Tables – Introduction Preview 01:23
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.
How to create a calculation field – Average Item Price Preview 03:19
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
How to create a calculation field – Average Weighted Sales Density – Part 1 Preview 02:32
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
How to create a calculation field – Average Weighted Sales Density – Part 2 Preview 02:44
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
Modifying Calculated fields – Recalculate to USD Preview 02:24
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
How to create averages for calculated fields – Average Front Margin Preview 04:29
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
Switching the basic calculation to different options Preview 02:31
You can switch the default option in Pivot Tables from sum to count, average etc. I will show you how to do it
Show values as Option – Introduction Preview 01:52
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 % of Total / Row / Column Preview 02:51
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 Parent Preview 01:49
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 a Difference From Preview 03:33
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 Running Total Preview 02:48
In this lecture I will show you how to show the values in the Pivot table as a running total
Show values as a Rank Preview 03:03
In this lecture I will show you how to show the values after ranking them
Other useful pre-defined options – Introduction Preview 01:06
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.
Copying Pivots Preview 01:59
In this lecture I will show you how to create a copy of already existing Pivot Table
Moving Pivots to new sheets Preview 01:28
In this lecture I will show you how to move the created Pivot into new sheet
How to change the source of data to more general one Preview 03:09
In this lecture I will show you how to change the source of data for already existing Pivot Tables
How to hide and show the lists related to the Pivot Tables Preview 01:08
I will show you How to hide and show the lists related to the Pivot
How to add new fields & refresh data in the Pivot Tables Preview 01:37
I will show you How to add new fields & refresh data in the Pivot Tables
Sorting data in Pivot Tables Preview 03:21
I will show you how to Sort data in Pivot Tables
Filtering – General Options Preview 02:07
Here I will talk about filtering options available in the Pivot Tables
Filtering Text Preview 02:54
Here I will talk about how to filter when you deal with text variables
Filtering Numbers Preview 01:42
Here I will talk about how to filter when you deal with number variables
Report Filter Pages Preview 03:07
Here I will talk about how to create separate sheets automatically for every value in the filter
Improving the look and feel of the table – Introduction Preview 00:54
In the 5th section I will show you how to improve the look and feel of the Pivot Tables.
Formatting the numbers in the Pivot Tables Preview 01:57
Here I will show you how to format the whole Pivot Table in Excel
Changing the colors and the layout of the Pivot Tables Preview 01:09
In this lecture I will show you how to change the colors in the Pivot Table
Conditional Formating used on Pivot Tabels Preview 04:42
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
How to customize headers Preview 01:40
Now I will show you how to customize headers
Layout of the Pivot Table Preview 03:49
Layout in Pivot Tables is very important. I will show you how to change it and what consequences it has
PivotCharts – Introduction Preview 00:58
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.
Pivot Chart – Example Preview 02:39
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
Combining slicers with Pivot Charts Preview 04:09
I will show you how to combine Pivot Chart with slicers and how to make the slicer impact more than one Pivot Chart.
Building a Dashboard – Part 1 Preview 01:40
Now we will build using the slicers and Pivot Charts from the previous lecture Dashboard.
Building a Dashboard – Part 2 Preview 01:46
Now we will build using the slicers and Pivot Charts from the previous lecture Dashboard.
Look & Feel of Pivot Chart Preview 03:26
In this lecture, I will discuss how to impact the look & feel of Pivot Charts.
Examples of analyses using Pivot Tables Preview 00:14
Let's have a look at a few examples of how to use in practice Pivot Tables
Online Storechecks – Cosmetics – Case Introduction Preview 01:00
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 – Available Data Preview 02:09
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 – Analysis by Brands Preview 03:28
In this lecture we solve the case study on onlie storecheck
Online Storechecks – Cosmetics – Analysis by Applications Preview 02:53
In this lecture we solve the case study on onlie storecheck
Online Storechecks – Cosmetics – Analysis by Size & Customer Group Preview 03:28
In this lecture we solve the case study on onlie storecheck
Online Storechecks – Yogurts – Case Introduction Preview 00:55
Data for a another analysis of store checks
Online Storechecks – Yogurts – Case Solution Preview 00:17
Let's look at the solution to the case study
Using Customer reviews – Case Introduction Preview 01:19
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 – Available Data Preview 03:21
Let's see at the data that we have for the case study that we will be solving
Using Customer reviews – Analysis by Industry & Size of Firm Preview 03:09
In this lecture we will be solving the case study introduced in the previous lectures
Using Customer reviews – Customer Segment Size Preview 03:02
In this lecture, we will be solving the case study introduced in the previous lectures.
Bonus Lecture Preview 01:22
This course includes:
- 2.5 hours on-demand video
- Access on mobile and TV
- Certificate of completion
Related Courses
Recent Posts
![4 Dentistry courses [2021] learn online for free](https://www.courses-for-you.com/images/uploads/thumbs/11-3.png "4 Dentistry courses [2021] learn online for free")
4 Dentistry courses [2021] learn online for free
Dental medicine is so vast that we could not incorporate it into MBBS. These 32 teeth and associated structures make a whole other discipline known ...Read more
Learn Python like a professional (online courses and tutorial)
Python is a high-level programming language. It enables the coders to write logical and oriented codes which we can easily interpret. Learning Python can be ...Read more
3 Three-Course Menu Examples that Steal The Show of New Year Eve
New Year’s Eve is on its way. It is the ideal time for families and friends to gather and enjoy the last meal of the ...Read more
7 Google Hacks to Improve Your Search Rank for Free
Every second, almost 63,000 searches are conducted on Google – Yes, it is a lot. Sixty-three thousand searches per second make it nearly 5.4 billion ...Read more
Learn about a career in counseling
Mental health counselors guide people who are dealing with issues that affect mental health and well-being. Many counselors use a “wellness” model that emphasizes and ...Read more
