Learn Everything about Excel Pivot tables
When you have large amount of data in your worksheet and you can’t analyze or sort the data then Pivot Table helps you to carry out the task easily and more conveniently. Pivot Tables are powerful functions in excel. To know how powerful Pivot Table can be let’s go through the details step by step.
? What is Pivot Table?
A Pivot Table helps to get an overview of the data represented in the Excel spreadsheet. Pivot Tables allows you to analyze, summarize the data for easy analysis, quickly extract the data from any part of the Pivot Table and show only relevant data in our reports. Bill Jelen and Mike Alexander are referred as “Father of Pivot Tables.” They have defined Pivot Table as “a tool that would help users to recognize that these patterns will be helpful to build advance data more quickly”
In just few clicks, you can represent your data in charts, tabular and more presentable form using Pivot Table. It can sort all your relevant data into respective columns and rows, into charts. It can even highlight key words or important points within few clicks and can save your valuable time.
? Why Table for a Pivot Tables?
Reasons to use tables to create Pivot Tables are as follow:
1. Handle a large amount of data for reporting: Pivots Tables are capable of handling a large amount of data but in an easier way to understand the data
2. Pivot field across and down your report: You can view your report in the three-dimensional form rather than in a flat traditional form
3. Display complex data graphically: Graphical representation helps to understand the report more easily through charts. It usually saves time for analyzing and decision making
4. Sort and filter data: You can easily sort and filter your data in the Pivot Table
5. Drill-down your data: You can drill down your data very easily at a time.
6. Use Slicer and Timelines: You can use additional features of Pivot Tables like the Slicer and Timelines. Slicer is used for graphical representation whereas timelines are used to sort data according to the dates
7. Refresh data: As explained earlier, we can refresh data into the Pivot Table when you alter data at the source data
? Pivot Tables Terminology
This is a tool where your data is summarized to sort and analyze the data automatically. It pulls information from one table and results are displayed on another table. It makes us easy to retrieve specific information easily from a large source. The terms used in the Pivot Table are as follows:
1. COLUMN FIELD: A field that is assigned a column orientation in the Pivot Table report
2. DATA AREAS: A cell in the Pivot Table that contains a detailed summary of the data
3. GRAND TOTALS: Total of all rows and columns in the Pivot Tables
4. GROUPS: Collection of items that were treated as single items. You can group manually or automatically
5. ITEM: An element that appears as a row or column header in the Pivot Table
6. PAGE FIELD: You will see a list of all the fields that were in your source data. It is at the upper left of the Pivot Table report
7. REFRESH: To update the data in the Pivot Table after alteration in the summary data
8. ROW FIELD: A field that is assigned a row orientation in the Pivot Table report
9. SOURCE DATA: The data which is used to create Pivot Tables
10. SUBTOTALS: Separate column total and row total can also be done in the Pivot Table report
? How to Quickly Create a Pivot Table in 30 Seconds?
Before you create a Pivot Table you need to keep the following things in mind:
1. Your data should be in tabular form.
2. The data in the column should be same. Text and number should not be in the same column.
3. No column or row should be left empty. The same was while we do Autosum.
4. First row should contain unique, short heading.
5. It should provide details of similar records. For example, if sales is the head then it should include data relating to sales and not purchase.
After you do this and have your data ready in the form prescribed above you can now create Pivot Table in less than 30 Seconds.
let us see how we can create a pivot table using data set of 300 rows.
Steps to create Pivot Table:
Step 1: Select any cell from your data set, also you can press Excel Shortcut key Ctrl + A.
Step 2: From the Insert Tab in the ribbon click on the Pivot Table option.
Step 3: A dialogue box gets open that enables you to select the data. It by default chooses a new worksheet to store the Pivot Table.
Step 4: Click on OK and right click on Pivot Table and select Pivot Table options.
Step 5: Click on the display button and enable classic Pivot Table layout option.
Step 6: Now just drag the Rating column from Pivot Table fields window into the Row field and Salary p.a. (US$) column into the Value field.
? What is Recommended Pivot Table?
Many people believe that creating a Pivot Table is a long, complicated and time consuming process. However, you are wrong if you think so. Pivot Tables will save a lot of time that you will use while creating table manually. It is faster and more flexible.
If you have less knowledge about Pivot Tables then you can use the Recommended Pivot Table. This table will recommend you on the basis of your data collected. You can arrange the data, add new fields to the data, explore different orientation and many more. All this will be explained as you go through this tutorial.
For creating Recommended Pivot Table, follow the below steps.
Step 1: Select the range of cells from your data
Step 2: Click on the recommended Pivot Table option in the Insert ribbon
Step 3: It provides you with various options. Here we are selecting Sum of Salary p.a by Rating option.
Step 4: Click on OK. Excel will create a Recommended Pivot Table for you
The same you can achieve it manually without using the Recommended Pivot Table option. learn How to create a Pivot table Manually?
? How to refresh Data in Pivot Table?
Whenever you want to change data or add data into your Pivot table you simply don’t have to create full Pivot Table again. If you add any new data or change any value in the data and you have to refresh those data in your Pivot Table, then you can carry out using three types:
TYPE 1: If you want to make single change in your data then go to –
Ribbon > PIVOT TABLE TOOLS > Options > Refresh
If there are more than one change you want to refresh then go to-
Ribbon > PIVOT TABLE TOOLS > ANALYZE > Data > Refresh All
(Excel Shortcut keys Refresh All is CTRL + ALT + F5)
TYPE 2: Press ALT + F5
TYPE 3: Right click on the Pivot Table and select Refresh option.
The Pivot Table shown in below picture is created for 296 employees and suddenly a new employee joins the firm. In such case, no need to create the table again.
Just add the details of the new employee in your data table and Refresh the Pivot Table as explained above. This will update the Pivot Table automatically.
? How to Drill Down into a Pivot Table?
When you create a Pivot table, it reorganizes and summarizes the selected columns and rows of data. This lets you see a total displayed in a Pivot Table. You can easily Drill Down to visualize and extract the data that makes up the total in the Pivot Table. To Drill Down, simply double-click on the number and Excel will add a new sheet to your workbook that contains the data used for calculation.
Example of Drill down Pivot Tables
Here, we have a Pivot Table showing the Division and number of Employees working in that Division. To view the details of employees for each Division, use the Drill Down method.
As shown in the picture below, 28 employees work in Division AD.
If we want to view the details of those 28 Employees who work in Division AD, then just double click on the cell containing number 28 and a new sheet will get open with all the details of those 28 employees.
Likewise, you can view the details of different employees also working in other divisions.
? How to add fields to your Pivot Table?
Sometimes, it may happen that you want to add a new field into the Pivot Table after you have created a Pivot Table. In such cases, you can add fields in the Pivot Table very easily.
Find the Pivot Table in the picture below. It contains the information about the total salary drawn by the employees of each division. Now, if you want to make your Pivot Table more efficient by adding more details, you can do it by following steps:
Step 1: Click anywhere in the Pivot Table you created, it will show the field list
Step 2: Check the boxes on the field you want to add or drag directly the fields into the Pivot Table. Here, we have added the following data:
>> Rating data to the Report Filter field so that we can filter the Pivot Table w.r.t Rating of employees
>> We have grouped the Age data and added to columns filed. (Grouping is explained in detail below)
>> This Pivot Table gives you the information about total salary drawn by the employees from each division between the age group of 19-28, 29-38 and so on. You can also filter the Rating column for deep details.
Hope you like our tutorial for more cool and amazing trick of excel Like our Excel Superstar Facebook Page and subscribe to our Excel Superstar YouTube channel. Excel Superstar is the leading online training company, which provides Online Excel Course in Hindi. Connect with us and become an Excel Superstar.
Learn 100+ Excel Tips & Tricks