Skip main navigation

How to Create Pivot Tables in Excel

This video explains that pivot tables are powerful tools to calculate, summarise and analyse data that lets you see comparisons, patterns and trends.
A pivot table is a powerful tool to calculate, summarise and analyse data that lets you see comparisons, patterns and trends. To create a pivot table, first select the cells you want to create a pivot table from. At this points it’s worth noting that your data shouldn’t have any empty rows or columns and it must have only a single row heading. Then select Insert, Pivot Table.
Under Choose the data that you want to analyse, select Select a table or range. In Table/Range, verify the cell range or sheet.
Under Choose where you want the Pivot Table report to be placed, you can select New Worksheet to place the pivot table in a new worksheet or Existing Worksheet and then select the location. We’ll select New Worksheet for now and then click OK. After you create a pivot table you’ll see the field list appear on the right-hand side. You can change the design of the pivot table by adding and arranging its fields. In the Field section, you can pick the fields you want to show in your pivot table and the area section at the bottom in which you can arrange those fields to display the information the way that you want.
You can use the field section of the field list to add fields to your pivot table by checking the box next to field names to place those fields.
You can rearrange the fields into different areas. Filters are shown as top-level report filters above the pivot table like this.
Columns are shown as column labels at the top of the pivot table.
Rows are shown as row labels on the left side of the pivot table. Values area fields are shown as summarised numeric values in the pivot table, such as sum of gross sales and sum of units sold. If you have more than one field in an area you can rearrange the order by dragging the fields into the position that you want. To delete a field from the pivot table, drag the field out of its area of selection.
Grouping data in a pivot table can help you show a subset of data to analyse. For example, you may want to group an unwieldily list of dates or times into quartiles and months, like in this image. To group data in the pivot table, right-click a value and select Group.
In the Grouping box, select Starting at and Ending at chat boxes and edit the values if needed. Under By, select the time period. For numerical fields enter a number that specifies the interval for each group. And then click OK. To ungroup grouped data, right-click any item that is in the group
and select Ungroup.
To focus on a smaller portion of your pivot table data for in-depth analysis, you can filter it. First, insert one or more slicers for a quick and effective way to filter your data. To filter data in a pivot table using a slicer, first create the slicer. Select a cell in the pivot table and select Analyse, Insert Slicer.
Select the fields you want to create slicers or filters for.
And then click OK.
Excel created three slicers based on my selections, one for country, another for product, and the third for years. Next we can filter the pivot table using the slicers.
To filter the pivot table using the slicer, click the values that you want to filter by.
The pivot table is filtered based on your selections. To remove a filter, click the Clear Filter icon on the slicer.
Now you know the basics about creating a pivot table, how you can group data in a pivot table, and how you can create slicers to help you focus your analysis.
This video on the basics of creating pivot tables will include:
  • Creating and selecting the correct cells
  • Using pivot tables in an existing or new worksheet
  • How to add fields
  • How to edit filters, columns and rows
  • How you can group data in a pivot table in order to analyse it
  • How to create and edit slicers to focus analysis
This article is from the free online

Data Analytics for Business: Creating Databases

Created by
FutureLearn - Learning For Life

Our purpose is to transform access to education.

We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.

We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.

Learn more about how FutureLearn is transforming access to education