Pivot Tables Explained With One Real Example

Pivot tables are one of the most useful features in Microsoft Excel and Google Sheets, yet many people avoid them because they look more complicated than they really are.

The easiest way to understand a pivot table is to stop thinking of it as a formula. A pivot table is a reporting tool that takes a large list of data and quickly answers questions such as:

• How much did each salesperson sell?
• Which product generated the most revenue?
• What were the monthly totals?
• Which region performed best?
• How many orders were completed?

Instead of manually writing dozens of formulas, you can drag fields into a few boxes and let the spreadsheet group and calculate the information for you.

This guide explains pivot tables with one practical sales example, showing exactly how the source data is structured, how the pivot table is created and how each section works.

What Is a Pivot Table?

A pivot table summarises a larger dataset without changing the original information.

It can:

• Add values
• Count records
• Calculate averages
• Find minimum and maximum values
• Group dates by month, quarter or year
• Compare categories
• Filter results
• Display percentages
• Rearrange reports instantly

The word “pivot” refers to the way you can rotate or reorganise the report by moving fields between rows, columns, values and filters.

For example, the same sales data can be summarised by:

• Salesperson
• Product
• Region
• Month
• Customer
• Any combination of these fields

The original table stays unchanged.

The Real Example: A Small Sales Dataset

Imagine a business has the following order data:

Looking at the table, you could answer simple questions by reading each row. However, this becomes much harder when the list contains thousands of transactions.

Suppose management asks:

How much revenue did each salesperson generate for each product?

This is an ideal question for a pivot table.

The Answer We Want

The finished pivot table would look like this:

This report immediately shows that:

• Chloe generated the most total revenue.
• Laptops produced the most revenue overall.
• Ben generated the most monitor revenue.
• Keyboard sales were much lower than the other categories.

Creating this report manually would require several SUMIFS formulas. A pivot table can build it in seconds.

Step 1: Prepare the Source Data

A pivot table is only as reliable as the source data.

Your source should be arranged as a simple rectangular table.

Each Column Needs a Heading

Good headings include:

• Date
• Salesperson
• Region
• Product
• Quantity
• Revenue

Avoid vague headings such as:

• Info
• Details
• Total 1
• Miscellaneous

Each Row Should Represent One Record

In this example, each row represents one sale.

Do not place several transactions inside one row.

Avoid Blank Rows and Columns

Blank rows can cause Excel or Google Sheets to detect only part of the dataset.

Do Not Use Merged Cells

Merged cells make data harder to analyse and can prevent pivot tables from working correctly.

Keep Data Types Consistent

A Revenue column should contain numbers, not a mixture of:

• 1600
• £800
• N/A
• Eight hundred

Currency symbols should normally be applied through number formatting rather than typed into the cell as text.

Step 2: Convert the Data Into an Excel Table

This step is not mandatory, but it is strongly recommended in Excel.

Click anywhere inside the dataset and press:

Ctrl + T

Confirm that My table has headers is selected.

An Excel Table provides several benefits:

• New rows are included more easily.
• Formatting remains consistent.
• Filter buttons are added.
• Formulas automatically copy down.
• The pivot source can expand with the table.

Give the table a useful name such as:

SalesData

You can change the name from the Table Design tab.

Step 3: Insert the Pivot Table

In Excel:

1. Click anywhere inside the source table.
2. Open the Insert tab.
3. Select PivotTable.
4. Confirm the table or range.
5. Choose New Worksheet.
6. Select OK.

Excel creates an empty pivot table and displays the PivotTable Fields panel.

In Google Sheets:

1. Select the data.
2. Open Insert.
3. Select Pivot table.
4. Choose a new or existing sheet.
5. Select Create.

The terminology differs slightly, but the core idea is the same.

The Four Pivot Table Areas

Most pivot tables use four main areas:

• Rows
• Columns
• Values
• Filters

Understanding these four areas is the key to understanding pivot tables.

Rows

The Rows area controls the labels shown vertically down the left side.

For our example, place:

Salesperson

in Rows.

The report will now list:

• Amy
• Ben
• Chloe

Columns

The Columns area controls the headings displayed across the top.

Place:

Product

in Columns.

The report will now contain:

• Keyboard
• Laptop
• Monitor

Values

The Values area contains the number being calculated.

Place:

Revenue

in Values.

Excel should automatically use:

Sum of Revenue

The pivot table now adds all revenue for each salesperson and product combination.

Filters

The Filters area lets you filter the entire report using another field.

For example, place:

Region

in Filters.

You could then display:

• All regions
• North only
• South only
• East only

