Pivot tables in Excel

Pivot tables in Excel

Pivot tables in Excel are a powerful and versatile tool that can help you analyze, summarize, and manipulate data. Pivot tables can also help you discover patterns, trends, and relationships in your data. In this article, I will explain what pivot tables are, how to create them, and how to use them effectively.

What are pivot tables?

A pivot table is a way of organizing and displaying data in a tabular format. A pivot table can have one or more rows and columns that show different aspects of your data, such as categories, subcategories, sums, counts, averages, percentages, etc. A pivot table can also have filters that let you narrow down your data to show only what you want.

A pivot table is based on a source data set, which is usually a range of cells or a table in an Excel worksheet. The source data set should have a clear and consistent structure, with each column having a unique header and each row representing a record or an observation.

For example, suppose you have a data set that shows the sales of different products in different countries and categories. The data set looks like this:

Order ID	Product	Category	Amount	Date	Country
1	Apple	Fruit	10	01/01/2023	USA
2	Banana	Fruit	20	01/02/2023	France
3	Carrot	Vegetable	15	01/03/2023	Germany
…	…	…	…	…	…

You can use a pivot table to summarize this data in various ways. For example, you can create a pivot table that shows the total amount of sales for each product in each country. The pivot table would look like this:

Country	Apple	Banana	Carrot
France	16	60	0
Germany	12	0	45
USA	40	0	30
Grand Total	68	60	75

You can see that the pivot table has four areas:

The Rows area shows the country names as row labels.
The Columns area shows the product names as column labels.
The Values area shows the sum of the amount for each combination of row and column labels.
The Filters area is empty in this example, but it can be used to filter the data by any field.

You can drag and drop fields from the source data set to the different areas of the pivot table to change how the data is displayed. You can also change the calculation type, sorting order, formatting, and layout of the pivot table.

How to create a pivot table?

There are two ways to create a pivot table in Excel: using the Recommended PivotTables option or using the PivotTable option.

Using the Recommended PivotTables option

The Recommended PivotTables option is a quick and easy way to create a pivot table based on Excel’s suggestions. Excel analyzes your data and shows you several possible pivot tables that you can choose from. To use this option, follow these steps:

Select any cell in your source data set.
Go to the Insert tab and click Recommended PivotTables on the left side of the ribbon.
When the window opens, you’ll see several pivot tables on the left. Select one to see a preview on the right. If you see one you want to use, choose it and click OK ¹.
Excel will create a new worksheet with the selected pivot table. You can then customize it as you like.

Using the PivotTable option

The PivotTable option is a more manual way to create a pivot table. You can specify the location and the fields for your pivot table. To use this option, follow these steps:

Select any cell in your source data set.
Go to the Insert tab and click PivotTable on the left side of the ribbon.
When the dialog box opens, Excel will automatically select your data range. You can also choose a different range or an external data source if you want.
Choose where you want to place your pivot table: in a new worksheet or an existing worksheet².
Click OK.
Excel will create an empty pivot table with the PivotTable Fields pane on the right side of the screen. You can then drag and drop fields from the pane to the different areas of the pivot table.

How to use pivot tables effectively?

Pivot tables are very flexible and customizable. You can use them to explore and analyze your data in many ways. Here are some tips and tricks to help you use pivot tables effectively:

Use slicers to filter your data by one or more fields. Slicers are interactive buttons that let you quickly select the values you want to see in your pivot table. To add a slicer, go to the Analyze tab (or the Options tab for Excel 2010 and earlier) and click Insert Slicer. Then choose the fields you want to use as slicers³.
Use timelines to filter your data by dates. Timelines are similar to slicers, but they only work for date fields. They let you select a specific time period, such as a year, a quarter, a month, or a day. To add a timeline, go to the Analyze tab (or the Options tab for Excel 2010 and earlier) and click Insert Timeline. Then choose the date field you want to use as a timeline.
Use the Field List to arrange fields in your pivot table. You can drag and drop fields from the PivotTable Fields pane to the different areas of the pivot table. You can also right-click on a field and choose where you want to place it: in the Filters area, the Rows area, the Columns area, or the Values area.
Use the Value Field Settings to change the calculation type, number format, and name of your values. By default, Excel summarizes your data by either summing or counting the items. You can change this to other types of calculations, such as average, minimum, maximum, percentage, etc. You can also change the number format, such as currency, decimal, percentage, etc. You can also rename your values to make them more descriptive.
Use the Design tab to change the layout and style of your pivot table. You can choose from different layouts, such as compact, outline, or tabular. You can also choose from different styles, such as light, medium, or dark. You can also customize your own style by changing the colors, fonts, borders, etc.

Conclusion

Pivot tables are a great way to summarize and analyze large and complex data sets in Excel. They can help you find insights and patterns in your data that might otherwise be hidden or difficult to see. Pivot tables are also easy to create and modify using Excel’s built-in features and options.