- \n
- There should be no blank row or column.<\/span><\/li>\n
- \u00a0<\/span>Make sure there is no unnecessary cell filled up in the dataset itself.<\/span><\/li>\n<\/ul>\n
For example<\/strong> Total at the end of a column in the dataset. <\/span><\/p>\n
How to create a pivot table in Excel?<\/h3>\n
1:<\/b> To form a pivot table, you should have a dataset.<\/span><\/p>\n
2:<\/b> After fetching the dataset, select the data range to which the pivot table action should be applied.<\/span><\/p>\n
3:<\/b> Click on the<\/span> INSERT <\/b>tab and choose the <\/span>PIVOT TABLE<\/b> label which is on the left side. Then, click on the <\/span>\u201cOK<\/b>\u201d button in the box once you have chosen where your pivot table should appear.<\/span><\/p>\n
Note:<\/b> It\u2019s better to have your pivot table in the new worksheet rather than in the existing worksheet.<\/span><\/p>\n
![\"MS](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/05\/pivot-table.png\")
<\/a><\/p>\n4: <\/b>If you have clicked on \u201cNew worksheet\u201d, a blank spreadsheet will be automated with the fields besides.<\/span><\/p>\n
![\"pivot](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/05\/pivot-table-page.png\")
<\/a><\/p>\n5:<\/b> You can choose the fields of your data from the pivot table fields. You can drag the fields and drop it in the areas at filters, columns, rows, values as according to convenience.<\/span><\/p>\n
Note: <\/b>If you want to change the<\/span> way the field appears, then you can click on the dropdown near the settings icon and choose any one among the options provided below.<\/span><\/p>\n
![\"Pivot](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/settings-dropdown.png\")
<\/a><\/p>\nExample<\/b>: For the sample dataset, we are going to count the number of people in the same age classified by their gender.<\/span><\/p>\n
![\"Excel](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/05\/num-of-ppl-in-same-age.png\")
<\/a><\/p>\nInterpretation:<\/h3>\n
From the dataset, we can observe that there are totally 3 females and 2 males which resulted as the sum of 5 beside the age 18.<\/p>\n
These were the steps to form a simple pivot table.<\/p>\n
Change in Summary Calculation<\/h3>\n
Now, we will see how to change the summary calculations at our convenience. In order to do that, follow the steps:<\/p>\n
1: Right-click on the pivot table.<\/p>\n
2: Choose the \u201cvalue field settings\u201d option.<\/p>\n
![\"value](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/05\/pasted-image-02.png\")
<\/a><\/p>\n3: Choose the function you want to perform.<\/p>\n
For our sample, we are going to sum the estimated salary according to their gender.<\/p>\n
![\"Sum](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/sum-of-salary-function.png\")
<\/a><\/p>\nOnce you click on the sum, the calculation takes place and it’s viewed as one of the columns in the table.<\/p>\n
Customizing the fields to calculate<\/h3>\n
In the pivot table, we can add columns for calculation purposes. In order to do that, follow the steps:<\/p>\n
1: Click on the Analyze tab from the menu.<\/p>\n
2: Choose Fields, Items & sets option from the ribbon.<\/p>\n
3: Click on the \u201ccalculated field..\u201doption
![\"calculate](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/calculate-fields.png\")
<\/a><\/p>\n4: A box appears asking about the heading and the formula type.<\/p>\n
Note<\/strong>: In the formula type, you will enter the required calculation you wanted to perform in the table.<\/p>\n
5: To fill the formula, double click on the required fields and then it is automatically viewed in the formula label option.<\/p>\n
![\"Custom](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/custom-cal.png\")
<\/a><\/p>\n6: Once you press ok, the column will be visible in the pivot table.<\/p>\n
![\"Excel](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/sample-of-custom-calc.png\")
<\/a><\/p>\nFrom the sample, we can observe that the custom calculation that we entered is now visible as one of the columns in the pivot table.<\/p>\n
There is also an <\/span>advanced method of forming the pivot table<\/b>. It is available in new versions of excel. It is also simple and quick to implement.<\/span><\/p>\n
1: <\/b>Select the data to which recommended pivot tables has to be applied.<\/span><\/p>\n
2:<\/b> Click on the <\/span>INSERT<\/b> tab and choose the recommended pivot tables.<\/span><\/p>\n
![\"Pivot](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/05\/recommended-pivot-table.png\")
<\/a><\/p>\nAnd then the recommended pivot tables show the number of tables the dataset could form for better interpretation of the data.<\/span><\/p>\n
3:<\/b> Choose the recommended table if it’s required and click on the <\/span>OK<\/b> button. The pivot table will be generated in the new worksheet.<\/span><\/p>\n
Note:<\/b> If you wanted to form your customised pivot table, then click on the <\/span>blank pivot table <\/b>button.<\/span><\/p>\n
<\/a><\/p>\nOnce it is being clicked, a blank pivot table appears and then you can proceed with the above steps of the simple pivot table.<\/span><\/p>\n
Now you may be wondering how those fields are being formed?<\/h3>\n
When you\u2019re doubtful with the statistics it has performed then, just double click on the cell and then the new worksheet will be formed explaining how it has accumulated the sum or how the analysis has been done.<\/p>\n
A demo has been demonstrated below:<\/span><\/p>\n
![\"Demo](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/demo.png\")
<\/a><\/p>\nExample:<\/b> I wanted to check how the table has decided its 5. So, I just double-clicked on 5.<\/span><\/p>\n
![\"Pivot](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/5-sample.png\")
<\/a><\/p>\nHence, this is how a pivot table is being formed.<\/span><\/p>\n
Advanced features of pivot table in Excel<\/h3>\n
![\"advanced](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/advanced-pivot-table-features.png\")
<\/a><\/p>\nThe \u2018-\u2019 symbol near the number shows that it has expanded its classification, whereas when we click on it, it changes to \u2018+\u2019 symbol with the details hiding.<\/p>\n
![\"+](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/symbol.png\")
<\/a><\/p>\nFor example: Look at the changes in cell A4 after clicking on the \u2018\u2013\u2019 symbol. It has been converted to the \u2018+\u2019 symbol by hiding the counts of male and female individually.<\/p>\n
We can also change the table headers as per the requirement. To do this, click on the cell and change it in the formula bar. Hope, you have seen that \u201crow labels\u201d have been changed to \u201cAge\u201d.<\/p>\n
![\"Pivot](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/age.png\")
<\/a><\/p>\nThe other super special feature is slicer<\/span> in the pivot table.<\/span><\/p>\n
First of all, let’s understand what a slicer is?<\/h3>\n
Slicer is also the same as filtering but instead of using a drop-down menu, we can set up the slicer beside itself and work upon it. The other advantage of a slicer is that it can be connected with one or more pivot tables.<\/span><\/p>\n
\u00a0<\/span>To set up a slicer to a pivot table, click on the <\/span>INSERT<\/b> tab and choose the slicer option.<\/span><\/p>\n
![\"Slicer](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/slicer.png\")
<\/a><\/p>\nOnce you click, you will see a box in the spreadsheet with the attributes of the dataset.<\/span><\/p>\n
Choose the attributes to which slicers have to be applied and then click <\/span>\u201cOK\u201d.<\/b><\/p>\n
Example:<\/b> For this dataset, I am going to click on the attribute \u201cgender\u201d and let\u2019s see how it works.<\/span><\/p>\n
![\"Slicer](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/gender-slicer.png\")
<\/a><\/p>\nIn this slicer, I have chosen only the female and that\u2019s why the table contains only the female count in it.<\/span><\/p>\n
If we wanted to filter for more than one variable, then you can click on multiple-choice option and choose whichever options you require in the slicer.<\/span><\/p>\n
Modifying the pivot table in Excel<\/h3>\n
In a pivot table, adding other columns is also possible. To do this, follow the steps:<\/span><\/p>\n
1: Click on the pivot table.<\/span><\/p>\n
2: You will find the fields and variables on the right side of the sheet.<\/span><\/p>\n
3: Click on the variable and drop it in the fields as per convenience.<\/span><\/p>\n
Let’s look at a sample:<\/span><\/p>\n
![\"modifying](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/05\/modifying-pivot.png\")
<\/a><\/p>\nNow, we wanted to add another attribute to the table. Here, we wanted to view the sum of estimated salaries also. So in order to add that to the table, check the box for the estimated salary and drop it in columns, values fields.<\/p>\n
![\"Modify](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/05\/mod-pivot-sample.png\")
<\/a><\/p>\nOnce we follow the above steps, the column is being viewed in the pivot table. Hence, we can modify a pivot table even after forming it.<\/p>\n
Deletion of columns in Excel<\/h3>\n
Now, let’s see how to delete a column in the pivot table. In order to delete a column from the pivot table, follow the steps:<\/p>\n
1: Right-click on the column.<\/p>\n
2: Choose the remove \u201ccolumn\u201d option from the list.<\/p>\n
![\"Remove](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/05\/remove-opt.png\")
<\/a><\/p>\nThe column will be deleted from the pivot table.
\nIn this sample, we wanted to delete the salary\/age column. After following the steps, the table will look like:<\/p>\n![\"Removing](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/05\/after-pressing-remove.png\")
<\/a><\/p>\nCustomizing the table in MS Excel<\/h3>\n
Using filters, we can choose the fields we have to focus on. In order to do that, follow the steps:<\/p>\n
1: Click on the arrow mark in the first variable.<\/p>\n
2: Deselect the select all option.<\/p>\n
3: Choose the required fields alone.<\/p>\n
![\"deselect](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/05\/deselect-all.png\")
<\/a><\/p>\n4: Press OK.<\/p>\n
In this sample, we are willing to look at the fields of 18,19 and 20 alone. Hence, we have selected only those fields in the option.<\/p>\n
We can also sort the data records in the table. In order to sort the data for your convenience, follow the steps:<\/p>\n
1: Right-click anywhere on the table.<\/p>\n
2: Choose the sort and select either sort from largest to smallest or the sort from smallest to largest.<\/p>\n
The table sorts it accordingly.<\/p>\n
Let’s look at a sample to see how it sorts the data records.<\/p>\n
![\"Sort](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/05\/sort.png\")
<\/a><\/p>\nIn this sample, we wanted to sort it from largest to smallest. So, we have chosen the sort largest to smallest option.<\/p>\n
The dataset now appears like this:<\/p>\n
![\"Output](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/05\/sort-sam.png\")
<\/a><\/p>\nHope, you can observe that the values are in descending order.<\/p>\n
This is one type of sorting and there is also another way to apply sorting to the table. In order to do that, follow the steps.<\/p>\n
1: Place the cursor anywhere on the pivot table.<\/p>\n
2: Click on the Data tab.<\/p>\n
3: Click on the sort option.<\/p>\n
Note<\/strong>: A box appears with radio button choices and with a summary. So, this can be helpful to you when you aren\u2019t sure about which type of sorting to apply. You can either apply row-wise sorting or column -wise sorting.<\/p>\n
When you choose the sort options and sort direction, the summary appears as per your dataset attributes and how it will perform. This is another way to apply sorting to the pivot table.<\/p>\n
![\"Sort](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/05\/sort-box.png\")
<\/a><\/p>\nCreating Excel Pivot table for multiple sheet data<\/h3>\n
In order to create a single pivot table for multiple sheets data, follow the steps:<\/p>\n
1: Press alt+D.<\/p>\n
2: Press p. A box appears like this.<\/p>\n
![\"Multiple](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/05\/step-1.png\")
<\/a><\/p>\n3: Choose the third option i.e., \u201cMultiple consolidation ranges\u201d.<\/p>\n
![\"Creating](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/05\/step1.png\")
<\/a><\/p>\n4: Press on the next option.<\/p>\n
5: Now choose \u201cI will create the page fields\u201d option.<\/p>\n
![\"Create](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/05\/step2.png\")
<\/a><\/p>\n6: Next, choose the range of the sheet. After adding a sheet range, click on the add button to add the next sheet range.<\/p>\n
![\"Pivot](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/05\/step2b.png\")
<\/a><\/p>\n7: Click on the Next option and press Finish option.<\/p>\n
Thus, the pivot table for the multiple sheet datasets will be visible now.<\/p>\n
2D pivot tables in Excel<\/h3>\n
Two-dimensional pivot tables are also known as 2d pivot tables. It is also like a simple pivot table but in this 2d pivot table, we will have the attributes in both row and column fields and that is why it is being termed as a 2D pivot table.<\/p>\n
![\"2D](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/05\/2d-pivot.png\")
<\/a><\/p>\nFor the sample dataset, we have generated the report for gender and estimated salary which is a 2-Dimensional pivot table.<\/p>\n
Pivot Charts in Excel<\/h3>\n
Charts are being used for better understanding of the dataset and we can also apply charts for the pivot table. This helps us to draw more insights of the dataset.<\/p>\n
- \u00a0<\/span>Make sure there is no unnecessary cell filled up in the dataset itself.<\/span><\/li>\n<\/ul>\n