How to Create a Pivot Table in Excel [A Complete Guide]

To create a pivot table in Excel, follow the instructions below:

1. Prepare a dataset in Excel.
2. Select the range.
3. Go to Insert tab > PivotTable dropdown > From Table/Range option.
4. Select a location.
5. Click OK.

By following the above steps, you can create a pivot table in Excel. Now, you can work on your pivot table to summarize and analyze data.

What is a Pivot Table?

A pivot table is an Excel tool to summarize and analyze large datasets in Excel. It has several advantageous features. You can do all kinds of calculations like sum, average, maximum, minimum, etc. Pivot tables convert values into percentages.

In addition, they also help to understand patterns and trends. For example, if a company makes its sales summary, it can see the trends of the product. The employees can work on the best-selling item and forecast the upcoming profit and sales.

Sample Dataset to Create a Pivot Table in Excel

To create the pivot table, you have to prepare a dataset first. Here I have prepared a sales report in Excel.

As it is a large dataset, it is difficult to work on it. So, I will insert a pivot table in the later part of this article. Then, I am gonna drag fields, summarize, analyze, sort, and filter data in pivot table fields.

Insert a Pivot Table in Excel

1. Select the entire range of data.
2. Go to the Insert tab in the ribbon.
3. Click on the dropdown of PivotTable.
4. Select From Table/Range.
5. Choose the location where you want to place the pivot table.
6. Click OK.
   

You have created a pivot table. You will see this view in your Excel workbook. Now, you have to select the rows, columns, and filters as per your needs.

Arrange the Pivot Table Fields in Excel

To arrange the pivot table, you have to select the column headers in the following sections, Filters, Columns, Rows, and Values. You can arrange them as per your requirements. You can make the rows into columns, and columns into rows for summarization.

1. Drag PivotTable Fields

I want to see the sales of each product with quantity. So, I have dragged the category header in the Rows section in PivotTable Fields. To see the quantity of the product and sales values, I have dragged Qty and Sales to the Columns section.

As these two headers are values, there are the sum of quantity and the sum of sales in the Values section under PivotTable Fields.

2. Filter Data

Now, we will learn to filter category-wise sales as per salesman to observe the sales of each salesman. I want to see the sales for a salesman named Martha.

To do that:

1. Drag the salesman in the Filters section.
2. Click on the Filters icon.
3. Click on Martha in the Salesman filter.

3. Sort Data

You can sort the amounts in ascending or descending order to keep an eye on the product details. Now, I will sort the quantity in ascending order. For instance, if any product is almost out of stock, then a company will refill that.

To sort the quantity in ascending order:

1. Click on the dropdown of the first cell of the table.
2. Choose More Sort Options from the dropdown list.
   
3. Select Ascending in the Sort dialog box.
4. Choose Sum of Qty from the dropdown.
5. Click OK.
   
   Now, you can see the updated result in the pivot table.
   

Set Value Field Settings

To set the value of any particular column, you can change it from Value Field Settings in Excel. The Sum is selected by default in pivot tables.

To set the value field settings:

1. Go to the column header, if you want to change the settings.
2. Right-click on it.
3. Choose Value Field Settings from the context menu.
4. Select the type of calculation you want to summarize from the selected field.
   

How to Show PivotTable Fields?

After creating a pivot table, you can select a cell in the pivot table to open the pivot table fields. However, sometimes you will not find the pivot table fields even after clicking on the cells inside the pivot table.

To show the PivotTable fields:

1. Click on a cell inside the pivot table.
2. Go to PivotTable Analyze tab.
3. Navigate to the Show group.
4. Select Field List tool.
   

Refresh Pivot Table in Excel

The pivot table automatically updates if any changes are made. However, you will encounter trouble to update the data.

To refresh a Pivot Table:

1. Select a cell inside the pivot table.
2. Go to PivotTable Analyze tab.
3. Navigate to the Data group.
4. Click on Refresh command.

Alternatively, you can use the shortcut keys to refresh the pivot table. Press ALT+F5 to refresh your Pivot Table.

Conclusion

To sum up, this article contains details of creating a pivot table in Excel. Here, I have included the step-by-step guide to each section. Hence, you can follow the steps very easily. You will see the relevant examples to relate to why each segment in the pivot table is important. I hope you won’t face any troubles regarding the pivot table in Excel. You have the guidelines to create it and handle the queries about it.

Frequently Asked Questions

How do I create a simple table from a Pivot Table?

To create a simple table from a Pivot Table in Excel:

1. Select the Pivot Table you want to convert.
2. Copy the selected data (Ctrl+C).
3. Paste it into a new worksheet or location (Ctrl+V).

This process copies the results of your Pivot Table into a regular Excel table, which you can further manipulate or use for different purposes.

How do I create a formula in a PivotTable data?

To create a formula in a PivotTable data:

1. Click on the Pivot Table.
2. Navigate to the PivotTable Analyze tab.
3. Go to Calculations group > Fields, Items, & Sets dropdown, and select Calculated Field option.
4. In the Name box, provide a name for the field you’re creating.
5. In the Formula box, input the desired formula using Excel functions.
6. Click Add to generate the calculated field in the PivotTable and hit OK.