How to Use Pivot Tables in Excel 2021

Edited 1 week ago by ExtremeHow Editorial Team

Microsoft OfficeExcelPivot TablesAdvancedProductivityWindowsMacBusiness

Pivot tables are a powerful feature of Microsoft Excel and they allow you to organize, summarize, and analyze your data in a way that yields meaningful information. In Excel 2021, using pivot tables can help you transform your raw data into a more understandable format. This guide will show you how to create and use pivot tables in Excel 2021.

Understanding pivot tables

Before learning how to create a pivot table, it is important to understand what they are. A pivot table is essentially a reporting tool or technique that allows you to summarize large amounts of data. For example, if you have a large dataset with sales transactions, a pivot table can help you quickly view total sales by region, product, or sales representative.

Pivot tables are interactive and allow you to pivot or move the data to view it from different perspectives. This is extremely helpful when you are considering different ways to analyze your dataset.

Getting started with pivot tables

To get started with Pivot Tables in Excel 2021:

1. Open Excel and load data: Open your Excel workbook that contains the data you want to analyze. Your data should be organized in a columnar format, including headers. Each column should have a title, such as "Date", "Sales Amount", "Area", etc.
2. Select your data: Highlight the range of cells that contains the data you want to use for your pivot table. This includes all rows and headers of the data.
3. Insert the pivot table: With your data selected, go to the "Insert" tab on the Excel ribbon. Click the "PivotTable" button. A dialog box will appear, asking you where you want to place your pivot table. You can choose to place it on a new worksheet or an existing worksheet. Click "OK" to create the pivot table.

Creating a pivot table

After you insert a pivot table, you'll see a new grid appear at the location you selected alongside the PivotTable Fields pane on the right side of Excel. This is where you'll select and arrange the data fields to create your analysis:

• Rows: Drag and drop the fields you want to appear as rows in your pivot table into the "Rows" area. For example, if you want to analyze sales by region, you would drag the "Region" field into this area.
• Columns: If you want to further segment your data, you can drag a field to the "Columns" area. For example, dragging "Product Category" here will allow you to see sales data broken down by products in different regions.
• Values: This is where you place the field that contains the data you want to measure. Most of the time, this is numerical data such as "sales amount" or "quantity". This is summarized or counted in your pivot table.
• Filter: If you want to be able to filter your entire pivot table by another field (such as “Date” or “Vendor”), you can drag that field to the “Filter” area.

Customizing a pivot table

Once the pivot table is created, you may want to customize it to suit your needs:

• Summarizing data: By default, Excel will sum the numerical values placed in the "Values" area. If you need a different type of summary, such as an average or count, click the small arrow next to the field in the "Values" area and select "Values Field Settings." Here, you can change the summary function.
• Sorting data: You can sort the data in your pivot table in both ascending and descending order. Simply click any of the arrow buttons next to the fields in the pivot table, and choose the sorting option you want.
• Pivot table styles: Excel provides several built-in styles to beautify your pivot table. You can access these from the "Design" tab on the pivot table's ribbon. Choose a style that makes your data presentation clear.

Using filters and slicers

Filters are an important tool that helps you understand specific sections of your data without the noise of other entries. Applying filters in a pivot table is done like this:

• Filter dialog box: Click the down arrow next to the filter's field name. Check or uncheck the boxes in the filter dialog to show or hide the selected data.
• Slicer: A slicer is a visual filter that allows you to interactively filter data in a pivot table. You can insert a slicer from the "Insert Slicer" button on the Pivot Table Analyze menu. Select the field for which you need to add a slicer, and it will appear in your sheet.

Grouping data in a pivot table

In some cases, you may want to group the data in your pivot table. For example, you may want to group daily sales data into months or quarters. Here's how to do it:

1. Select the data: Click one of the cells that contain the date in your pivot table.
2. Group by options: Right-click a cell, then choose "Group" from the menu. In the grouping dialog, choose how you want your data grouped -- by days, months, quarters, and even years.

Refreshing a pivot table

If you make changes to your original dataset (such as adding new rows or columns), Excel doesn't automatically update your pivot table. You must refresh it:

1. Refresh a pivot table: Click anywhere inside your pivot table. Go to the "PivotTable Analyze" tab on the ribbon and click the "Refresh" button.

Advanced features with pivot tables

Excel Pivot Tables also allow you to perform more advanced manipulations and calculations, such as:

• Calculated fields: You can create calculations using other data within a pivot table. For example, if you need a field that divides "Total Sales" by "Number of Transactions" to get "Average Sales per Transaction."
• Using Excel formulas: You can use Excel formulas in your pivot tables through the options in “Value field settings.”

Benefits of using pivot tables

Pivot tables provide several benefits:

• Easy data analysis: Quickly summarize large amounts of data.
• Dynamic reporting: Easily modify fields and filters for different data views.
• Time savings: The need for complex formulas or lengthy data processing is reduced.
• Enhancing Business Intelligence: Provides the comprehensive data view needed to make decisions.

Conclusion

Learning how to use pivot tables in Excel 2021 can be a transformational skill for those who handle data regularly. Whether for business analysis, school projects, or personal finance, pivot tables offer an unparalleled level of efficiency and excellence in data handling. By easily summarizing, sorting, and evaluating large data sets, they become a tool you can't ignore. With practice and experimentation, you'll find many ways to customize pivot tables to suit your specific needs.

  Share This Article

If you find anything wrong with the article content, you can request an update