How to make a pivot table in Excel
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.
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.
Doing so will prompt the following 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.
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.
Find the result below.
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.
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.
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