Thinkabara

📗 Excel & Google Sheets for Students · Lesson 5 of 7

Pivot tables

Google Classroom

Summarize hundreds of rows in a few clicks.

What a pivot table does

A pivot table summarizes a big table by groups: average satisfaction by class year, total spending by category, count of responses by major — without writing formulas.

Create one

  1. Click anywhere in your data (with headers)
  2. Excel: Insert > PivotTable. Google Sheets: Insert > Pivot table
  3. Drag a category to Rows (e.g., Major)
  4. Drag a number to Values (e.g., Score) and choose SUM, AVERAGE or COUNT
  5. Optionally drag another category to Columns (e.g., Class year)

Example

A survey of 300 students with columns Major, Year and Hours of sleep. Rows: Major; Columns: Year; Values: Average of Hours. In seconds you see whether engineering seniors sleep less than psychology freshmen.

Tips for clean results

Consistent labels“Bio”, “Biology” and “biology ” become 3 groups — clean first
RefreshPivot tables don’t update automatically in Excel — Refresh after changes
FiltersAdd a filter to show one semester or one group
ChartsInsert a PivotChart for an instant graph

Tip

💡 Use COUNT of a column to see how many responses each group has — small groups make averages unreliable.

✏️ Practice: your turn

  1. Download or create a small survey dataset (30+ rows)
  2. Make a pivot table: Rows = a category, Values = an average
  3. Add a second category as Columns
  4. Make a chart from the pivot table

✏️ Online spreadsheet: practice formulas without installing anything

🧠 Quick quiz

Answer all 4 questions correctly to mark this lesson as done.

1. A pivot table is for…
2. Where does the variable you average go?
3. “Bio” and “Biology” in the data will…
4. After changing source data in Excel, you…

0 of 4 correct

All lessons in Excel & Google Sheets for Students

  1. 🎓 1. A grade tracker with weighted averagesSUMPRODUCT, absolute references and “what do I need on the final?”
  2. 🧪 2. Cleaning and formatting lab dataTables, number formats, rounding, sorting and filtering
  3. 🔀 3. IF, COUNTIF and AVERAGEIFLabel results and summarize by condition
  4. 🔍 4. XLOOKUP and VLOOKUPPull matching data from another table
  5. 🧮 5. Pivot tablesSummarize hundreds of rows in a few clicks
  6. 📊 6. Charts for reportsPick the right chart, label it and keep it clean
  7. 💵 7. Project: a student budgetIncome, expenses, categories, a pivot and a chart