How to Create a Pivot Table in Excel (with Easy Steps)

It is easy to use Recommended PivotTables option for creating pivot tables. But you might prefer to create a pivot table manually. Prior to Excel 2013, manually creating a pivot table was your only option. In this article, I am going to describe to you how to create a Pivot Table in Excel manually. If you are unfamiliar with the elements of a pivot table, read pivot table terminology.

Download Practice Workbook

7 Easy Steps to Create a Pivot Table in Excel Manually

Here, I have taken the following dataset to explain this article. I will show you how you can create an Excel Pivot Table manually by using this dataset.

The following steps will guide you on how to create a Pivot Table in Excel manually.

Step-01: Inserting Pivot Table in Excel

In this first step, I will show you how you can insert a Pivot Table in Excel.

• Firstly, select the data range.
• Secondly, go to the Insert tab from Ribbon.
• Thirdly, select PivotTable.

Subsequently, a dialog box named PivotTable from table on range will appear.

• After that, select New Worksheet if it is not selected already.
• Next, select OK.

Now, a Pivot Table will be inserted into a new worksheet.

You can also insert the Pivot Table in the same worksheet.

• Firstly, select the data range.
• Secondly, go to the Insert tab.
• Thirdly, select Pivot Table.

Here, a dialog box named PivotTable from table or range will appear.

• Firstly, the Table/Range will already be selected.
• Secondly, select Existing Worksheet.
• Thirdly, select the Location where you want your Pivot Table. Here, I selected cell J4.
• Finally, select OK.

Subsequently, a Pivot Table will be inserted into your selected location.

There’s another way of inserting a Pivot Table in the same worksheet.

• First, select the cell where you want your Pivot Table. Here, I selected cell J4.
• Secondly, go to the Insert tab.
• Thirdly, select Pivot Table.

After that, PivotTable from table or range dialog box will appear. Here, the Existing Worksheet option and the location will be selected already.

• Firstly, select the marked button to add the Table/Range.

• Secondly, select the data range.
• Thirdly, select the marked button to add the selected range to the dialog box.

• Finally, select OK.

Now, the Pivot Table will be inserted into your selected location.

Tip: If you’re creating a Pivot Table from data in a worksheet, you can create a table first for that data range. To create a table, select any cell in that data range and choose Insert ➪ Tables ➪ Table. Now, if you expand the table by adding new rows of data, Excel will automatically refresh the Pivot Table.

Read More: How to Create Pivot Tables for Meaningful Data Analysis!

Step-02: Adding Fields to Pivot Table

In this step, I will show you how to add fields to the Pivot Table. Here, I will work on the PivotTable Fields task pane to lay out the pivot table.

After inserting Pivot Table the PivotTable Fields will appear on the right side of the screen. PivotTable Fields task pane has two parts: the upper part, where the field names reside, and the lower part, where you will place the field names from the upper part.

You can lay out the Pivot Table in the following ways:

• Firstly, select and drag the field names (at the top of the PivotTable Fields task pane) to one of the four boxes at the bottom of the task pane. Here, I dragged the Amount field into the Values area. The Pivot Table will display the total of all the values in the Sum of Amount column.
• Secondly, select and drag the field names you want in your Row Labels into the Rows area. Here, I selected the Branch, OpenedBy, and AccType fields in the Rows area.

The following figure gives me the desired Pivot Table. From this Pivot Table, you can easily find out the grand total of the amount opened in the Westside Branch or any other Branch.

Here, I have hidden the gridlines to give the Pivot Table a better view.

Tip: Typically the PivotTable Fields task pane is docked on the right side of your Excel window. You can drag its title bar to move it anywhere in your Excel window. If you click a cell outside the pivot table, the task pane will temporarily hide.

Read More: How to Modify an Excel Pivot Table

Step-03: Formatting Numbers in Pivot Table in Excel

In this step, I will explain how you can change the Number Format in the Pivot Table.

• Firstly, Right-click on any cell of the Pivot Table where you want to change the Number Format.
• Secondly, select Number Format.

After that, a dialog box named Format Cells will appear.

• Firstly, select the Category you want. Here, I selected Currency.
• Secondly, select OK.

