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.
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.
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.
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.
Learn the most exciting and miraculous way to repair or split data. This revolutionary feature called Flash Fill was introduced in Excel 2013.
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
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.
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!
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.
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...)
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.
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.
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.
Let us find out if you learnt well in this lecture.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Let me find out how much you have understood about Show Values As options
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!
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.
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.
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.
Copy pasting pivot tables helps you create a dashboard showing the data from multiple points of view. Adding slicers creates interactivity without any programming.
Learn about Pivot Charts, hiding the extra buttons, interactivity, templates and pasting into PowerPoint.
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.
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.
By default, Grand and Sub-Totals are ON. Learn about the nuances of these totals. Learn how to remove all totals quickly.
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.
This tab provides many options which are globally available. Here we can customize for each field - hence the name "Field" settings.
This tab controls overall layout related options. It also allows you to preserve column widths and formatting. Learn the practical usage of these settings.
Learn how to control global settings for Totals and Filters. Also understand the importance of Custom Lists.
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.
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.
Learn how to auto-refresh data while opening the file. Also learn how to solve the "Ghost" pivot table entries problem.
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.
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.