Hello Everyone! In this blog, I will be talking about the basics of Pivot Table in Excel and if you have any questions regarding about How Pivot Table works in Excel, then I would say only one thing that if you want to work in Excel then you must know the trick inside out. So let us see, How this Data will help us to analyze it.
To understand better about How Pivot Table works I will teach you with the help of an example given below:
50% OFF on Excel Course
Let us think that I have a Pickle company in which I make different types of pickles like Green Chili, Carrot, Tomato, and many more. Now I want to make a report and see that in which states my orders or pickle are going and which pickle is giving me more advantages or more sales.
Pivot Table Prerequisites
So in order to make and use a Pivot Table, there are 2 important points which you need to remember in Excel:
- No Header should remain Blank and if it is there, then filled the Header
- No Cell should be merged and if it is there then to unmerged you should do this:
- Click on Home Button
- Now navigate the section and click on Merge and Center
- A pop-up screen will open, click on OK
- Lastly, your cell will be unmerged
How to Make a Pivot Table in Excel?
To make a Pivot Table in Excel you have to remember the 2 main points which are given above. So follow the given step by step procedure in order to make a Pivot Table
Step 1: Choose the Entire Data or Click Shortcut keys Ctrl + A
Step 2: Go to Insert Tab than click on the Pivot Table
Step 3: A pop-up screen will appear to click on OK
Change Setting into Classic Options
Now if you want to take full advantage of Pivot Table then you must turn on one important setting that is:
Step 1: Right-click the cell than Go to Pivot Table Option
Step 2: After clicking the option go to Display Tab than Tick the Classic Pivot Table layout option Lastly, press OK
Understanding the Pivot Table Layout
Now there are 4 different sections in front of you which are:
- Drop Row Fields
- Drop Column Fields
- Drop Report Filter Field
- Drop Value Fields
So after making a Pivot Table and knowing all the 4 different sections let us understand How to make a Report in Excel.
How to Make a Report in Excel?
If you are seeing the fields on the right-hand side, then don’t worry these are the same fields which you have seen in the table header. Also, the same thing is reported so that you can select and see which data is converted into a report
Well, if you wish how many sales amount which means how much amount of pickle you have sold or how much value of pickle you have sold so to see if you can do this:
Step 1: Drag State into the Row field area
Step 2: Now drag and drop the sales field into the Value fields area
Now if you want to see how much orders we have got then you can see by doing this:
Step 1: Drag and drop the Sales field from the right-hand side into the Value fields area
Step 2: Right Click on the Sum of sales 2 Now go to Summarize Value
Step 3: There will be several options, click on Count
Result: You will show the Count of sales
Pivot Table Data Sorting
So here we have seen that we got a total of 9 orders which generates a total value of 7425. As you can see how easily you have got the Count as well as Sum Value. Now if you wish to sort your value from largest to smallest, then what you will do is:
Step 1: Right Click the value cell than Go to sort section
Step 2: Select the option from Largest to Smallest
So now you can see which states you have got the maximum amount of sales and orders
So guys this was all about How to Make a Pivot Table in Excel. By following the step by step guide from our blog you can easily learn to make Pivot Table in Excel in very less time. So learn and practice daily if you are really looking to learn Pivot Table in Excel.