Excel 101: What Is a Pivot Table and How to Set It Up

A pivot table is a powerful Excel tool that lets you summarize and explore data interactively. It turns large, detailed datasets into meaningful insights by aggregating and grouping data dynamically. The actual job is to extract significance from raw numbers without writing complex formulas or manual calculations.

Many PMs underestimate how much value a simple pivot table can unlock. It is not just a spreadsheet feature — it is a foundational data skill that accelerates your analytical capability.

The stakes are clear. You will often face hundreds or thousands of rows of transaction or user data. Pivot tables let you slice that data by different dimensions and metrics in seconds — saving you hours of manual work and reducing errors.

Opening the door to pivot tables: how to set one up

Start with a clean, structured dataset in Excel where each column is a variable and each row is a record. For example, a table with columns like Day, People Dancing, People Eating, and so on.

The steps to create a pivot table are:

1. Select all the filled rows and columns in your Excel sheet.
2. In the Excel toolbar, click DATA → Pivot Table.
3. Excel will open a new sheet and create a blank pivot table.
4. When you click inside the pivot table, the Pivot Table Builder appears.

The Pivot Table Builder is an interactive interface that lets you build your summary by dragging fields into four areas:

• Report Filter: Creates a filter on top of the entire pivot table to show subsets of data.
• Column Labels: Sets which fields appear as columns in the table.
• Row Labels: Sets which fields appear as rows.
• Values: The numeric fields you want to aggregate (sum, count, average).

You can add or remove fields by dragging them into or out of these areas.

The anatomy of the pivot table builder

Understanding what each area does is critical to building the right summary:

For example, if you want to see "People Dancing" and "People Eating" counts by Day:

• Drag Day to Row Labels.
• Drag People Dancing and People Eating to Values.

Excel will automatically aggregate these values by day, showing sums by default.

If you see "Count of People Dancing" instead of "Sum", click the small “i” icon next to the field in the Values area and change the aggregation from Count to Sum. This ensures you're summing the numbers, not counting rows.

A concrete example: calculating ratios with pivot tables

Let's say you want to answer this question:

What is the ratio of total people dancing on Mondays to total people eating on Tuesdays?

Here’s how you do it:

1. Select all data rows in your original sheet.
2. Create a pivot table as described above.
3. Clear any pre-populated fields in the Pivot Table Builder.
4. Drag Day into the Row Labels area.
5. Drag People Dancing and People Eating into the Values area.
6. Make sure both are aggregated as sums.

The pivot table will show totals for each day:

Now, the ratio you want is 257 / 500 = 0.514.

This quick calculation is possible because the pivot table did all the grouping and summing for you instantly.

Why pivot tables matter for PMs

You will encounter datasets ranging from user activity logs to sales reports. Pivot tables let you:

• Explore data across different dimensions without writing code.
• Validate hypotheses quickly by slicing data by user segments, time periods, or geography.
• Prepare summary reports and dashboards that communicate impact clearly.

The alternative is manual aggregation — copying data into multiple sheets, writing formulas, or exporting to BI tools. Pivot tables are built into Excel, require no programming, and work offline — making them indispensable for rapid analysis.

Common pitfalls and how to avoid them

• Data formatting matters: Ensure your source data has no blank rows or columns and consistent column headers.
• Aggregation defaults: Excel defaults to counting text fields. Always check that numeric fields aggregate as sums or averages as needed.
• Removing fields: Drag fields out of the builder area to remove them; don’t try to delete the pivot table itself.
• Refreshing data: If you update the source data, right-click the pivot table and select Refresh to update the summary.
• Filter misuse: Avoid over-filtering in the Report Filter area, which can hide data unintentionally.

Putting it all together: a quick exercise

Try this hands-on:

1. Open an Excel sheet with data columns: Day, People Dancing, People Eating.
2. Select all rows and columns.
3. Create a pivot table.
4. Clear any pre-set fields.
5. Drag Day to Row Labels.
6. Drag People Dancing and People Eating to Values.
7. Change aggregation to Sum if needed.
8. Read off the totals and calculate the ratio of Monday dancers to Tuesday eaters.

This simple exercise teaches you how to turn raw data into actionable insight in minutes.

Beyond Excel: pivot tables in product analytics tools

While Excel pivot tables are foundational, many product analytics platforms like Amplitude or Mixpanel offer similar pivot or cohort analysis features. These allow you to analyze millions of events in real-time and segment users by behaviors.

However, the conceptual foundation is the same. Mastering Excel pivot tables builds your analytical mindset and prepares you to use these advanced tools effectively.

Test yourself: The pivot table setup challenge

Where to go next

• Master Excel basics and data cleaning: Excel Fundamentals
• Learn SQL for data querying: SQL for Product Managers
• Explore product analytics tools: Using Amplitude for Product Insights
• Understand metrics and KPIs: Metrics and KPIs
• Practice data-driven decision making: Data-Driven Product Management