In the following picture, you can see that the Number Format has changed to Currency.

Read More: How to Show Zero Values in Excel Pivot Table: 2 Pro Tips

Step-04: Refreshing Values in Pivot Table

Here, I will show you how to refresh data in a Pivot Table in Excel.

• Firstly, Right-click on any cell of the Pivot Table.
• Secondly, select Refresh.

Now, the data in your Pivot Table will be refreshed.

Step-05: Sorting Data in Pivot Table

In this fifth step, I will show you how you can sort data in a Pivot Table.

• Firstly, Right-click on any cell from the column where you want to sort data.
• Secondly, select Sort.
• Thirdly, select the sorting order you want. Here, I selected Sort Smallest to Largest.

Now, you will see that the data in your Pivot Table are sorted in the order you selected.

Step-06: Adding Percent of Total in Pivot Table

Here, I will explain how to add Percent of Total in your Pivot Table.

• Firstly, select any cell where you want to add a Percent of Total.
• Secondly, select Show Values As.

Now, a drop-down menu will appear.

• Thirdly, select the option you want. Here, I selected % of Grand Total.

In the following picture, you can see that Percent of the Grand Total is added to my Pivot Table.

Step-07: Moving Pivot Table to a New Location in Excel

In this step, I will show you how you can move the Pivot Table in Excel to a new location.

• Firstly, select any cell of the Pivot Table.

• Secondly, go to the PivotTable Analyze tab.
• Thirdly, select Move PivotTable.

After that, a dialog box named Move PivotTable will appear.

• Firstly, select the Location where you want to move the Pivot Table. Here, I selected cell D4.
• Secondly, select OK.

Finally, you will see the Pivot Table is moved to the new location.

How to Create a Pivot Table in Excel with Dates

In this section, I will explain how to create a Pivot Table in Excel with dates. Let’s see the steps.

Steps:

• Firstly, select the data range.
• Secondly, go to the Insert tab.
• Thirdly, select PivotTable.

•  After that, select New Worksheet if it is not selected already.
• Then, select OK.

Now, a Pivot Table will be inserted into a new worksheet.

After inserting Pivot Table the PivotTable Fields will appear on the right side of the screen.

• Firstly, select the Date field and drag it into the Rows area. The Pivot Table will display it in the Rows Label column.
• Secondly, select and drag the Amount field into the Values area. The Pivot Table will display the total of all the values in the Sum of Amount column.

Now, you can see that I have inserted the fields into the Pivot Table. And, you can see that I have created my desired Pivot Table with dates.

Now, I have formatted the worksheet to give the Pivot Table a better look.

1. Grouping Dates in Pivot Table

Here, I will show you how to group dates in a Pivot Table in Excel. Let’s see the steps.

Steps:

• Firstly, Right-click on any date in the Pivot Table.
• Secondly, select Group.

After that, a dialog box named Grouping will appear.

• Firstly, select the category by which you want to group the dates. Here, I selected Months.
• Secondly, select OK.

Now, you can see that the dates are grouped by Months in the Pivot Table.

2. Ungrouping Dates in Pivot Table

In this part, I will explain how you can ungroup the grouped dates in Pivot Table in Excel. Let’s see the steps.

Steps:

• Firstly, Right-click on any grouped date in the Pivot Table.
• Secondly, select Ungroup.

Finally, the grouped dates will be ungrouped.

Things to Remember

• You must remember that if you group any date in your Pivot Table it will be grouped in all the other worksheets of that workbook.

Practice Section

Here, I have provided a practice sheet for you so that you can practice how to create a Pivot Table in Excel manually.

Conclusion

In this article, I explained how to create a Pivot Table in Excel manually. Here, I explained it in easy steps. I hope, this article was clear to you. For more articles like this stay connected with ExcelDemy.

Related Articles

• Reverse Pivot Tables – Unpivot Summary Data
• How to create a pivot table report in Excel
• Excel Pivot Table Tutorials for Dummies Step by Step | Download PDF
• How to Filter Excel Pivot Tables with Slicers!
• Creating a Pivot Table Automatically
• 8 Excel Pivot Table Examples – How to Make a PivotTable!