Easily summarize, tabulate, and analyze data with the pivot table feature in Microsoft Excel. Below are the steps to create a pivot table in Excel.
Table of Contents
Create a pivot table in Excel
Knowing the steps to create a pivot table in Excel will make you much more productive compared to using TOTAL, SUBTOTAL and other commands to analyze data.
The first step in creating an error-free pivot table is to ensure that the source data for the pivot table is in the correct format.
Once the source data is ready, you can insert either a blank pivot table or one of the suggested pivot tables into the worksheet.
In our opinion, it is easier and faster to work with and modify a proposed pivot table than to work with an empty pivot table.
1. Prepare source data for pivot table
In general, source data for pivot tables must meet the following requirements
1. Each individual column in source data must have a heading or column label. To avoid confusion, column names must be unique and non-repetitive.
2. Source data cannot have empty columns. In fact, you cannot create a pivot table if the source data contains an empty column.
3. Avoid blank cells and blank rows in source data as it can cause errors and confusion in the pivot table.
4. Don’t include totals, subtotals, and averages (column or row totals) when selecting the source data range in a pivot table.
5. Be sure to apply formatting (date, number, etc.) to cells in the source data.
Once the pivot table source data is properly organized and meets the above requirements, you should have no trouble creating error-free pivot tables.
2. Insert the pivot table into the worksheet
To explain the basic steps to create a pivot table in Excel, we’ll use sales data recorded in two computer stores, conveniently labeled Store #1 and Store #2.
As you can see in the image above, the source data is well organized with clear column labels and has no empty columns, empty rows or cells.
Once the source data is in the right format, you can follow the steps below to create a pivot table in Excel.
1. Open the Excel file with source data that you want to include in the pivot table.
2. Choose one cell in Source Data > click insertion > tables > Recommended pivot tables Possibility.
3. On the Recommended PivotTables screen, scroll down the suggested list to view it > select it PivotTable layout you want to use and click on it OK.
Note: You can actually click on the suggested PivotTable layouts to see them in a larger view. A layout is not inserted until you click the OK button.
4. Clicked once OKExcel inserts a pivot table into a new worksheet.
3. Change the layout of the pivot table
Even after you create the PivotTable with the suggested layout, you can customize the PivotTable to suit your own needs.
Click any cell inside the PivotTable and this will open the PivotTable Field List.
Once the PivotTable Field List is active, you can modify the pivot table by adding field elements and dragging the field elements between columns, rows, and value ranges.
Initially, you might find that things go horribly wrong when you try to change the pivot table layout. However, the only way to master pivot tables is to play around and make mistakes.
It is recommended that you spend enough time playing around with PivotTable array elements and getting used to modifying a specific PivotTable.
Once you learn how to modify pivot tables, you can easily analyze large amounts of data and create all kinds of data summaries.