In this example, each salesperson belongs to one region, so the filter is not especially revealing. In a larger business where salespeople work across regions, it would be much more useful.

The Exact Field Layout

To create our example report, use:

That is the complete setup.

You have not written a single formula.

Why the Revenue Field Might Show a Count

Sometimes Excel places a numerical-looking field into Values but displays:

Count of Revenue

instead of:

Sum of Revenue

This usually means Excel has detected one or more cells as text.

Possible causes include:

• Currency symbols typed directly into cells
• Spaces before or after numbers
• Empty strings created by formulas
• Words such as N/A
• Numbers imported as text

To correct the summary calculation:

1. Open the dropdown beside the field in Values.
2. Select Value Field Settings.
3. Choose Sum.
4. Select OK.

If Sum is unavailable or produces unexpected results, clean the original Revenue column first.

Formatting the Numbers as Currency

The pivot table may display values as ordinary numbers.

To apply currency formatting properly:

1. Right-click any value in the pivot table.
2. Choose Value Field Settings.
3. Select Number Format.
4. Choose Currency or Accounting.
5. Select the pound symbol.
6. Choose the required decimal places.
7. Select OK.

Use the Number Format option within the pivot field settings rather than formatting random cells individually. This helps the formatting remain consistent after refreshes.

What Happens When You Move the Fields?

The power of a pivot table becomes clearer when you rearrange it.

Put Product in Rows and Salesperson in Columns

The report now lists products vertically and salespeople horizontally.

The numbers remain the same, but the presentation changes.

Put Region Above Salesperson in Rows

The report becomes hierarchical:

East

    Chloe

North

    Amy

South

    Ben

In a larger dataset, this would group all salespeople beneath their regions.

Remove Product From Columns

The report now shows total revenue per salesperson only:

Replace Revenue With Quantity

The report displays the number of units sold rather than money earned.

You are not rebuilding the source data. You are simply asking it a different question.

How to Show Revenue by Month

Suppose management now asks:

How much revenue was generated each month?

Use:

The pivot initially may display each individual date.

To group the dates in Excel:

1. Right-click any date in the pivot table.
2. Choose Group.
3. Select Months.
4. Select Years as well when the dataset covers more than one year.
5. Select OK.

The result would be:

This is one of the most useful pivot table features for sales, expenses and reporting.

Why You Should Group by Year and Month

When your data covers several years, grouping by Month alone can combine all Januaries together.

For example, January 2025 and January 2026 could appear as one total.

Select both:

• Years
• Months

The report then displays the correct time hierarchy.

Adding More Than One Value

A pivot table can calculate several measures at once.

For example, add both:

• Revenue
• Quantity

to Values.

The report can then show how much money was generated and how many units were sold.

You might discover that one product sells many units but produces relatively little revenue.

Counting Orders

Suppose you want to know how many transactions each salesperson completed.

Drag a field such as Salesperson, Date or Product into Values and change the calculation to Count.

The report might show:

In a real dataset, you would ideally count a unique Order ID rather than a general field.

Calculating Average Order Value

Place Revenue in Values and change the summary type from Sum to Average.

The result would show the average transaction value for each salesperson.

For the example:

This answers a different question from total revenue.

A person can have the highest average sale without having the highest overall sales total.

Sum, Count and Average Explained

Sum

Adds all numerical values.

Use it for:

• Revenue
• Costs
• Profit
• Quantity
• Hours

Count

Counts how many records contain a value.

Use it for:

• Number of orders
• Number of customers
• Number of support tickets
• Number of transactions

Average

Calculates the mean value.

Use it for:

• Average sale value
• Average response time
• Average rating
• Average cost
• Average hours worked

Other available calculations may include:

• Maximum
• Minimum
• Product
• Standard deviation
• Variance

Showing Values as Percentages

Pivot tables can display values as percentages without changing the source data.

For example, you could show each salesperson’s share of total revenue.

Right-click a value and choose:

Show Values As

Then select:

% of Grand Total

The results would be approximately:

Other useful options include:

• Percentage of row total
• Percentage of column total
• Difference from
• Percentage difference from
• Running total
• Rank largest to smallest

Sorting the Pivot Table

To show the highest-performing salesperson first:

1. Right-click a value in the Grand Total column.
2. Choose Sort.
3. Select Largest to Smallest.

The order becomes:

1. Chloe
2. Ben
3. Amy

This makes the report easier to interpret.

Filtering the Results

Pivot tables can filter data in several ways.

Report Filter

Place a field in the Filters area.

For example:

Region

Row or Column Filters

