How to make a pivot table in Excel

A Pivot Table is a powerful tool in Microsoft Excel that can save a lot of time when it comes to analyzing data. That is why it is no surprise that it is common for companies to utilize this tool in their business reporting.

One of the most important points to getting started with pivots is recognizing the best time to use them. In the guide below we will be breaking out rent and state in our data set, but in the real world, it will be more likely for there to be many more columns of data. Many more columns of data make for a recommended situation to insert a PivotTable because it will make the data easier to read and less intimidating.

Here is a quick lesson on ensuring the data is ready for a PivotTable.

To get started select the data the PivotTable is intended for. While the data set is highlighted select the insert tab at the top of the page and click PivotTable.

Selecting a data set for a Pivot Table.

Select Data>Insert>PivotTable

Insert a PivotTable by clicking insert and the PivotTable option.

Doing so will prompt the following create a PivotTable menu.

Inserting a pivot opens a create a PivotTable menu.

 

 

The top portion of this menu is for data selection and the bottom is asking where the PivotTable should be placed. 

If “OK” is selected it will open the pivot table in another worksheet as that is the default.

A blank Microsoft Excel PivotTable

In order to place the pivot elsewhere, the location needs to be changed from the default to the desired location. In this case, I am selecting the Pivot to go into cell K2.    

Desired PivotTable location K2 in the create a PivotTable menu.

Find the result below.

Blank PivotTable in cell K2

Once the Pivot is in the chosen spot, click and drag fields as needed. As an example, I will click and drag fields so that the purchase price is shown by each state.

For a quick walkthrough on PivotTable fields click here.

Pivot Table with state field by sum of purchase price

Next, In-Place Rent is needed in the analysis. Specifically, the average rent per state. 

The first step will be to drag the In-Place Rent field into the value section.

Including in-place rent field to the value section of a pivot.

Currently, the Pivot is showing the total amount of rent per state. In order to switch the values to show averages, right-click on the in-place rent column and select summarize values by averages as shown below.

right-click column>summarize values by>average

Summarize values by average in a pivot column.

summary