How to Create a Pivot Table in Excel: Step-by-Step Guide for Beginners

shape
shape
shape
shape
shape
shape
shape
shape
How to Create a Pivot Table in Excel: Step-by-Step Guide for Beginners - featured image

A pivot table is the fastest way to summarize a large table of data in Excel. In a few clicks you can turn thousands of rows into a short report that shows totals by region, product, month or any other field, without writing a single formula. You can then rearrange it by dragging fields, which is why it is called a pivot. This guide shows how to prepare your data, how to insert a pivot table, how to use the four areas of the field list, and how to build a worked example with a small sales table that you can check by hand. It also covers counting and averages, percentages of the total, grouping dates, filters and slicers, refreshing, pivot charts, and the common problems with their fixes.

Updated November 2026

In this guide

  1. What is a pivot table?
  2. Prepare your data first
  3. The example data
  4. Step 1: insert the pivot table
  5. Step 2: understand the four areas
  6. Step 3: build the summary
  7. Step 4: add a second dimension
  8. Change the calculation: sum, count, average
  9. Show values as a percent of the total
  10. Group dates by month or year
  11. Filters and slicers
  12. Refresh the pivot table
  13. Create a pivot chart
  14. Sort and format the report
  15. Common problems and fixes
  16. Common mistakes
  17. Practice problems
  18. Quick recap

What is a pivot table?

A pivot table is an interactive summary of a data range. It reads the rows of your source data, groups them by the fields that you choose, and calculates a total, a count, an average or another figure for each group. The source data does not change, so you can try different views without any risk. Pivot tables are used for sales reports, budgets, survey results, inventory lists and attendance sheets.

Prepare your data first

A pivot table works best when the data is in a clean table. Use one header row, with a unique name for each column. Do not leave blank rows or blank columns inside the data, and do not merge cells. Each column should hold one kind of value, such as dates or numbers. Press Ctrl and T to turn the range into an Excel table, so the pivot table can grow when you add rows. Clean data saves most of the problems later on.

The example data

We use a small sales table with seven rows. The columns are Region, Product and Sales.

RegionProductSales
EastPens100
EastPens150
EastBooks200
WestPens120
WestBooks180
WestBooks220
EastBooks50

The total of all sales is 100 plus 150 plus 200 plus 120 plus 180 plus 220 plus 50, which equals 1,020.

Step 1: insert the pivot table

Click any cell in the data. Go to the Insert tab and choose PivotTable. Excel selects the whole table and offers to place the pivot table on a new worksheet, which is the best choice. Click OK. You now see an empty pivot table and the PivotTable Fields pane on the right.

Step 2: understand the four areas

The field list has four areas. Rows lists the groups down the side. Columns lists the groups across the top. Values holds the numbers that are calculated. Filters lets you limit the whole report to one group. Drag a field into an area, or tick its box and Excel will place it for you, with text fields in Rows and number fields in Values.

Step 3: build the summary

Drag Region to Rows and Sales to Values. Excel shows the sum of sales for each region. East has 100 plus 150 plus 200 plus 50, which is 500. West has 120 plus 180 plus 220, which is 520. The grand total is 1,020, which matches the total of the source data. This is a good habit: always check the grand total against the data.

RegionSum of Sales
East500
West520
Grand Total1,020

Step 4: add a second dimension

Now drag Product to Columns. The report becomes a cross table. East Pens is 250, East Books is 250, West Pens is 120 and West Books is 400. The product totals are 370 for Pens and 650 for Books, and they also add up to 1,020.

RegionBooksPensGrand Total
East250250500
West400120520
Grand Total6503701,020

You can swap Rows and Columns by dragging the fields, which is the pivot in the name.

Change the calculation: sum, count, average

Click the field in the Values area, choose Value Field Settings, and pick a different function. Count gives the number of rows, which is 7 for the whole table. Average gives 1,020 divided by 7, which is about 145.71. Max and Min give the largest and smallest values. You can also rename the field, for example from Sum of Sales to Total Sales, and set the number format so that the result shows commas.

