In the modern and complex world, nowadays data has immense importance. However, simple raw form of data doesn’t help us to achieve our goals, the main thing is to derive conclusion and take decision from available data. It is too difficult for a person to analyse raw data as our mind requires data in a summarize form to analyse the same and draw conclusion from it.
Normal excel table won’t be much of use if there are large data sets. Here, pivot table comes into picture. Analyses with pivot tables becomes much more easier and can save lot of your time as it presents data into a summarize way.
Now let’s understand, what is Pivot tables?
- 1 What is Pivot table?
- 2 Why would you use a pivot table?
- 3 How to insert a pivot table in excel?
- 4 How to use different labels in Pivot Table with examples:
What is Pivot table?
Pivot table is a powerful excel tool to analyse, summarize and present large data in a way which creates ease in analyzing and drawing conclusion. Pivot table allows you to summarize data into excel, based on selected rows and columns.
Why would you use a pivot table?
Pivot tables can make your daily work more simpler and easy. There are many things for which you might putting lot of hard work to get done but can be done with the pivot tables in a one shot.
For e.g. If you have following sales register having columns of date, branch, customer name, item, quantity sold, selling price and total sales amount in a tabular form. (Pic1)
Let’s say, you want to analyse this data with
- Sales amount by Branch wise
- Customer wise Sales
- Sales by Item wise
- Daily, monthly, quarterly and yearly sales
- Percentage (%) wise sales to customer.
- Top 5 Customer (Sales amount wise) in Amount, in % and in Cumulative running %.
- Top 10 Branch (Sales amount wise)
Is it possible to summarize this data without pivot table?
Yaa it is possible, but it will take substantial amount of time to do the same. You could also avoid using sumif formula and countif in many cases by pivot table. So, you can make your work simpler by just learning pivot tables. Amazing! Keep reading to know How to insert a pivot table in excel.
How to insert a pivot table in excel?
There are two ways to insert a pivot table in excel.
- To use recommended pivot tables
- Manually inserting a pivot table into excel.
Recommended Pivot Tables
- Open the data table in which you want to insert recommended pivot tables
- Select any cell of the table or select entire table
- Select Insert >> Recommended pivot tables. After selecting you will get options of pivot tables based on your data set.
- Select any one of them based on your need. (You can modify your data later.)
- Press Ok and boom! Here you got your pivot table in just few seconds.
How to insert a pivot table manually
- Select any cell of the table or select entire table you want to analyse.
- Select Insert >> Pivot Table.
After selecting you will get following screen.
Here you can modify your table or range if your data table not automatically selected by excel or you can use external source (Such as Data table of Access) for pivot also.
- Now you have to choose whether you want the Pivot table report to be placed in New worksheet or in Existing worksheet. (If you choose existing worksheet, you need to select the cell in which you want to insert a pivot table.)
- Press OK and you will have your pivot table in new worksheet or in existing worksheet as per your choice.
Okay, we inserted pivot table into excel. Now what!
You can see there are four types of labels i.e. Filter label, Columns label, Rows label and Values label. Let’s understand each of them one by one.
How to use different labels in Pivot Table with examples:
For e.g. If you have following sales register having columns of date, branch, customer name, item, quantity sold, selling price and total sales amount in a tabular form.
After inserting pivot table, you will see the table with four different types of labels.
What is row label in Pivot table?
Row Label: The field enter row label will be shown vertically.
Points to be considered while using Row Label.
- Row label removes duplicates of the field.
- Row label arranges data into ascending order.
Here drag the field of “customer” into rows label.
What is filter label in Pivot table?
Filter Label: Field you add in this label will be shown on the top of pivot table and you can have filter the pivot data based on fields in filter label. You can filter row labels also but there is a big difference. To know the difference stay connected to us and subscribe our blog to get updates directly to your inbox.
Now, add Branch into filters.
Here you get Branch in filter. Select the down arrow on the right-hand cell of filter label to see the options.
You can select any one of the branch in filter or else you can also select multiple items by selecting check box in filter menu.
What is column label in Pivot table?
Column Label: The field enter column label will be shown horizontally.
Suppose you want to analyse what product is sold to what customer.
Add Item to column label.
But nothing is coming in the intersection of items and row labels!! Because you haven’t added any field into value label.
What is values label in Pivot table?
Values Label: Field which includes data in figure should be dragged in this label to do the analyses by sum, count, min, max, product etc.
- If you want to analyse how much quantity of items sold to each of customer, add Qty in Values label.
- If you want to analyse how much sales made to each of customer having item wise bifurcation, add amount in Values label. (Remove the quantity from value filter, if you want to analyse amount wise only.)
Here you go!! You have mastered basic of pivot tables. Cheers!! Try out now.
Stay Connected to know
- How to convert dates into months in pivot table?
- How to change layout of the pivot tables?
- What is Pivot Table Slicer and How to use it effectively?
- How to change value filters of pivot tables to analyse by count, sum, min, max, % wise and many more?
- How to generate individual sheets based on filters label?
- How to refresh pivot table?
- How to change the data source of pivot table?
- How to have a subtotal based on your requirement?
Don’t forget to share and subscribe our blog via email, if you loved post.
Have any suggestion/ recommendation? Leave a comment.