How to Create Pivot Tables in Excel

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.