Create Pivot Table Step-by-Step in Excel

Excel 7 Min Read

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:

Pivot table example
Pivot Table example datasets

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:

  1. No Header should remain Blank and if it is there, then filled the Header
  2. No Cell should be merged and if it is there then to unmerged you should do this:
    1. Click on Home Button
    2. Now navigate the section and click on Merge and Center
    3. A pop-up screen will open, click on OK
    4. 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

Select Datasets - Pivot Table

Step 2: Go to Insert Tab than click on the Pivot Table

Select pivot table
Go to Insert Tab than Pivot Table

Step 3: A pop-up screen will appear to click on OK

Pivot table popup
Create Pivot Table Popup

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

Pivot Table Options

Step 2: After clicking the option go to Display Tab than Tick the Classic Pivot Table layout option Lastly, press OK

Classic pivot table
Classic Pivot Table layout

Understanding the Pivot Table Layout

Pivot Table Layout

Now there are 4 different sections in front of you which are:

  1. Drop Row Fields
  2. Drop Column Fields
  3. Drop Report Filter Field
  4. 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

Pivot table drag area
Pivot Table State-wise sales

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 

Summarize values by count - Pivot Table
Summarize Pivot Table Value by count

Result: You will show the Count of sales

Pivot Table Count of sales result

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

Pivot Table Data Sorting

So now you can see which states you have got the maximum amount of sales and orders

Pivot table Data sorting
Pivot table data Sorting Result

Conclusion:

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.

Tags: