makekite
Software Masterclass

Microsoft Excel & Google Sheets: How to Create and Filter a Pivot Table

Pivot Tables are the most powerful data analysis tool in modern business, summarizing vast spreadsheets in seconds without writing a single formula.

The Four Pivot Table Field Quadrants FILTERS (Page slicers) e.g. [ Year = 2026 ] COLUMNS (Horizontal labels) e.g. [ Department ] ROWS (Vertical grouping) e.g. [ Sales Region / State ] VALUES (Aggregates & sums) e.g. [ Sum of Total Sales $ ] Drag your field categories into the 4 boxes to re-summarize data instantly.
Figure 1: The four Pivot Table field wells: Filters, Columns, Rows, and Values.

Imagine you have a spreadsheet with 10,000 transactions listing Date, Salesperson, Region, Product, and Price. If your manager asks: 'What was the total revenue per region, broken down by product category?', doing that manually would take hours.

A Pivot Table does this in four clicks without any formulas. It 'pivots' (rotates) raw tabular data into dynamic summaries.

How to Build Your First Pivot Table in 4 Steps

1
1. Ensure Clean Raw Data

Make sure your data has a single row of bold column headers (no merged cells, no blank header names, and no empty rows in between).

2
2. Insert the Pivot Table

Click anywhere inside your table. Go to the top ribbon ➔ click Insert ➔ click PivotTable. Leave 'New Worksheet' selected and click OK.

3
3. Drag Fields into Quadrants

A panel appears on the right. Drag your main category (e.g. 'Region') into the Rows box. Drag 'Sales $' into the Values box.

4
4. Format Values as Currency

Right-click any number in your new pivot table ➔ select Number Format (not Format Cells!) ➔ choose Currency ($) with 2 decimals.

Summarizing by Count, Average, or Percentage

By default, Excel sums numeric columns. But you can easily change the aggregation:

Click the small drop-down arrow next to your field in the Values box ➔ select Value Field Settings. Here you can choose Average, Count, or switch the display to '% of Grand Total' to see market share percentages instantly!

💡 Pro Tip: If you add new rows of data to your original table, your Pivot Table does NOT update automatically! Always click anywhere inside the Pivot Table and click 'Refresh' (or press Alt + F5) to load fresh numbers.

✨ Key Takeaways

  • Clean data is essential: every column must have a unique header name with zero merged cells.
  • Drag categories you want listed vertically into Rows; drag metrics to calculate into Values.
  • Change Value Field Settings to calculate Averages, Counts, or % of Total.
  • Remember to right-click and hit 'Refresh' whenever you add new raw rows of data.

Print business invoice template →

You may also like…

🔧 How to Set Up, Connect & Calibrate a Smart TV HDMI eARC soundbar wiring, optical audio, 4K HDR picture settings, Filmmaker mode, and Wi-… 🔧 How to Position and Configure a Home Wi-Fi Router & Mesh System 2.4 GHz vs 5 GHz frequencies, optimal node placement, channel congestion, wall penetration… 🔧 Microsoft Excel & Google Sheets: How to Use SUM, AVERAGE, and IF Formulas Formula syntax, cell referencing, calculating totals, averages, conditional IF statements,… 🔧 Microsoft Word & Google Docs: How to Build an Automatic Table of Contents Apply Heading 1, 2, 3 styles, generate automated page-numbered tables of contents, and upd… 🔧 Zoom & Microsoft Teams: How to Test Audio, Video, and Share Screen with Sound Microphone echo troubleshooting, testing sound before meetings, sharing computer audio for… 📋 How to help a child learn their times tables Short, regular practice and a few patterns make times tables stick.

View all Guides →

makekite.com