Pivot Table Tutorial for Beginners: Summarise Any Sales Sheet in 10 Steps

A pivot table summarises a long Excel sheet by grouping rows and adding up, counting or averaging values, all without a single formula. In this pivot table tutorial you will turn a raw sales sheet into a summary of sales by region, month and product in 10 steps, then add a slicer and a percentage view. It works in Microsoft 365, Excel 2021 and most older versions.

Pivot tables are usually the first “advanced” skill people learn in Excel, and they are taught early in our Advanced Excel course because almost every report starts with one.

Quick answer: Clean your data into one table with a single header row, convert it to an Excel Table (Ctrl+T), then choose Insert > PivotTable. Drag Region to Rows, Month to Columns and Sales to Values. Group dates by month, set Values to Sum, format the numbers, and add a slicer for filtering. Refresh whenever the data changes.

The sample data for this pivot table tutorial

Imagine a sheet of sales orders with one row per order and these columns:

Order Date	Region	Salesperson	Product	Units	Sales
03-04-2026	East	Riya	Notebook	12	1,440
05-04-2026	West	Arjun	Pen set	30	2,100
07-04-2026	East	Riya	Planner	5	1,250

Your own sheet can have hundreds or thousands of rows. The question we will answer: which region sold the most each month, and which products drove it?

Before you start: three rules for pivot-ready data

Most pivot table problems are really data problems. Check these first.

1. One header row, every column named. No blank or merged header cells.
2. No blank rows or subtotal rows in the middle of the data.
3. One type per column. Dates are real dates, numbers are real numbers (not text with spaces), and nothing says “N/A” in the Sales column.

If your data needs more work, fix it in the source first. Once you do the same clean-up every month, Power Query can record those steps and repeat them for you.

How to make a pivot table in 10 steps

Step 1: Turn the data into an Excel Table

Click any cell in the data and press Ctrl+T, tick “My table has headers” and click OK. Rename it in Table Design (for example tblSales). A Table grows automatically when you add rows, so your pivot table will pick up new data on refresh.

Step 2: Insert the pivot table

With a cell in the table selected, go to Insert > PivotTable, confirm the source is tblSales, choose New Worksheet and click OK. You will see an empty pivot area on the left and the PivotTable Fields pane on the right.

Step 3: Add Region to Rows

Drag Region into the Rows box. Each region now appears once, however many orders it has.

Step 4: Add Sales to Values

Drag Sales into Values. You should see “Sum of Sales” for each region and a Grand Total. If it says “Count of Sales”, jump to the troubleshooting section below.

Step 5: Add Order Date to Columns and group by month

Drag Order Date into Columns. Newer Excel versions group dates automatically; if not, right-click any date in the pivot, choose Group, and select Months and Years. Always include Years, or April 2025 and April 2026 will be added together.

Step 6: Format the numbers

Right-click a value, choose Value Field Settings > Number Format, and pick Number with a thousands separator and no decimals. Formatting through Value Field Settings keeps the format when the pivot refreshes; formatting cells directly does not.

Step 7: Rename the headings

Click the “Sum of Sales” cell and type Sales followed by a space (Excel will not let you reuse the exact field name). Change “Row Labels” to Region via Design > Report Layout > Show in Tabular Form, which also makes the pivot easier to read.

Step 8: Add Product as a second row level

Drag Product into Rows below Region. Now each region expands to show its products. Use the small +/− buttons to collapse and expand, or right-click and choose Sort > Largest to Smallest to rank products within each region.

Step 9: Show each region’s share with % of total

Drag Sales into Values a second time. Right-click the new column, choose Show Values As > % of Grand Total (or % of Row Total to see each month’s share within a region). You now have amount and share side by side, which is usually what a manager actually asks for.

Step 10: Add a slicer and refresh

Click inside the pivot, choose PivotTable Analyze > Insert Slicer, and tick Salesperson. Clicking a name filters the whole pivot. When new orders arrive, add them to the table and use PivotTable Analyze > Refresh (or Data > Refresh All).

Rows, Columns, Values and Filters: which field goes where?

Area	Put here	Example
Rows	Categories you want to list down the side	Region, Product
Columns	A few categories to compare across the top	Month, Quarter
Values	Numbers to add up, count or average	Sales, Units
Filters	A field to filter the whole report by	Year, Channel

A good rule: keep Columns short. Twelve months across the top is fine; three hundred product names is not.

Five pivot table views worth knowing

• Top 5 products: Product in Rows, Sales in Values, then Row Labels filter > Value Filters > Top 10 and change 10 to 5.
• Average order value: add Sales to Values and switch the summary to Average.
• Orders per salesperson: Salesperson in Rows, Order Date in Values summarised as Count.
• Month-on-month change: Show Values As % Difference From, base item (previous).
• Drill down to the rows: double-click any number and Excel opens a new sheet listing the orders behind it. This is the quickest way to check a figure that looks wrong.

Troubleshooting common pivot table problems

It counts instead of sums

The Sales column contains text or blanks somewhere. Find them (filter the column for non-numbers), fix them, refresh, then set Value Field Settings to Sum.

New rows are missing

Either you have not refreshed, or the pivot was built from a fixed range like A1:F500. Rebuild it from an Excel Table, as in step 1.

Dates will not group

At least one “date” is actually text. Use Data > Text to Columns or DATEVALUE to convert them, or let Power Query set the column type.

“(blank)” appears in the rows

The source has empty cells in that column. Fill them with a proper value such as “Unknown”, or filter “(blank)” out if those rows truly do not belong.

The layout breaks other formulas

Formulas that point at a pivot can break when its shape changes. Use GETPIVOTDATA, or better, keep pivot tables on their own sheet and build reports from them, not around them. Microsoft’s Excel help centre documents each pivot option in detail.

Where pivot tables fit in your Excel skills

Pivot tables pair naturally with lookup formulas. If you still join sheets with VLOOKUP before pivoting, our comparison of XLOOKUP vs VLOOKUP vs INDEX MATCH shows the cleaner options. The next steps are Power Query for automatic cleaning and Power Pivot for data models with several related tables, all covered in our guide to advanced Excel skills. When reports need to be shared and refreshed online, the same rows-columns-values thinking carries straight into Power BI.

Practice tasks

Try these with the same data before moving on:

• Sales by Product and Quarter, sorted largest to smallest
• Each salesperson’s share of their region’s sales
• Top 3 products in each region
• Units and Sales side by side, with an Average selling price you calculate next to the pivot
• A pivot chart with a slicer for Region

Learn it with Omebly

Omebly Academy’s Advanced Excel course covers pivot tables, Power Pivot data models and Power Query in month 2 of a live, mentor-led syllabus, with recordings for revision and weekly one-to-one reviews.

• Core (3 months, $699): formulas and dynamic arrays, pivot tables, Power Query and Power Pivot, dashboards, financial models and VBA macros, with two capstone projects.
• Professional (6 months, $1,199): adds advanced modelling, scenario analysis, Office Scripts and Power Automate, four capstones, mock interviews and portfolio review.
• Career (12 months, $1,999): adds SQL and Power BI for Excel users and a live client-style reporting project with the Omebly team.

You receive an Omebly certificate of completion with your projects listed. Placement assistance on the Career track means CV and LinkedIn help, mock interviews, portfolio review and referrals where possible; there is no job guarantee. A free first call checks your level and suggests a track.

Questions about where to start? Contact us or message us on WhatsApp.