Excel Pivot Tables - the Second Honeymoon
4.9 (9 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.
47 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Excel Pivot Tables - the Second Honeymoon to your Wishlist.

Add to Wishlist

Excel Pivot Tables - the Second Honeymoon

In-depth, crisp knowledge upgrade for those who use Pivot Tables already. Great start for those who have never used it.
4.9 (9 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.
47 students enrolled
Last updated 6/2017
Curiosity Sale
Current price: $10 Original price: $30 Discount: 67% off
30-Day Money-Back Guarantee
  • 5.5 hours on-demand video
  • 47 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Understand what is data analysis and why should we use it (in very simple, jargon-free manner)
  • How to identify bad data from good data (Easy to analyze data = Good data)
  • How to convert bad data to good data
  • Understand the real meaning of analytics and how analytics can help you grow in your business or career
  • Look at existing data as an opportunity to know more
  • Deliver existing reports / MIS much faster
  • Deliver much more than what is expected in terms of reports creation and submission
  • Unearth new and useful information from the same familiar data
  • Interpret data in a more comprehensive manner
  • Understand how to simplify interpretation using data Visualization
  • Understand how to handle large amount of data in Pivot Tables
  • Learn a new method of generating dashboards
  • Learn a revolutionary new way of sharing reports with others (including very large files)
  • Learn how to improve performance of Pivot Tables
  • Add value beyond your job description
  • Grow faster in their career
View Curriculum
  • Excel version 2007 or above.
  • If you have used Pivot Tables before, this course will expose you to undiscovered gems and surprise you at every turn. What people do not realize is using pivot table everyday is not the same as knowing Pivot Table!
  • If you have never used Pivot tables before, you will discover and master a very powerful tool which will help you in personal as well as professional context.

Why is this course called the "second honeymoon"? 

Simple. Many of us have been using Pivots for decades. We think we already know Pivot Table. But trust me, there is vast amount of unexplored stuff - waiting to be exploited. This is course is for those who have used Pivots and attended other Pivot Courses. 

This is not a click here, go there type of course. It provokes you to think, learn and apply the knowledge to your own work or business. 

If you do not know Pivot tables, don't worry. This course starts from scratch and helps you become a master Pivot analyst. Those who already know Pivot Tables are in for a surprise. For example, do you know that the raw data must be an Excel table before you create a Pivot Table? Most probably, you will ask me a question: "What is an Excel table?"

Try out the free preview videos and you will notice the difference instantly. 

Who is the target audience?
  • If you have already worked on Pivot tables, you will gain most. You are revisiting Pivot Tables in this course. You will soon realize how much of precious time you have wasted in misusing Pivot Tables in the past. In addition, you will learn more capabilities which you never thought were possible using Pivot Table.
  • If you have never used pivot tables, thank your stars. You are in the right place! You are free from the bias of bad habits others have learnt. Start from scratch and become super-proficient in Excel based data analysis.
  • This course is not about just features and "advanced" capabilities. It is about building the right mindset to learn from the past in order to improve the future. Everyone who wants to build a better future can benefit from this course.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
44 Lectures
A fresh, new, introduction!
1 Lecture 04:01

Learn the concepts of data analysis in the context of Excel. Understand what you are missing, even if you are generating Pivot table based reports. Learn the final objective - how to grow in your job / career using Data Analysis as a concept and Pivot Table as a tool.

Preview 04:01
Good or Bad Data - Quick Primer
6 Lectures 34:40

Analysis requires data. If data is not structured properly, analysis becomes unnecessarily complex. We end up wasting too much time cleaning up the data before we can do any analysis. That is why, you must be able to look at the raw (input) data and judge whether it is easy to analyze (called Good Data) or it requires manual effort to clean it up (called Bad Data). This is a checklist of 11 items which you can use to classify data as good or bad.

Preview 07:30

If the input data is Bad - someone has to clean it up. But do not try to do it yourself first. Ask for or demand good data. If you get it, you save the clean-up time. If you cannot get good data, then you must manually clean it up. However, we do not want to waste too much time in cleaning up. This section shows smarter, faster ways of data-clean up.

Data Clean up

This is a common type of bad data. Multiple ways of filling up gaps in the data are shown. The most powerful way is to use a new tool called Power Query.

Filling up Empty Cells

Learn the most exciting and miraculous way to repair or split data. This revolutionary feature called Flash Fill was introduced in Excel 2013. 

Data Clean up using Flash Fill

One of the commonest form of bad data is a Cross-Tab. Excel has a new, powerful tool for data cleanup called Power Query. If you have Excel 2016, you already have it. If you have Excel 2010 or 2013, you must download and install Power Query from here https://www.microsoft.com/en-us/download/confirmation.aspx?id=39379

Converting Cross-tab to Tabular Format

One of the commonest form of bad data is a Cross-Tab. Here rows as well as columns have headers. It is like the output of a Pivot table. Unfortunately, very commonly, we get this as an input. This video explains a powerful feature of Excel called Multiple Consolidation ranges based Pivot Table - which has been around for at least 2 decades. Hours (or days) of laborious manual work can be done in minutes using this feature.

Preview 09:16
Instant Analysis without using manually typing formulas = Pivot Table
6 Lectures 01:06:49

Now we begin to understand the mechanics of how a pivot table works. Even if you are already using Pivot Tables, please go through these videos. Many people who use Pivot Tables have a false sense of knowledge. You will realize what I mean when you view this video (and all other videos in this course). Using and KNOWING are two different things!

Create a Simple Pivot Table

Pivot Table is used for data analysis. However, before we analyze the data, we must be sure that the data is clean, correct and consistent. Data can have thousands or rows and many columns. It is not possible to check the data quality by just scrolling it. This video shows you a very smart and fast way of using Pivot Table to audit (check) the quality of data before analyzing it.

Using Pivot Tables to Audit Data Quality

Pivot layout is an often ignored functionality. However, everyone suffers due to this ignorance. Ever got irritated because the field you dragged into row area is now called "Row Labels" instead of the name of the actual column? You will find the answer to this problem here (and much more...)

Preview 09:07

We begin our journey into data visualization here. Sometimes, it is easier to view a chart than looking at numbers. Learn when to use Pivot Charts (and when not to use them). Also learn about Chart Templates - a gem of a feature which is largely unknown to the world.

Create Pivot Charts to understand the data better

How to use a subset of available data? The answer is "using Filters". But there is more to it than just putting items in the filter area. In fact, the traditional filters are no longer required - there is a new, cool way of filtering called Slicers. Using Slicers you can filter multiple Pivot Tables and Charts in one go. Very powerful and useful for creating interactive dashboards without programming. By the way, Slicers work on raw data (tabular data) as well.

Focused analysis using filtering

Simple but important topic. Reordering items in Pivot Tables is required quite often. Pivot table has many ways of selecting specific subset of data. Sometimes, you need to select the entire Pivot table, but it just does not work the way you expected it to. Find all the answers here.

Select, reorder, rename fields and items

Let us find out if you learnt well in this lecture.

1 question
Categorizing and Grouping Data
4 Lectures 29:06

Input data usually has lot of details. Often, we want to view the data by reducing the level of details. This is done using Grouping features of Pivot Table. For example, products can be classified (grouped) into categories. Learn the nuances of text based grouping here.

Categorizing text items using Grouping

Bin or bucket means a range. For example, if you have a column containing age of your customer, you want to group it like 10 to 20, 21 to 30 and so on. Most of us struggle with nested IF functions in this case. Pivot lets you do it in few clicks! It also covers what to do if you want dissimilar bins. For example, 10 to 20, 21 to 50, 51 to 150 and so on. 

Preview 05:07

Dates need to be grouped at least by month and year routinely. Again Pivot Table grouping eliminates creation of additional columns and struggling with date / time functions. What's more, you can also group by hours and seconds. Want weekly grouping? No problem. Want to get rid of the time stamp and group by days? No problem. It's all here.

Grouping by Weeks, Months, Quarters and Years

Pivot based date grouping assumes that the financial year starts in the month of January. What if your fiscal year starts in April or July or October? Pivot Table groups quarters which start from January. Which is not acceptable. This lecture explains the solution. It uses VLOOKUP to create custom fiscal years and quarters. 

Handling fiscal years which do not start in January
Amazing ways of viewing the same data from different points of view
6 Lectures 35:53

Pivot table summarizes numeric data in the context of some text (or date) fields. In short, it takes some rows which satisfy a particular criterion and combines the numeric value. For example, it will combine the sales quantity for a specific product. By default the combination is done using SUM. However, there are other options available - which are rarely used (other than COUNT). This lecture covers all available options and guides you about when to use which summary option.

Choosing the right summary - Sum, Count, Avg ...

By default we see values as sum for each item and a grand total. Most people stop analyzing data at this point. But that is not the end point. In fact, it is just the beginning. The same data can be seen in so many different ways. Each way will help you find some additional correlation or pattern or insights about your data (and your work or business). We start with Precentage of Total in this lecture. 

View Percentage of Total

This lecture shows you how to compare summarized values to each other. This is useful in taking one item (e.g. product) as a benchmark and comparing all other items (products) to it. This provides very useful insights which can lead to dramatic improvement of business. Learn how to compare using actual difference or percentage difference. 

Preview 08:19

This is a more sophisticated (but very simple) way of comparing hierarchical data. It is an extension of % of Total but can be extremely useful in finding hidden patterns in the data. 

Percentage of Parent total in hierarchical data

For chronological data we often need running totals. This lecture shows you how to create Running Totals without adding a formula outside the Pivot table manually. It also covers Ranking... Top 5 best selling products, for example.

Instant Running totals and Ranking

Index is a very special method of comparing data items to each other. Although it is rarely used, it can be a very important method which helps in precise decision making. You must view this video to understand the concept and its practical use.

The importance of the Index option

Let me find out how much you have understood about Show Values As options

Value Quiz
1 question
Why you rarely need to calculate Outside a Pivot Table
4 Lectures 25:41

If you add calculations outside the Pivot Table, most probably, you do not know about Calculated Fields / Items. Here is your chance to learn this powerful feature. It is painless, smart and sophisticated!

Calculations within Pivot Tables

Pivot Table gives you four areas to work with. Row, Column, Data (or Values) and Filter. Learning which field to put where is very important to create the desired output quickly. 

Which field to put in which area?

This feature is either unknown or if known, it is universally disliked. Learn how this irritating feature is your best friend in reality. You will be amazed at the amount of risk you were taking by not using this feature. I have also created a special macro to simplify usage of GetPivotData.

Preview 11:26

Double Click has a special meaning for Pivot Tables. Double click behaves differently in different parts of Pivot Table. Learn the nuances of double click and how it can help you drill down to find the root cause or lineage of data.

The Power of Double Click! Drilldown, Lineage and more...

Month on Month growth
1 question
Creating visually compelling dashboards
5 Lectures 54:39

Copy pasting pivot tables helps you create a dashboard showing the data from multiple points of view. Adding slicers creates interactivity without any programming. 

Preview 07:34

Learn about Pivot Charts, hiding the extra buttons, interactivity, templates and pasting into PowerPoint.

Creating smart Pivot charts

Learn various ways of understanding the data visually rather than reading each number and comparing it in your head. Excel provides many amazing methods. Learn about them and think about where you can apply them in your daily work. 

Data Visualization Primer

Conditional formatting is one method of data visualization. It offers many different ways of interpreting data using formatting, icons and so on. Learn the nuances of which method to use when and how. Also learn about how it integrates with Pivot Tables. 

Using Conditional Formatting with Pivot Tables

By default, Grand and Sub-Totals are ON. Learn about the nuances of these totals. Learn how to remove all totals quickly.

Grand totals and SubTotals
Customize reports using Field settings
2 Lectures 20:20

Field setting are totally ignored by most users. However, these have a large collection of useful settings which will help you create more refined reports instantly. We cover the subtotals and filters tab here.

Subtotals and Filters Tab

This tab provides many options which are globally available. Here we can customize for each field - hence the name "Field" settings. 

Layout and Print Tab
The power of Pivot Table Options
5 Lectures 28:33

This tab controls overall layout related options. It also allows you to preserve column widths and formatting. Learn the practical usage of these settings. 

Layout and Formatting tab

Learn how to control global settings for Totals and Filters. Also understand the importance of Custom Lists.

Totals and Filters tab

Learn about customizing the look and feel of Pivot Table. Still like to drag drop directly on the pivot table? No problem. Choose Classic View from here. 

Display tab

This tab provides brilliant options to print long and broad pivot tables. For example, you can repeat row as well as column labels for additional pages in just two clicks.

Printing and Alt Text tab

Learn how to auto-refresh data while opening the file. Also learn how to solve the "Ghost" pivot table entries problem. 

Data tab
Pitfalls, shortcomings and disadvantages
2 Lectures 10:57

When data size grows, files become larger and performance goes on a nosedive. Learn the solution to all these problems - Power Pivot. This is so important and revolutionary that I have a separate course for this topic on Udemy. 

Pivot Tables: Problems and Solutions

Pivot Tables keep a copy of the data leading to increase in file size. This fact is not known to most people. Learn the nuances of how pivot tables affect file size. Also learn why such a copy is kept and how to control it.

Preview 07:00
3 More Sections
About the Instructor
Dr. Nitin Paranjape
4.5 Average rating
144 Reviews
769 Students
2 Courses
Office System MVP, Microsoft Regional Director

Microsoft’s Most Valuable Professionals for Office 

Microsoft Regional Director (honorary title)

Written 2 books and 1000+ blog articles 

Coached 273,000+ professionals in 2100+ organizations across 18 countries

Covers the entire Office 365 platform including Excel, Power BI, Exchange, Skype for business, OneDrive, SharePoint, Yammer.

My sessions are often referred to as "Magic Show" or "Eye Openers"

CEOs, CFOs and other senior leaders love my sessions and the only question they ask is "Why did I know all this earlier". They can see the loss of efficiency potential in their past lives after my session. 

Unfortunately, there is no UNDO button for past life. However, after attending my sessions, you are yourself the magician. Office is a catalyst to your growth. 

I am fully aware that nobody wants Office as their revenue mechanism. However, you must realize that irrespective of your area of specialization, most probably, you are using Office for 2 to 8 hours every day. If you do not know Office and especially Excel, your talent and expertise will not be able to express itself fully. It may even hinder your growth. 

There are thousands of experts. Most of them specialize in a particular area. In my case, I have taken a lot of effort (12 years of my life) to ensure that I understand all tools from the Office 365 platform. That knowledge coupled with hundreds of thousands of actual interactions with users gives me a unique insight into the minds of users and what technology has to offer. All this is not my achievement. It is a great platform made available to the world by Microsoft. 

Unfortunately, I don't know of a single person who has attempted to understand and utilize this powerful platform to her advantage. 

It is my passion to share what I know with the world - in a simplistic yet powerful way so that you can grow in your career and business beyond your imagination.