Show values as a percent of the total

In Value Field Settings, open the Show Values As tab and choose % of Grand Total. The East region becomes 500 divided by 1,020, which is 49.02 percent, and West becomes 50.98 percent. By product, Books is 650 divided by 1,020, which is 63.73 percent, and Pens is 36.27 percent. This is useful for market share and for the mix of a budget. For the formula behind it, see our percentage of total guide.

Group dates by month or year

If your data has a date column, drag it to Rows. In recent versions Excel groups the dates automatically into years and months. If it does not, right click a date in the pivot table and choose Group, then select Months and Years. This gives a monthly sales report in seconds. It only works when the dates are real dates and not text, so convert text first with the ideas in our TEXT function guide if needed.

Filters and slicers

Drag a field to the Filters area to add a drop-down above the pivot table, for example to show only one region. A slicer is a friendlier version with buttons. Click inside the pivot table, go to PivotTable Analyze, choose Insert Slicer, and tick a field. Click a button to filter, and hold Ctrl to select several. Slicers make a report easy to use for people who do not know Excel well.

Refresh the pivot table

A pivot table does not update itself when the source data changes. After you edit or add data, right click the pivot table and choose Refresh, or press Alt and F5. If the new rows are outside the old range, change the source under PivotTable Analyze, Change Data Source, or use an Excel table so that the range grows. You can also set the file to refresh when it opens in the PivotTable options.

Create a pivot chart

Click inside the pivot table, go to PivotTable Analyze and choose PivotChart. Pick a column or bar chart, and it is linked to the pivot table, so it changes when you drag fields or use a filter. Keep the chart simple, with a clear title and no extra decoration, and avoid data labels if they clutter it. For chart basics see our bar graph guide.

Sort and format the report

To sort, right click a value and choose Sort, Largest to Smallest. To change the look, choose a style under PivotTable Design. You can turn off the grand totals, repeat the row labels, and show the report in tabular form to make it easier to copy. If you want to use a figure from the pivot table in a formula, GETPIVOTDATA does it, although a direct cell link may be simpler for small reports.

Common problems and fixes

If a number field is counted and not summed, there are probably text or blank cells in the column, so clean the column and refresh. If the new data is missing, refresh or extend the range. If dates will not group, some of them are text. If the pivot table shows the old name of a field, rename it in the pivot table. If you get an error that the field name is not valid, a header cell is empty. If the layout jumps when you refresh, turn off Autofit column widths on update.

Common mistakes

The first mistake is to leave blank rows in the source. The second is to use merged header cells. The third is to forget to refresh. The fourth is to type over a value in the pivot table, which is not allowed, so change the source instead. The fifth is to build a report without checking the grand total against the source. The sixth is to put the pivot table on the same sheet as the data and then overwrite it when the data grows.

Practice problems

Try these on the example. Problem 1: what is the total of West Books? It is 400, since 180 plus 220. Problem 2: what is the share of Pens in the total? It is 36.27 percent. Problem 3: how many rows are there for East? There are 4. Problem 4: what is the average sale of West? It is 520 divided by 3, which is 173.33. Problem 5: add a Month column to your own data and build a monthly pivot table.

Quick recap

Prepare a clean table, choose Insert, PivotTable, and drag fields to Rows, Columns, Values and Filters. Change the sum to a count or an average, show percentages of the total, group dates, add slicers and refresh after changes. Check the grand total against the source, and add a pivot chart for a clear picture.

Frequently asked questions

How do I create a pivot table in Excel?

Select your data, go to Insert, choose PivotTable and drag the fields into the areas.

Why is my pivot table not updating?

You need to refresh it with a right click or Alt and F5.

What are the four areas of a pivot table?

Rows, Columns, Values and Filters.

Can a pivot table calculate averages?

Yes, change the Value Field Settings from Sum to Average.

Do I need formulas to use a pivot table?

No, the summary is built by dragging fields.

Related guides and free tools

Leave a Reply

Your email address will not be published. Required fields are marked *