Features of Pivot Table in Excel

An incredibly effective tool for data summarization is the Pivot Table. It is especially helpful for analyzing large datasets. They can be used to calculate sums, averages, or other statistics for data in a table. Pivot tables can also be used to create charts and graphs based on the data in the table. In this article, I will describe to you the different features of the Pivot Table and the use of these features of the Pivot Table in Excel.

Introduction to the Dataset

To show you how to use a Pivot Table, I’ve made a dataset of some delivery details. The data table is pretty large. It has 100 rows.

To make my work organized, I am going to create a Pivot Table using this dataset.

To create a pivot table using the above dataset, read this article.

Features of Pivot Table in Excel

1. Opportunity to Add or Remove a Field

As you apply a Pivot Table on your datasheet, you will notice the PivotTable Fields task pane on the right side of your computer screen. The PivotTable Fields task pane allows you to create a pivot table in various combinations of your columns.

Add a Field

To add a field to the table, simply tick on the checkbox next to a column name in the PivotTable Fields task pane.

Remove a Field

You can remove a field from the table in two ways.

One way is to unmark the checkbox next to a column name.

And the second one is:

Step_1: Right-click on the field that you want to remove.

Step_2: Then remove the field name from the context menu.

2. Flexibility to Arrange the Fields

You can filter a column or find the values from a particular column. You can also turn the rows into columns and vice-versa.

To do these jobs, drag a column name with the help of the mouse and drop them into your preferred areas such as Filters, Columns, Rows, and Values.

Here is an interesting thing to share. You will see the columns which contain numeric values in the Values Area. On the other hand, the non-numeric fields are inside the Rows Area.

3. Availability of Various Functions

Pivot Table by default shows the sum-up results of the numerical values using the SUM function. Microsoft Excel also designed the Pivot Table in a way that, if you drag any field from the Rows area into the Values area, the COUNT function will be activated for that field.

Apart from the SUM and COUNT functions, you can apply various useful functions like AVERAGE, MIN, MAX, PRODUCT, etc. on your table.

Steps to Apply a Function

Step_1: First, right-click on your preferred numerical value field.

The context menu will appear.

Step_2: Then, take the cursor on Summarize Values By option.

Step_3: Then, choose a function from the list.

There are also more functions available like StdDev, StdDevp, Var, Varp, etc.

Click on the More Options command from the context menu.

This will lead you to the Value Field Settings dialog box. Choose your preferred function from the function list.

4. Options to Apply Various Formats

Pivot Table has some fantastic built-in options to represent the values in different ways. You can show the percentage value of various fields. Explore these features from Show Values As command.

Apply a Value Format

Step_1: First, right-click on a field that has numerical values.

The context menu will show up.

Step_2: After that, take the cursor on Show Values As.

Step_3: Then, choose a command from the list.

5. Sort and Filter Any Fields

You may need to organize the table with certain criteria like smallest to latest or displaying certain fields. For that reason, Pivot Table allows you to the Sort and Filter options. To find these options, just right-click on any non-numerical field and find these options on the context menu.

Application of Pivot Table

Pivot Tables are especially useful for data with a lot of rows and columns. For example, a table with sales data for a year might have thousands of rows and dozens of columns. Pivot Tables can be used to quickly summarize this data to show total sales for each month or average sales for each product. The main applications of the Pivot Table are:

• Summarizes the results with subtotals and totals.
• To apply different data formats.
• Organizes unique values in a few seconds.
• Allows doing various arithmetic calculations. (counts, sums, averages, etc.)
• Generates pivot charts.
• To filter & sort the results.
• Connecting datasets outside of an Excel sheet and creating pivot reports compiling the sheets.

Conclusion

Pivot Tables are a flexible tool that can be used in many different ways. Now, you can experiment with different ways of summarizing your data to see what works best for your needs. Tell us how you like this article in the comment section. Also, please visit our Blog page for more helpful articles about Excel. Have a nice day!