Introduction

Excel pivot tables allow you to automatically sort, count, total or average selected fields of your raw dataset to gain powerful insights. By dragging and dropping fields, you can interactively change the perspective of your data within seconds. Fields can be positioned into row labels, column labels, or values areas. Multiple fields can also be nested within each other when placed into the row or column labels areas. Filters allow you to instantly select or deselect fields to include or exclude from your analysis with a single click. You can also change layouts by dragging fields between the row and column labels areas to alter how information is grouped or summarized. In this guide, we’ll go through how to easily create a pivot table from an existing data source and customize fields, filters, rows and columns to gain different views into your information.

Creating a Pivot Table

Setting Up Sample Data

For our sample, we’ll use an ecommerce transactions dataset instead of the outdated fruit sales data. This contains fields like Product, Date, Customer, Price, etc.

Inserting a Pivot Table

1. Select any cell within the sample data.
2. Go to the Insert tab and click Pivot Table.
3. Choose a New Worksheet option for the report.

Positioning Fields

Fields from the sample data will appear in the pivot table field list panel. Positions include:

• Row Labels
• Column Labels
• Values

Drag fields to customize the initial view. See Figure 1 for an example layout.

Customizing the Pivot Table

Filtering Data

1. Click the dropdown arrow next to a field label
2. A filter menu appears listing all field values
3. Check/uncheck values to include/exclude

Rearranging Rows and Columns

Simply drag fields between the Row Labels and Column Labels areas to alter layouts.

Using Calculated Fields and Filters

Newer Excel pivots support calculated fields to perform additional analytics on the data. Timeline filtering allows interactive filtering on date fields.

Advanced Techniques

For more advanced learning on pivot charts, crosstabs, and other powerful pivot table features, check out the resources listed here.

Here are some additional ways to customize pivot tables in Excel:

• Format cells/numbers – Change number formats like currency, percentage, decimals, etc. Right click on values cell and select format cells.
• group dates/values – Group dates by month, quarter, year using the group selection under the pivot table options. Group values using intervals.
• add totals/subtotals – Use the pivot table options to add and custom count, sum, average subtotals or grand totals to rows or columns.
• Slicers – Add slicers to allow interactive filtering across multiple pivot tables at once. Slicers work like visual filters.
• Timelines – Filter dates using the built-in timeline instead of a manual list of dates in a filter.
• Calculated fields/items – Use formulas in new or existing fields to perform calculations on the data.
• Report filters – Filter field values before they appear in the pivot table for conditional views.
• Pivot charts – Add pivot charts to visualize trends in the pivot table data interactively.
• Page/timeline filters – Limit data shown to a certain page or time period using these filtering options.
• Formatting/styles – Use pivot table styles, conditional formatting, number formats to visualize and emphasize the data.
• Field settings – Change calculation type, format fields, show/hide buttons under the field options contextual tab.

Adding Slicers to a Pivot Table in Excel

Here are the steps to add slicers to a pivot table in Excel:

1. Have your base pivot table created with appropriate fields represented as rows, columns, or filters.
2. Go to the PivotTable Tools > Analyze tab and click on Slicers.
3. Select the fields you want to slice or filter the pivot table with. You can select multiple fields.
4. Click OK. Slicers will appear on a new Slicer pane on the right side of the sheet.
5. Interact with the slicers by selecting or deselecting values.
6. The pivot table will dynamically update to only show data matching your slicer selections.
7. To move a slicer, click and drag its header bar to reposition on the sheet.
8. Formatting options allow changing colors, filtering behavior, and more.
9. Slicers work across multiple pivot tables simultaneously, providing a powerful dashboard filter.
10. To remove slicers, go back to the Analyze tab and click Slicer and select none.

The key benefit of slicers is the ability to interactively filter pivot tables with visual selections instead of dropdown lists. This improves the exploration of data relationships.