Excel PivotTables—if you have used the SUBTOTAL function in Excel, you may have been impressed by its ability to calculate functions such as SUM, AVERAGE, MAX, MIN, PRODUCT, and COUNT directly in conjunction with filters. However, this is only a small part of what PivotTables can do. Let’s learn about PivotTables, one of Excel’s core features.
Excel PivotTables
Let’s look at the definition of PivotTables and how to use them. If you are new to PivotTables, we recommend following the steps in order and practicing each one.
Definition
An Excel PivotTable is a tool used to summarize, analyze, and visualize large amounts of data. A PivotTable displays data in a table organized into rows and columns, allowing you to summarize and analyze the data as needed.
How to Use PivotTables
Using a PivotTable can be divided into six steps.
- Organize Your Data
: Enter data characteristics in each column and individual items in each row for the data you want to use in the PivotTable.
When selecting data, use “Excel for Windows: CTRL + A” or “Excel for Mac: COMMAND + A” to select all data.
- Create a PivotTable
: After selecting the data, use the “PivotTable” feature to create a new PivotTable. Select the necessary options and place the PivotTable in the desired location.
When you select “PivotTable,” the following default screen appears.
- Choose a Summary Method
: In the default PivotTable, place the salesperson name in ROWS and sales quantity in VALUES to create an Excel PivotTable like the one below.
- Filter and Group
: You can filter or group data as needed to focus on the information you want. This makes it easier to analyze the data you need. The data below groups sales quantities by product for each salesperson.
- Visualize Data
: PivotTables make it easy to create charts that help you quickly identify data patterns and trends.
- Update
: If the source data changes, use the REFRESH option to update the PivotTable. You can also use this option to set or change the data range.
Conclusion
Excel has many features and functions. Although you cannot use every feature 100%, Excel PivotTables are an essential feature that goes beyond basic methods of data analysis.