Pivot Table Basics - Microsoft Excel for Data Analysis

In this training session from Pragmatic Works, Microsoft Certified Trainer Allison Gonzalez walks viewers through the fundamentals of using Pivot Tables in Microsoft Excel for data analysis. Whether you're new to Pivot Tables or a seasoned user, this tutorial offers foundational guidance and helpful tips to get the most out of Excel’s data summarization tool.

What is a Pivot Table?

A Pivot Table is a powerful Excel feature used to:

• Summarize and explore large datasets
• Analyze trends and patterns quickly
• Present key insights in a clean, visual format

Pivot Table Design Lifecycle

Allison outlines a consistent process for creating effective Pivot Tables:

1. Source the data: Identify the dataset to analyze.
2. Pivot cache: The data is stored here for fast interactions.
3. Design the layout: Decide on rows, columns, and calculations to show insights clearly.

What Makes a Good Dataset?

Before creating a Pivot Table, the dataset must meet certain quality standards:

• Consistent, descriptive column headers (e.g., Date, Hour, Minute)
• No blank cells or rows
• No subtotals or pre-pivoted data
• No combined multiple values in a single column cell

Creating the Pivot Table

With the data in good shape (in this example, nearly 70,000 rows), Allison demonstrates how to insert a Pivot Table:

1. Select any cell within the dataset.
2. Go to the Insert tab and click on Pivot Table.
3. Choose whether to place it in a new or existing worksheet.

Customizing the Pivot Table

After inserting the Pivot Table, several layout and field options are available:

• Change field panel layout for better visibility
• Drag fields into Rows, Columns, and Values sections

Allison demonstrates several configurations:

• Rows: Category
• Columns: Group (e.g., Alcohol, Food)
• Values: Units sold

This allows quick summarization by category and group, with instant aggregation across thousands of records.

Exploring Further Insights

The training emphasizes experimenting with layouts to uncover more insights:

• Dragging Group above Category in Rows for clearer grouping
• Adding Date to Columns to see trends by month
• Adding Item under Category to create hierarchy
• Expanding/collapsing rows for a better view of data granularity

Refreshing Pivot Table Data

A key point covered is how Pivot Tables rely on cached data. If the source data changes, the Pivot Table won’t reflect updates until refreshed. To refresh:

• Right-click anywhere inside the Pivot Table and select Refresh
• Or go to the Data tab and click Refresh All

Final Tips

Allison encourages users to keep experimenting and exploring different configurations to discover insights. She also recommends using the “on-demand” learning platform at Pragmatic Works for deeper Excel training and Power Platform topics.

By the end of the video, users will feel confident in building a Pivot Table, customizing its layout, and refreshing the data source — essential skills for any data-driven professional working in Excel.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Excel and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.