Use the dropdown beside Row Labels or Column Labels.

Label Filters

Filter categories based on names.

Examples:

• Begins with
• Contains
• Does not equal

Value Filters

Filter categories based on calculated results.

For example:

• Revenue greater than £3,000
• Top three salespeople
• Products below £1,000

Using Slicers

Slicers are visual filter buttons.

In Excel:

1. Click the pivot table.
2. Open PivotTable Analyze.
3. Select Insert Slicer.
4. Choose fields such as Region or Product.
5. Select OK.

The slicer displays clickable buttons.

A user can select:

• Laptop
• Monitor
• Keyboard

and the pivot table updates instantly.

Slicers are particularly useful for dashboards and reports shared with non-technical users.

Using a Timeline

A timeline is a visual filter designed for dates.

In Excel:

1. Click inside the pivot table.
2. Open PivotTable Analyze.
3. Select Insert Timeline.
4. Choose the Date field.
5. Select OK.

You can then filter by:

• Year
• Quarter
• Month
• Day

A timeline is often clearer than a normal date dropdown.

Refreshing the Pivot Table

A pivot table does not always update automatically when the source data changes.

After adding new transactions:

1. Right-click inside the pivot table.
2. Choose Refresh.

Or use:

Data > Refresh All

When the source is an Excel Table, new rows are more likely to be included correctly.

When the source is a fixed range such as:

A1:F500

rows added below that range may be excluded.

This is one of the main reasons to convert source data into an Excel Table first.

Refreshing When the File Opens

In Excel, you can configure the pivot table to refresh automatically when the workbook opens:

1. Right-click the pivot table.
2. Select PivotTable Options.
3. Open the Data tab.
4. Select Refresh data when opening the file.
5. Select OK.

This is useful for reports connected to regularly updated datasets.

Changing the Data Source

When the pivot table is based on the wrong range:

1. Click inside the pivot table.
2. Open PivotTable Analyze.
3. Select Change Data Source.
4. Choose the correct range or table.
5. Select OK.
6. Refresh the report.

Using a named Excel Table reduces the need to change the range manually.

Pivot Tables Do Not Change the Original Data

This is important.

Dragging fields, grouping dates or filtering the pivot table does not rearrange or delete the source data.

The pivot table is a separate summary.

You can create several pivot tables from the same dataset, with each answering a different question.

For example:

• Revenue by salesperson
• Revenue by product
• Monthly sales trend
• Units sold by region
• Average order value
• Top-performing products

Why Source Data Structure Matters

Pivot tables work best with data in a database-style format.

Each column should represent one type of information, and each row should represent one record.

Good structure:

Poor structure:

The second example contains headings, blank cells and subtotals inside the dataset.

Pivot tables should receive raw records, not an existing visual report.

Do Not Add Manual Totals to the Source Data

Avoid including rows such as:

January Total

February Total

Grand Total

The pivot table will calculate totals itself.

If manual totals are included, the pivot may add them again and produce inflated results.

Use One Header Row

The dataset should have a single row of headings.

Avoid:

The first decorative heading may be interpreted as part of the data range.

Keep titles outside the source table.

Avoid Separate Columns for Each Month

A common spreadsheet layout looks like this:

This is already a summary and is less flexible.

A better source format is:

This format lets the pivot table group, filter and compare the information more easily.

Handling Blank Cells

Blank fields can appear as:

(blank)

inside a pivot table.

You can:

• Correct missing data in the source
• Filter out the blank item
• Replace blanks with a valid category such as Unknown
• Configure empty value cells to display zero

Do not replace missing categories with zero when zero is not a meaningful label.

Handling Errors

Source errors such as:

#N/A

#VALUE!

#DIV/0!

can affect the pivot table.

Correct the underlying formulas where possible.

You can also configure the pivot table to display a chosen value for errors, but this may hide a genuine data-quality problem.

Pivot Tables and Calculated Fields

A calculated field creates a new calculation inside the pivot table.

For example, when the data contains:

• Revenue
• Cost

you could create:

Profit = Revenue - Cost

However, calculated fields have limitations and may not behave like ordinary spreadsheet formulas.

In many cases, it is clearer to add a proper Profit column to the source table before building the pivot.

Pivot Tables vs. SUMIFS

Both tools can summarise data.

Use a Pivot Table When:

• You want to explore the data quickly.
• The report layout may change.
• You need interactive filters.
• You want to group dates.
• You want several summaries from one dataset.
• Users need to rearrange the report.

Use SUMIFS When:

