Excel Pivot Tables Tutorial for Beginners: Build Your First One Step by Step

You have a spreadsheet with a few thousand rows of sales, and someone asks a simple question: “Which region sold the most last quarter?” You could sort, filter and add up columns by hand. Or you could answer it in about 30 seconds with a pivot table.

A pivot table is Excel’s built-in tool for summarizing large lists. It groups rows by whatever you choose (region, product, month, salesperson) and calculates totals, counts or averages for each group. Nothing in your original data changes, so you can rearrange the summary as many times as you like.

This tutorial walks through building your first pivot table from a small sales table, then adds filters, date grouping, slicers and a chart. The steps use Excel for Windows and Microsoft 365, but the menus are very similar on Mac and Excel for the web.

What a pivot table does (and when to use one)

Think of a pivot table as a fast “group by and total” machine. You give it a flat list where each row is one record, and it builds a summary table you can reshape by dragging fields around.

Use a pivot table when you need to:

• Total sales, hours or costs by category
• Count how many orders, tickets or leads fall into each group
• Compare periods, such as this month versus last month
• Spot your top and bottom performers quickly
• Build a quick report you can refresh when new data arrives

Skip it when your data is tiny (10 rows doesn’t need a pivot) or when you need row-by-row calculations. For those, regular formulas are simpler.

Step 1: Set up your data the right way

Most pivot table problems start with messy source data. Microsoft’s own guidance is to organize data in columns with a single header row, give every column a unique, non-blank label, and avoid blank rows, double header rows and merged cells.

Here’s the sample sales table this tutorial uses:

Date	Region	Product	Salesperson	Units	Revenue
2026-01-05	East	Widget A	Maria	12	600
2026-01-09	West	Widget B	James	8	640
2026-02-14	East	Widget B	Maria	5	400
2026-03-02	South	Widget A	Priya	20	1,000
2026-04-18	West	Widget C	James	3	450

A few rules to follow before you start:

• One record per row. Each sale gets its own row.
• One type of data per column. Don’t mix dates and text in the Date column, or numbers and notes in Revenue.
• No subtotal rows. The pivot table will calculate totals for you.
• Format it as a table. Click inside the data and press Ctrl+T (or choose Insert > Table). An Excel table expands automatically, so new rows are picked up the next time you refresh.

Key takeaway: Clean, flat data with one header row is most of the work. If your pivot table looks wrong, check the source data before you blame the pivot.

Steps 2 to 4: Build your first pivot table

Step 2: Insert the pivot table

With your data formatted as a table:

1. Click any cell inside the table.
2. Go to Insert > PivotTable and choose From Table/Range.
3. Confirm the table name in the dialog box.
4. Choose New Worksheet (recommended for beginners) and select OK.

Excel opens a blank pivot table on a new sheet and shows the PivotTable Fields pane on the right. The top half lists your column headers. The bottom half has 4 areas: Filters, Columns, Rows and Values.

Not sure where to begin? Try Insert > Recommended PivotTables. Excel suggests a few layouts based on your data, and you can pick one as a starting point and adjust it.

Step 3: Drag fields into Rows, Columns and Values

This is where the pivot table comes alive. Answer the question “How much revenue did each region bring in?”:

1. Drag Region into the Rows area.
2. Drag Revenue into the Values area.

You now have a list of regions with a total revenue for each, plus a grand total. That’s a complete pivot table.

Now add a second dimension. Drag Product into Columns. The summary becomes a grid with regions down the side and products across the top, so you can see which product sells best in which region.

Here’s what each area does:

Area	What it does	Example field
Rows	Creates one row per unique item	Region, Salesperson
Columns	Creates one column per unique item	Product, Month
Values	Calculates a number for each row/column combination	Revenue, Units
Filters	Filters the whole pivot table by a field	Year, Sales channel

If you tick a field’s checkbox instead of dragging it, Excel guesses where it belongs. Text fields usually go to Rows, and numbers go to Values.

Step 4: Change how values are calculated

By default, Excel sums numeric fields and counts text fields. That’s often what you want, but not always.

To change it, click the dropdown next to the field in the Values area (for example, “Sum of Revenue”) and choose Value Field Settings. From there you can:

• Summarize Values By: switch between Sum, Count, Average, Max, Min and more.
• Show Values As: display numbers as a percentage of the grand total, a percentage of the row or column, a running total or a difference from a previous item.

A useful trick: drag Revenue into Values twice. Leave the first as a Sum, and set the second to Show Values As > % of Grand Total. Now each region shows both dollars and its share of total sales.

While you’re in Value Field Settings, click Number Format and pick Currency so the totals are easy to read. Microsoft also suggests waiting until your layout is final before renaming fields, because changing the calculation changes the field name.

Step 5: Filter and sort your results

