How to create an Excel pivot table: data analysis for beginners

A pivot table may sound complicated, but it is actually one of the most powerful and useful features in Excel. With a pivot table you can quickly summarize, analyze and understand large amounts of data, without having to write complicated formulas. In this article we explain step by step how to create your first pivot table.

What is a pivot table?

A pivot table is an interactive table that summarizes and groups your data. Say you have a list of all sales from the past year. With a pivot table you can see in just a few clicks:

  • How much revenue each salesperson generated
  • Which products sell best
  • In which months most sales took place
  • Combinations of these, such as which salesperson sold the most of which product

The great thing is that you can view the same data in different ways simply by dragging and clicking. Hence the name "pivot" table: you can pivot, or rotate, the data to gain other insights.

Preparation: getting your data ready

Before you create a pivot table, your data needs to meet a few conditions. This is important for a good result.

Rule 1: Columns must have headers

Every column must have a clear header in the first row. For example: Date, Salesperson, Product, Quantity, Amount. Without headers, Excel doesn't know how to group the data.

Rule 2: No empty rows or columns

Your data must be contiguous. Empty rows or columns in the middle of your table can cause problems. Remove them before you start.

Rule 3: Consistent data

Make sure the data in the same column is of the same type. A column of amounts should contain only numbers, not text such as "still unknown". A column of names must be spelled consistently: "John Johnson" and "J. Johnson" are treated as different people.

Rule 4: No merged cells

Merged cells don't work well with pivot tables. Unmerge any merged cells before you start.

Creating a pivot table step by step

Let's create a pivot table using a practical example. We'll use a sales list with the columns: Date, Salesperson, Region, Product and Amount.

Step 1: Select your data

Click anywhere in your data table. Excel usually selects the correct range automatically. You can also manually select the entire range, including the header row.

Step 2: Insert a pivot table

Go to the Insert tab on the ribbon at the top. Click PivotTable. A dialog box appears.

Step 3: Choose a location

Excel asks where you want to place the pivot table:

  • New Worksheet: the pivot table goes on a separate tab (recommended for beginners)
  • Existing Worksheet: you choose a location yourself

Choose New Worksheet and click OK.

Step 4: Build the pivot table

You now see an empty pivot table area and, on the right, the PivotTable Fields pane. It lists all the columns from your source table. Now the real work begins.

Understanding the fields pane

The fields pane has four areas you can drag fields into:

Filters

Fields you place here become filter options above the pivot table. Useful, for example, to show only the data from a specific year.

Columns

Fields here become column headers in your pivot table. Use this for categories you want to compare side by side.

Rows

Fields here become row labels. This is usually where you put the main breakdown of your analysis, such as product names or salesperson names.

Values

This is where you put the numbers you want to analyze, such as sales amounts or quantities. Excel calculates totals automatically.

Creating your first analysis

Let's create a simple analysis: total sales per salesperson.

Dragging the fields

  1. Drag the Salesperson field to the Rows area
  2. Drag the Amount field to the Values area

That's all. You now immediately see a table with all salespeople and their total sales amount. Excel has automatically added up all the amounts per salesperson.

Expanding the analysis

Do you also want to see which products each salesperson sold? Drag the Product field to Rows, below Salesperson. Now you see a breakdown by product for each salesperson.

Adding columns

Do you want to see the sales per region side by side? Drag Region to the Columns area. Now you have a cross table: salespeople in the rows, regions in the columns, and the sales amounts in the cells.

Customizing the pivot table

A pivot table is fully customizable. Here are the most important options.

Removing fields

Drag a field out of an area back to the field list, or uncheck it in the list at the top.

Changing the order

Drag fields up or down within an area to change the order. With multiple row fields, the order determines the hierarchy.

Changing the value calculation

By default, Excel adds up values with SUM. But you can also choose other calculations:

  1. Click the field in the Values area
  2. Choose Value Field Settings
  3. Select a different calculation: Count, Average, Min, Max, etc.

This is useful if, for example, you want to know how many transactions there were (Count) instead of the total amount (Sum).

Filtering and sorting

Pivot tables offer extensive filtering and sorting options.

Using the filter area

Drag a field to Filters to get a drop-down filter above the table. This lets you show, for example, only the data from 2024, or only the North region.

Row or column filters

Click the small arrow next to a row or column label to filter. You can select or deselect specific items.

Sorting

Right-click a value and choose Sort. You can sort from largest to smallest or the other way around. This way you can see right away who the best salesperson is.

Refreshing the pivot table

When you add new data to your source table, you need to refresh the pivot table:

  1. Click anywhere in the pivot table
  2. Go to the PivotTable Analyze tab
  3. Click Refresh

Or right-click in the pivot table and choose Refresh.

Watch out with new rows

If you add new rows at the bottom of your source table, they may fall outside the source range. Check this via PivotTable Analyze, then Change Data Source.

Tip: turn your source data into an Excel table (Ctrl+T). The range will then expand automatically when you add new data.

Formatting and presentation

With the right formatting, a pivot table can look professional.

Applying styles

Go to the Design tab when the pivot table is selected. Here you will find dozens of predesigned styles. Click a style to apply it right away.

Adjusting the number format

By default, values are shown without formatting. To add euro signs and separators:

  1. Right-click a value in the pivot table
  2. Choose Number Format
  3. Select Currency or Number with the options you want

Filling empty cells

Empty cells in a pivot table can be confusing. Via PivotTable Options, you can set empty cells to show a 0 or a dash.

Practical applications

Pivot tables are useful in many situations.

Analyzing a household budget

Track your expenses with date, category and amount. A pivot table then immediately shows how much you spend per month on groceries, energy or leisure.

Membership administration

For an association or club, you can analyze how many members each section has, when most sign-ups take place, or which membership fee categories are the most popular.

Sales analysis

For a small business, a pivot table is ideal for seeing which products are doing well, which customers order the most, and how revenue develops month by month.

Tips for beginners

Tip 1: Start simple

Start with one or two fields and build up slowly. That way you'll better understand what is happening.

Tip 2: Feel free to experiment

You can't break anything. Drag fields back and forth and try different combinations. The source data always stays unchanged.

Tip 3: Use the right mouse button

Right-clicking gives you access to many handy options, such as filtering, sorting and value field settings.

Tip 4: Create multiple pivot tables

You can create multiple pivot tables from the same source data. That way you can have different analyses side by side.

Frequently asked questions

Can I create a pivot table from data on multiple tabs?

Yes, but this requires using the Data Model or Power Pivot, which is more advanced. For beginners, it is easier to first combine all the data on one tab. You can copy and paste data, or use the VLOOKUP function to combine tables before you create the pivot table.

Why does Excel show text as Sum of Text?

When you drag a text field to Values, Excel cannot add it up. Instead, Excel counts the number of items (Count of Text Field). If you want to see counts, this is exactly what you need. If you want to add up amounts, make sure you drag the correct numeric field to Values.

How can I make the pivot table refresh automatically?

Go to PivotTable Options (right-click, then PivotTable Options) and check Refresh data when opening the file. That way the pivot table is always up to date when you open the file. Note that this only works if the source data is in the same file.

Can I create a chart from my pivot table?

Certainly, this is called a PivotChart. Click in the pivot table, go to Insert and choose PivotChart. The chart is linked to the pivot table and adjusts automatically when you change the pivot table. This is a powerful way to present your analysis visually.