A pivot table is a tool that is available in Excel which helps you summarise large chunks of data effortlessly and quickly. It collects all the relevant information in a spreadsheet and summarizes it in a table format and can be shown as a report. The table has information like the items in each type, data column sum, data column average based on data types. All of these information makes an analysis of data easy.
The Areas Of A Pivot Table
A Pivot Table consists of four areas:
Rows: This is the place where the outline of the report is built on. The fields that are needed in the report are put into the rows area and only the unique values are displayed in the first column. The duplicate fields are all removed automatically by the pivot table. The source data layout is of utmost importance for the report to be generated properly.
Values: It contains the data that is to be summarized in the report. The pivot table automatically calculates the sum or count of the data when a field is entered into the Values area. A count is calculated if the data has text, it can be changed to many other functions like Average, Min, Max and much more.
Filter: The source data is filtered by the pivot table based on the criteria in the column, rows or fields area. It then calculates the sum and it then repeats the process for every cell in the Values area. The data structure is critical in the calculation.
Columns: The working of rows and columns area are similar like the rows are only unique values are listed in the pivot table. Columns area differ from rows area in one aspect and that is it displays the values on the top row of the table.
Things To Know About Pivot Table:
Build it easily in a few minutes:It is believed that building a pivot table is complicated and hence time-consuming. But on the contrary, it can be built in minutes compared to creating a manual report. The only requirement for creating a pivot table is to have a source data which is organized.
Count anything & everything:It can count any text field and is a handy tool to have for business calculations. For example, if you want to count the number of employees in every department, it can be done easily. All you have to do is add Department in the Rows area, add the name of the employee in the Value and the table will show the count of employees in each department.
Clone the pivot table for a different view:After you have created the pivot table and wish to have a different view of it then the best way is to clone the table so that both the data views are handy. Cloning can be done either by duplicating the spreadsheet or by copy-paste method.
Format even the empty cells:If the pivot table has blank cells, you can set a character so that it can be displayed in those cells instead of showing nothing.