How to create a pivot table in Excel

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.

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.

Sample data to create a pivot table

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.

Insert recommended pivot table in Excel

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.

In Excel, select Recommended PivotTable

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.

Change the layout of the pivot table

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.

Related

Leave a Reply

Your email address will not be published.