The reason why many Excel users never learn how to do a Pivot Table in Excel is because they don’t understand why they should learn Pivot Tables neither what is a Pivot Table. If you are still afraid of Pivot Tables or you want to establish a solid foundation to your data analysis journey, in this free Excel Pivot Table tutorial, you will learn the fundamentals first:. I was one of those who was not aware of the psychology and mechanics of Excel Pivot Tables.
Why you shoud learn how to do a Pivot Table in Excel But I was determined to definitely get it because I realized Pivot Tables were a form of fast Excel dashboarding and fast data analysis and understanding. Create Excel dashboards fast You can create summarized tables out of big tables with a couple of clicks. You can add several views of the same data set (e.g. By department, by quarter, by region, sum, average, count, etc).
Pivot Tables. Pivot tables may be the single most powerful tool in Excel. They let you understand, explore, and summarize huge data sets very quickly, without any formulas at all. Pivot Tables. Pivot tables are one of Excel 's most powerful features. A pivot table allows you to extract the significance from a large, detailed data set. Our data set consists of 213 records and 6 fields. Order ID, Product, Category, Amount, Date and Country.
You can also add charts and dashboard controls such as slicers and timelines without using VBA, without writing any single vlookup or index and match formula. I created the plain dashboard you see below in a matter of 5 minutes! Analyze and understand data For example you can turn a big 10K rows Excel table into a tiny Excel table packed with meaningful information because a Pivot Table finds patterns and show them to you. For example, in the table below, the thousands of “Delivery Truck” entries become a single one with a subtotal.
When you learn how to do a Pivot Table in Excel, you become able to include in your analysis only the data that matters most to you. For example, if you are a manager, you can focus on departments or products or on both categories at the same time. You can focus your analysis even further by looking at a date range only. If you are a salesman, you can focus your analysis on the product you sell and narrow the date range to your sales period.
If you want to look at your data from another vantage point, you can do it easily because you can drag and drop any data field you want and change your Pivot Table report instantly. Well, if you don’t know how to do a Pivot Table in Excel yet, then you need to learn what is a Pivot Table first. If you want to get Pivot Tables explained to you in a meaningful manner, you might find this explanation useful: What is a Pivot Table You are about to discover a simple analogy that will make Pivot Tables easier to learn and use. If you don’t know what is a Pivot Table, if you are afraid of learning and using Pivot Tables, if you have only a basic idea of Pivot Tables in Excel and don’t know what to expect when you drop the fields to the PivotTable report, or if you just want to understand more about Pivot Tables, then keep reading because this grocery analogy I stumbled upon will show you how to do a Pivot Table in Excel because it will make PTs simpler for you, and it will boost your reporting and dashboard skills, starting today. It’s a shame I used to create Pivot Tables reports through trial and error but I was afraid my coworkers or boss would ask to me: how did you that? Because I didn’t know exactly how my Pivot Table reports were generated, how the fields affected the layout of the report, and so forth. I never had confidence in using Pivot Tables because it was a sort of hope-reporting.
Every column, row, page, or value field I dropped into the fields’ area, produced unexpected results. Everything changed when I discovered the Pivot Table relationships. Pivot Tables Paradigm!
A Pivot Table report is like a box. See below: Let’s imagine the box contains oranges, apples, lemons and bananas. This is your source data. With this bunch of fruits as your source data, you can ask questions like: how many oranges, apples, lemons and bananas are in the box?
You can achieve this by counting each type of fruit. To better find the answer, you organize the box by fruit type as you see below: Here’s the answer and a nice subtotal: You can also organize the fruits according to its type: citric and other. If each fruit have a price tag, then you will be able to answer questions like: how much the oranges, apples and bananas are?, how much the box is?, how much the citrics are? To answer the question: how many green and mature oranges contain the box?, you would need to compartmentalize the box by fruit state: mature and green oranges. Very simple, right?
To answer the question: how many big or small green/mature oranges are in the box?, you would need to compartmentalize the box by fruit size. Would you compartmentalize the entire box again? Or just the existing compartments? You would separate green oranges into small and big and mature oranges into small and big.
In other words, you compartmentalize the already compartmentalized box. This simple principle applies to Excel Pivot Tables Learn how to do a Pivot Table in Excel You simply group the data source on your Pivot Table report by any of the fields you choose.
The first item you add to your Pivot Table fields area (box) is what’s in your box (data source), the second field is how you compartmentalize the existing box, the third field is how you compartmentalize the existing already compartmentalized box, and so on. For example, if you have a table with these fields: Department, Brand, Model, Salesman, Date, Units sold, Amount, and you add the “Department” field to the fields area, then you will answer questions like how much sales by department. To answer the question how well each brand performed by department?, then you would add “Brand” to the fields’ area so you compartmentalize “Department”. If you add Brand, then you compartmentalize department by brand, if you add salesman, you split the brand compartment by brand, and so on. You now should be able to drag and drop the Pivot Table report fields with confidence. If you are more comfortable with using Pivot Tables in Excel, and want to get deeper into using Pivot Tables, you can definitely learn how to do a Pivot Table in Excel in my Or you can explore these good Pivot Table tutorials.
'I’ve purchased some of your publications in the past (“Master-of-Macros-book”, “Pivot-Tables-VBA-Secrets”,”How-to-write-perfect-vlookup-im-formulas”, etc), and I am quite impressed with how you go about making difficult concepts very clear.' - Dave U, Canada 'John brings to light useful functions/methods/approaches otherwise unknown to a significant portion of Excel users.' - Anonymous subscriber 'I have been “following” you for several years, and you produce lots of really good stuff for us Excel addicts' Geir S 'Dear JohnReally you are fascinating and have an explanation method that is highly attractive. Your approach impressed me because it hits the point directly' Emad T 'Hi John, I just bought your dashboards book. It looks very good and a new innovative way to view things!!
I love the way you are breaking things down and getting my brain working and improving my thought process.' 'Very good beginning to understanding the logical flow of programming. This lesson has helped me greatly in understanding what I call the GrandParent.Parent.Child objects. How to take action on each one of them and in what order.'
- Mary Grimes 'I do keep reading and enjoying your emails about Excel. They have proved helpful at work, I must admit. Good luck and thanks again.
Shreepad G.