When there is a lot of data, it is very difficult to analyse all in your worksheet or excel sheet. Pivot tables help in making your excel sheet easily manageable by data summarizing and manipulating in several ways.
A pivot table is one of the most intimidating as well as the powerful function of excel sheet. It is powerful because it summarizes large data very easily. So you must know how to create a pivot table in excel.
But before that, you should have clear knowledge about the pivot table.
What is a Pivot Table?
In simple terms, the Pivot table is a summary of your data that is present in a chart that allows you to report on and explore various trends depending on your information.
A pivot table is useful when you have long rows or columns of values that you require to keep a track of its sums and make a comparison with each other.
A pivot table helps in extracting some sense from a jumble of numbers present in your screen. You can group your data in several ways and draw some useful conclusions easily.
The word pivot in the pivot table comes from the fact that you can easily rotate your data reset in the table so that you can view it from a different perspective.
It reorganizes your data so that you can easily reveal some useful information from it. If you have a huge load of data to analyse then it’s important to know how to build a pivot table in excel.
How You Can Use the Pivot Tables?
A pivot table is very easy to understand and there are certain scenarios where you can use the pivot tables –
- When you are comparing the total sales of various products
- Display product sales as the percentage of overall sales
- For combining duplicate data
- Determining employee headcount for varied departments
- Adding default values to empty cells
Now, let’s discuss the steps on how to make a Pivot Table in Excel 2010 and 2016.
How to Create a Pivot Table in Excel 2010 & 2016?
The process to create a pivot table in the same for both Excel 2010 and excel 2016 but in excel 2016 there are some added features. Let’s discuss how to create a pivot table –
At first, open the worksheet that has the table which you want to summarize through pivot table and choose any cell in the table. Make sure there are no blank rows or columns and in each column, there is a header.
Now click on the pivot table button present in the Tables group on the Insert tab. Now excel opens up the “Create pivot table dialogue box” and selects all data present in the table. If required adjust the range in the table or range text box under the select a table or range option button.
If the source of data for your pivot table is an external database table that is made with some different program like Access, then click on “Use an External Data Source option button, and then select connection button and then the name of the connection in the already present connections dialogue box.
Now select a location for the pivot table. By default when you built a pivot table on a new worksheet it is added to the workbook. But if you want that the pivot table to appear in the existing worksheet, click on the existing worksheet option button and then indicate the location of the first cell of the new table present in the location text box.
Click on the OK button. Excel now adds a blank grid for a new table and displays a pivot table field list task pane on the right side of the worksheet area.
Now to complete the pivot table, assign the fields in the Pivot Table Field List task pane to several parts of the table.
You can do this simply by dragging the required field name from the Choose fields to add to report list box and then drop it in one of the four areas called drop zones.
Some of the important field areas are –
- Report filter: It allows you to page through the data summaries that are shown in the actual pivot table by bringing out sets of data. They act as the filters for the report.
- Column labels: This contains fields that help in the determination of the data arrangement of all data that is shown in the pivot table columns.
- Row labels: This area contains fields that help in the determination of the data arrangement that is shown in the rows of the pivot table.
- Values: Fields that estimates the data that are present in the cells of the pivot table.
Then there are several other options like field list, +/-button, Field headers etc that you can add according to your necessary after you insert a pivot table in excel.
Additional Features you can find In Excel 2016 while Building Pivot Table are –
- Automatic relationship detection: In older excel versions, pivoting of more than one sets of data was difficult as you have to utilize the relationship tool for defining the connecting field names in between various database tables. But in Excel 2016, the auto-detect button helps in comparison of multiple databases for identification of common field names and then the establishment of the joins easily.
- Create, edit and delete custom measures: In excel 2016, you can easily create and edit custom measures directly from the Pivot tables field list. The measure dialogue box helps in creating new formulas by using DAX formula programming language.
- Automatic grouping of date and time: In excel 2016, you can automatically group date and time-related fields in your Pivot table.
- Drill down buttons: With Excel 2016, you can easily zoom in and out of your pivot charts in groupings of time and other such structures.
- Smart renaming: In excel 2016, you can easily rename tables and columns in your worksheet data models.
- Multi-select slicer: You can easily select multiple items in an Excel slicer in a touch device.
- Get and transform: Excel 2016 offers a new Get and transform tool that helps you in connecting to data sources and collect and import the resulting data.
So, this is all about how to make pivot tables in excel sheet and how you can use it and make your work easy.
Was this information about creating excel pivot table useful? If yes, then do share with others on social media and help to summarize huge data without any hassle.