Decoding mechanics of Pivot Table - Rows and Values Area

Amey Vidvans
A free video tutorial from Amey Vidvans
Project Manager,Corporate Trainer,Yoga Wellness Instructor
4.4 instructor rating • 6 courses • 20,082 students

Learn more from the full course

Excel Pivot Tables -Artful Decision making in data Analysis

Decode Excel Pivot tables with real world case studies. Master the Art and Science of Pivot tables.

01:28:39 of on-demand video • Updated February 2018

  • In depth understanding of Pivot table mechanism.
  • Learn to handle any kind of the data with confidence.
  • Improve productivity by learning tips and tricks.
  • Prepare stunning Dashboards displaying key performance indicators.
  • Learn to generate trends and pattern to draw out conclusions.
  • Improved productivity and decision making.
English [Auto] Hello. In the last lecture we inserted a table object of today's lecture is to try to decode the mechanics of what they will and understand how it looks when we drag a fade into some of the ideas when we drag fails in different areas. Some magic happens. The magic works differently for different areas of the table. So we will learn the mechanics the rules area rules area of the table is a very typically start when building the outline of your report when you're dragging folding the rows area of the table all the unique values in the field will be displayed in the first column of the table removes all the duplicate in the field and only displays unique values. Let's check it open a new window for your document so that we can see hope beyond functions and simultaneously see how it reduces. I look at the actions and steps. Let's go to our windows group click on new window select the source sheet in sheet to we can switch between the sheets with alt plus tab. You know what assembly does. You can see some manufacturers are listed more than once because more than one brand of the manufacturer appeared in pop analyst venue Plez the manufacturer fitted in the Rosaria every manufacturer is listed only runs in the first column of the table because the more they will add the Martigny removes the duplicates and only displays unique values. So let's start the values area. Let's understand what happens when we drag a friend in the values area the values area displays data that we want to summarize what we were that will report. There is a sign of submission. So what I want to fill you want to summarize just drag in the values area when you fill in the values area the table will matter the sums or count of the data in that field. Let's bring in Booba lose area. Now understand this condensed text. So the P-word are dramatically gone. The brine for each manufacturer that is listed in the rules area. Let's check in SORAS datasheet. We will take one manufacturer let's say Mondelez has three bands featured in pop and list hence the coal industry. Similarly Marzouki Limited has four brands featured in the top 10 list of the best for brand strong values area and replace cells filling in the valleys area. Now this field contents numbers and hence Beware them will win at least some the cells of each manufacturer that is listed in the rules. So what exactly happens when I referred to the lose area. The word they will performs its magic by filtering and calculating the data for each cell in the values area. This is a really important concept to learn. It will help us understand. Hope your table works. Let's go into detail of it before shows 234 million dollars from $1 International. This is the sum up says for Mondelez International hold that we were able to calculate this number. We can break it out in two steps. First the reward for you. Does the source for the criteria in the fills columns and rows area in this case with only one fill in the rows area so the manufacturer column in the source data is filtered for Mondelez International. Let's check it in the source. Godless or dashiki select column us go to home editing group Glee concert unfilter now the fruiterer is a play. No we how manufacturer in the Rose area so let's a manufacturer to Mondelez International. The closer to drop non-mental in the manufacture of the column header. It will show you a message manufacturer showing all. It means no filter is played at present. No real apply the filter that is international. So click on dropdown select on and select only Mondelez International. Know how brands manufactured by Mourne International. Select all three brands says figure. And in the summer the state has bought hoodies showing 234. The process is repeated for each cell in the values area of the table. The manufacturer column is basically filtered for each Abda for the manufacturer and the sum of cells for each is calculated and placed in the values area after each filter. It is important to note that we are learning the concept how it works. The source data is not actually filtered on the sheet. When you are to fill in the values area where understanding the concept and the calculation will help us understand why the structure of the song is so important. So what we tried to do we try to understand what your table Vennochi add a fill in the rows area and lose area. When we add a friend draws area it displays only unique values and that in the first column of the table. What it means. It removes all the duplicated in the field. All of which are in the column of Warsaw's. Now when you drag a fruit into values area it will achromatic least some zircons the data in the field. Use the data the numbers. Then some will be calculated if the data and text are blanks. Then count will be calculated. So in this lecture we try to decode the mechanics of the P-word table. Then a field is added to the rows area and values area in the next lecture and we will see what happens when the field is added in column. Thank you very much.