Excel & Spreadsheets · Lesson 5 of 6
Pivot Tables and Data Analysis
A pivot table can answer in 10 seconds what would take 20 formulas.
After This Lesson, You Will Be Able To
Create a pivot table from a dataset and use it to answer 3 specific analytical questions — grouping, summarizing, and filtering data without formulas.
What a Pivot Table Actually Does
'Show me total revenue by client, for Q1, sorted highest to lowest.' That's a pivot table. What would take 10 SUMIF formulas takes 30 seconds of dragging.
When to Use a Pivot Table vs Formulas
Use a pivot table when
You want to summarize data by a category (by client, by month, by product). You want to explore your data from multiple angles. You're doing one-time analysis and don't need it to update automatically.
Use formulas when
You want the summary to update automatically as you add data. You need the output to feed into another calculation. You have a specific, fixed summary that will always be the same.
How to Create a Pivot Table
Google Sheets
Select your data range (including the header row). Click Insert → Pivot Table. Drag fields into Rows, Columns, Values, and Filters. Change the Value summarization (Sum, Count, Average) by clicking the field in the Values section.
Excel
Select your data range. Click Insert → PivotTable. The interface is the same: drag fields into Rows, Columns, Values, Filters. Refresh the pivot table after adding new data by right-clicking → Refresh.
The most useful pivot table configurations
Revenue by client (Rows: client name, Values: SUM of amount). Revenue by month (Rows: month/year, Values: SUM of amount). Count of tasks by status (Rows: status, Values: COUNT of task name). Top clients by revenue (apply filter or sort).
Exercise
~10 minutes · ChatGPT or Claude
Prompt to use
I have a spreadsheet with this data structure: [describe your columns — e.g., Date, Client Name, Project Type, Invoice Amount, Payment Status]. I want to use a pivot table to answer these questions: [list 2-3 specific questions — e.g., 'Which client has paid me the most this year?' 'How much did I earn per month in the first half of this year?' 'How many projects are in each status?']. I'm using [Google Sheets / Excel]. For each question: 1) Tell me exactly how to set up the pivot table (what to put in Rows, Values, Columns, Filters), 2) Any sorting or filtering to apply to get the clearest answer, 3) Whether a pivot chart would help visualize this answer.
Most people who work with data every day don't know how to use a pivot table. Which means most data-related questions get answered with hours of manual work instead of 30 seconds of dragging. What question about your data has taken you the longest to answer manually?
Key Takeaways
A pivot table summarizes your data interactively — drag fields instead of writing formulas.
Use pivot tables for exploratory analysis. Use formulas for automated, always-updating summaries.
The most powerful pivot table configurations: by category (client, product, status) and by time period (month, quarter).
Refresh pivot tables after adding new data. They don't update automatically like formulas.
Create a pivot table that answers your most important data question.
Open your spreadsheet. Select all your data including the header row. Insert a pivot table. Drag 'Client Name' (or equivalent) into Rows and 'Invoice Amount' into Values (set to Sum). Sort by the value column. You just created a ranked list of your clients by revenue. Try 2 more configurations: same thing by month, and a count by status. That's 3 different answers from the same data in under 5 minutes.
Next Lesson
Automating Spreadsheet Tasks
Excel & Spreadsheets · Lesson 6 of 6 · 10 min