Pivot tables give you several ways to focus on what matters:

• Filter area: Drag a field such as Salesperson into Filters. A dropdown appears above the pivot table so you can show one person’s sales at a time.
• Row and column filters: Click the dropdown arrow on the Row Labels header to tick or untick items, or use Value Filters > Top 10 to show only your top performers.
• Sorting: Right-click any value and choose Sort > Sort Largest to Smallest to rank regions or products by revenue.

Add slicers for click-to-filter buttons

Slicers are visual filter buttons that make a pivot table feel like a mini dashboard. Click inside the pivot table, go to Insert > Slicer (or PivotTable Analyze > Insert Slicer), and pick the fields you want, such as Region and Product.

Each slicer appears as a panel of buttons. Click a button to filter, hold Ctrl to pick several, and use the clear-filter icon to reset. A slicer can control more than one pivot table, as long as they share the same data source. That makes slicers handy for simple reports other people will use.

Step 6: Group dates and numbers

Raw dates make a messy pivot table: one row per day. Grouping fixes that.

1. Drag Date into Rows.
2. Right-click any date in the pivot table and choose Group.
3. In the Grouping box, select Months and Quarters (and Years if your data spans more than one year).
4. Select OK.

Your daily rows collapse into a clean monthly or quarterly view. In recent versions, Excel often groups dates automatically when you add a date field.

You can group numbers too. Right-click a numeric field in Rows (such as Units), choose Group, and set a start, end and interval, for example bands of 10 units. You can also group text items by hand: select several items with Ctrl, right-click and choose Group, then rename the group (for example, combining “East” and “South” into “Eastern territory”). To undo any group, right-click it and choose Ungroup.

Steps 7 and 8: Chart it and keep it current

Step 7: Turn it into a PivotChart

Numbers are great, but a chart tells the story faster. To add one:

1. Click any cell in your pivot table.
2. Go to Insert (or PivotTable Analyze) and choose PivotChart.
3. Pick a chart type and select OK.

The chart is linked to the pivot table, so filters and slicers update both. A column chart works well for comparing regions, and a line chart suits monthly trends. If you’re unsure which type to pick, our guide on how to choose the right chart walks through it by the question you’re answering.

Step 8: Refresh when your data changes

Pivot tables don’t update on their own when you edit the source data. After adding new rows:

• Right-click the pivot table and choose Refresh, or
• Go to PivotTable Analyze > Refresh > Refresh All to update every pivot table in the workbook.

Because your source is an Excel table, new rows are included automatically. If you used a plain range, you’d need to update the source range with Change Data Source.

Common pivot table mistakes and quick fixes

Problem	Likely cause	Fix
Revenue shows “Count of” instead of “Sum of”	Some cells contain text or blanks	Clean the column so it holds only numbers
New data doesn’t appear	Source is a fixed range, or you didn’t refresh	Use an Excel table and refresh
Dates won’t group	Some dates are stored as text	Convert the column to real dates
Duplicate-looking items (“East” and “East “)	Extra spaces or typos	Use TRIM or Find and Replace in the source
“PivotTable field name is not valid” error	A header cell is blank	Give every column a unique header

Where to start this week

You’ll learn pivot tables fastest by using your own data. Here’s a simple plan:

1. Pick one real question your boss or customers ask often, such as sales by month or tickets by category.
2. Export the raw data from your POS, CRM or accounting tool as a CSV and open it in Excel.
3. Clean and format it as a table using the rules in Step 1.
4. Build one pivot table that answers the question, then add a slicer and a PivotChart.
5. Save it as a template. Next month, paste in fresh data and hit Refresh All.

Once you’re comfortable, look at Power Query for cleaning messy exports and at Excel’s AI features for suggesting analyses. Our guide to using AI in Excel and Google Sheets covers those tools. Weighing up which spreadsheet to standardize on? See Google Sheets vs Excel. And if you’d like to turn spreadsheet skills into a career, read how to become a data analyst.

Conclusion

Pivot tables are one of the highest-value skills you can learn in Excel. With clean data and 4 drag-and-drop areas, you can answer most “how much, how many, which one” questions in seconds, and refresh the answer when new data arrives. Start with one real report, add grouping and a slicer, and you’ll soon wonder how you managed without them. For more small business reporting ideas, see data analytics for small business.

Sources

• Microsoft Support: Create a PivotTable to analyze worksheet data — insert steps, field areas, Value Field Settings, refresh and data preparation rules
• Microsoft Support: Group or ungroup data in a PivotTable — grouping dates, numbers and selected items
• Microsoft Support: Use slicers to filter data — inserting slicers and connecting them to pivot tables
• Microsoft Support: Create a PivotChart — creating charts linked to pivot tables
• Microsoft Support: Excel specifications and limits — worksheet and PivotTable limits