Microsoft Excel 2016 Pro is one of the most widely used tools for data organization and analysis. Among its most powerful features is the PivotTable, which allows users to summarize, analyze, explore, and present large datasets quickly and efficiently.
Whether you’re a beginner or looking to refine your PivotTable skills, this in-depth guide will walk you through the entire process—from data preparation to advanced PivotTable customization.
We’ll also link to helpful features in other Office tools like Microsoft Word 2016 Pro to help you present your findings professionally.
What is a PivotTable?
A PivotTable is a data summarization tool that’s used in Excel to sort, reorganize, group, count, total or average data stored in a table. It allows you to extract meaningful information from a large, detailed dataset without using complex formulas.
Why Use PivotTables?
- Analyze large amounts of data efficiently
- Summarize information with drag-and-drop ease
- Filter and drill down into details
- Discover patterns and trends
- Present insights for reports or decisions
Preparing Your Data
Before creating a PivotTable, ensure your data is clean and organized.
Guidelines:
- Your data should be in a tabular format (no blank rows/columns).
- Each column must have a unique header.
- Avoid merged cells.
- Make sure data types are consistent within columns (e.g., all dates, all numbers).
Example:
| Region | Salesperson | Product | Sales | Date |
| East | Alice | Printer | 500 | 01/01/2024 |
| West | Bob | Laptop | 1200 | 01/02/2024 |
How to Create a PivotTable in Excel 2016 Pro
Step 1: Select Your Data
- Click anywhere inside your data range.
- Or manually select the entire table.
Step 2: Insert a PivotTable
- Go to the Insert tab on the Ribbon.
- Click on PivotTable.
- In the dialog box:
- Choose your data range (selected automatically if you’re clicked in a table).
- Decide whether to place the PivotTable in a new worksheet or the current one.
- Click OK.
Step 3: Building Your PivotTable
The PivotTable Field List appears on the right.
There are four main areas:
- Filters: For filtering the whole PivotTable.
- Columns: Fields to display across the top.
- Rows: Fields to display down the left.
- Values: Fields to summarize (e.g., totals, averages).
Example:
To analyze total sales per region:
- Drag “Region” to Rows.
- Drag “Sales” to Values.
Customizing Your PivotTable
1. Change the Summary Calculation
By default, numerical fields are summed.
To change:
- Click the dropdown on the value field in the PivotTable.
- Choose Value Field Settings.
- Select other options like Count, Average, Max, Min, etc.
2. Format Numbers
- Right-click a value in the PivotTable.
- Choose Number Format to apply currency, percentage, or custom formatting.
3. Rename Fields
- Click on a field name in the PivotTable and type a new, more descriptive name.
Using PivotTable Tools
Excel 2016 introduces the PivotTable Tools tab when the PivotTable is selected. It consists of two sub-tabs:
Analyze Tab:
- Insert Slicer: Adds filter buttons for quick filtering.
- Field List: Show/hide the field list.
- Refresh: Updates data if the source table has changed.
Design Tab:
- Report Layout: Show in Tabular, Compact, or Outline form.
- Subtotals/Grand Totals: Turn them on/off or adjust their positions.
- Styles: Apply visual formatting for better presentation.
Using Slicers and Timelines
Slicers make it easy to filter data visually.
To Add a Slicer:
- Select your PivotTable.
- Go to Analyze > Insert Slicer.
- Choose the fields you want slicers for (e.g., Region, Product).
- Click OK.
You can now filter the PivotTable by clicking the buttons in the slicer.
To Add a Timeline (for date fields):
- Go to Analyze > Insert Timeline.
- Select a date field.
- Click OK and use the slider to filter data by time.
Grouping Data in PivotTables
You can group:
- Numbers (e.g., group sales in ranges)
- Dates (e.g., by month, quarter, year)
- Text (manual grouping)
To Group:
- Right-click on a value (e.g., a date).
- Choose Group.
- Select how you want to group (e.g., by months and years).
Sorting and Filtering
Sort Rows/Columns:
- Click the dropdown next to a field label.
- Sort A-Z, Z-A, or by value.
Use Filters:
- Add fields to the Filters area of the Field List.
- Use filter dropdowns to view data selectively.
Refreshing PivotTables
If your source data changes:
- Select the PivotTable.
- Go to Analyze > Refresh.
To refresh all PivotTables at once:
- Click the dropdown next to Refresh > Refresh All.
Multiple PivotTables from the Same Data
You can create multiple PivotTables using the same data source without copying the data.
- Use Insert > PivotTable multiple times using the same table.
- Use “Add this data to the Data Model” for more powerful analytics like relationships between tables.
Using Calculated Fields
You can add your own formulas to PivotTables using calculated fields.
Steps:
- Click the PivotTable.
- Go to Analyze > Fields, Items & Sets > Calculated Field.
- Name your field and enter a formula (e.g., =Sales*0.10 for 10% commission).
- Click Add then OK.
Exporting and Sharing Insights
Once your PivotTable is ready, you can:
- Copy and paste it into Microsoft Word 2016 Pro for reporting.
- Save as PDF or Excel workbook.
- Use charts (PivotCharts) for visual representation.
To insert a PivotChart:
- Select your PivotTable.
- Go to Analyze > PivotChart.
- Choose your desired chart type.
Troubleshooting Common PivotTable Issues
1. PivotTable Not Updating?
Try Refresh, and make sure the data source includes all rows/columns.
2. “Cannot Group That Selection” Error?
Check for blank cells or mixed data types in the field you’re trying to group.
3. Duplicate Fields?
PivotTables may automatically rename fields like “Sum of Sales2” if duplicated. Double-check your fields.
Final Thoughts
PivotTables in Excel 2016 Pro are a must-have tool for anyone working with data—whether you’re summarizing monthly sales, analyzing product performance, or compiling reports. With just a few clicks, you can transform a flat table into a dynamic summary view that makes sense of thousands of data points.
Plus, once you’ve built your summary, you can enhance and share your findings easily through Microsoft Word 2016 Pro for professional reports.
Mastering PivotTables boosts your productivity and empowers smarter decision-making.
FAQ: Creating PivotTables in Excel 2016 Pro
Q1. Can I create a PivotTable without formatting my data as a table?
Yes, but formatting as a table using Insert > Table makes it easier to manage dynamic ranges.
Q2. What’s the difference between PivotTables and regular tables?
PivotTables summarize and analyze data, while regular tables display data as-is.
Q3. Can I create PivotTables from data in multiple sheets?
Yes, but you’ll need to use the Data Model and create relationships between tables.
Q4. How do I remove a field from a PivotTable?
Uncheck the field in the PivotTable Field List or drag it out of the area box.
Q5. Can I use PivotTables in Excel Online?
Yes, with limited features. Full PivotTable functionality is available in desktop versions like Excel 2016 Pro.
Deutsch 
English