• The report must have a fixed layout.
• The summary needs to feed another formula.
• You require precise control over individual cells.
• The output is part of a structured template.
• You need results to update directly without refreshing.

Many professional spreadsheets use both.

Pivot Tables vs. Ordinary Filters

A filter hides or shows records in the original table.

A pivot table groups and calculates them.

For example, filtering for Amy shows her four individual transactions.

A pivot table can show Amy’s total revenue as one number.

Pivot Tables vs. Charts

A pivot table summarises information numerically.

A chart displays it visually.

You can create a PivotChart based on a pivot table.

For example, turn the salesperson totals into a column chart.

In Excel:

1. Click the pivot table.
2. Open PivotTable Analyze.
3. Choose PivotChart.
4. Select a chart type.
5. Select OK.

The chart changes as the pivot table is filtered.

The One Real Example, Explained in Plain English

Our field setup was:

• Salesperson in Rows
• Product in Columns
• Revenue in Values

This tells Excel:

Create one row for every salesperson, one column for every product and add together the Revenue values where those categories intersect.

For the cell where Amy and Monitor meet, Excel finds:

• £600 from January
• £400 from March

It then calculates:

£600 + £400 = £1,000

For Chloe and Laptop, it finds:

• £800 from January
• £2,400 from March

The result is:

£3,200

That is all a pivot table is doing. It is grouping matching records and applying a calculation.

Questions the Same Pivot Table Data Can Answer

Using the same 12 source rows, you can answer:

• Who generated the most revenue?
• Which product earned the most?
• What was the total revenue each month?
• How many units did each person sell?
• What was the average order size?
• Which region performed best?
• What percentage of total revenue came from laptops?
• How many transactions involved monitors?
• Which month had the strongest sales?

You do not need separate datasets for each question.

Common Pivot Table Mistakes

Using Poorly Structured Source Data

Blank rows, merged cells and multiple header lines create problems.

Forgetting to Refresh

New data may not appear until the pivot is refreshed.

Using Count Instead of Sum

This usually means the source numbers are stored as text.

Including Manual Total Rows

Totals may be counted twice.

Using a Fixed Data Range

New rows may fall outside the source.

Formatting Individual Cells

Formatting may disappear after the layout changes. Use Value Field Settings.

Grouping Months Without Years

Data from different years may be combined.

Editing Pivot Results Directly

Pivot cells are generated from source data and cannot usually be overwritten like ordinary cells.

Treating Blanks as Zero

Missing information and a genuine zero are not always the same thing.

Building a Pivot From Another Summary

Use the raw transaction data where possible.

A Beginner’s Pivot Table Checklist

Before creating the pivot:

• Use one row per transaction.
• Add clear column headings.
• Remove blank rows.
• Avoid merged cells.
• Remove manual subtotals.
• Check numerical columns contain numbers.
• Correct missing or invalid dates.
• Convert the range into an Excel Table.
• Give the table a useful name.

When creating the report:

• Put categories in Rows.
• Put comparison categories in Columns.
• Put numbers in Values.
• Put optional report controls in Filters.
• Check whether Values use Sum, Count or Average.
• Apply number formatting.
• Sort results where useful.
• Add slicers for easier filtering.
• Refresh after source changes.

Useful Pivot Table Ideas for Businesses

Pivot tables can summarise:

• Sales by customer
• Revenue by month
• Profit by product
• Support tickets by category
• Staff hours by department
• Expenses by supplier
• Stock movement by item
• Website leads by source
• Project costs by manager
• Invoices by status
• Calls by technician
• Customer satisfaction ratings

The underlying principle remains the same: one clean list of records can produce many reports.

Final Thoughts

Pivot tables become much easier once you understand the four areas:

• Rows create vertical categories.
• Columns create horizontal categories.
• Values perform the calculation.
• Filters control which records are included.

In our sales example, placing Salesperson in Rows, Product in Columns and Revenue in Values produced a complete sales comparison without any formulas.

The most important part is not the pivot table itself. It is preparing clean source data with one row per record and one heading per column.

Once the source is organised properly, you can create reports, change layouts, group dates and compare totals in seconds.

That is why pivot tables are worth learning early: they turn a long spreadsheet into useful information without requiring a complicated formula in every cell.

Need Help With Excel, Reporting or Business Data?

Poorly structured spreadsheets can make reporting slow, unreliable and difficult to maintain.

Hamilton Group can help businesses organise data, improve Excel workflows, create clearer reporting systems and troubleshoot computers, Microsoft 365 and business technology.

Visit hgmssp.com, call 0330 043 0069, or book a meeting with one of our experts.