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.
I am going to describe to you how to create a pivot table manually. I shall use here again our Bank-accounts.xlsx file. If you are unfamiliar with the elements of a pivot table, read pivot table terminology.
Creating a Pivot Table Manually
The following steps will guide you to create a pivot table manually:
Step 1: Specifying the data
If your data is in a worksheet range, select any cell in that range and then choose Insert ➪ Tables ➪ PivotTable. The Create PivotTable dialog box, shown in the following Figure, will appear.
Excel automatically attempts to guess the data range, based on the location of the cell you have selected. If you want to create a pivot table from an external data source, you will select Use an external data source option and then click Choose Connection to open your external data source.
Tip: If you’re creating a pivot table from data in a worksheet, it’s better to 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.
Step 2: Specifying the location where you want to create the pivot table
Use Choose where you want the PivotTable report to be placed section of the Create PivotTable dialog box to select the location for your pivot table. The default location is on a new worksheet, but you can select any range on any worksheet, even the worksheet that contains the data. Click OK, and Excel creates an empty pivot table and displays a PivotTable Fields task pane. See the following Figure.
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 3: Laying out the pivot table
Now we shall work on the PivotTable Fields task pane to layout the pivot table. 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:
- You can 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.
- You can also place a checkmark next to the item at the top of the PivotTable Fields task pane. Excel places the field automatically into one of the four boxes at the bottom. You can drag the field name to a different box if it is placed in a wrong area.
- This is the last technique. You can right-click a field name at the top of the PivotTable Fields task pane, a shortcut menu will appear. You can choose a location from the shortcut menu.
The following steps will create a pivot table:
- Drag the Amount field into the Values area. The pivot table will display the total of all the values in the Amount column.
- Drag the AcctType field into the Rows area. The pivot table will show now the total amount for each of the account types.
- Now, drag the Branch field into the Columns area. The pivot table will show now the amount for each account type, cross-tabulated by branch. Observe closely. You will find that the total amount of each AccType is calculated on the right side of the pivot table. At the same time, the total amount opened in every branch is also calculated at the bottom of the pivot table.
The following figure gives us our desired Pivot Table. From this Pivot Table, we can find out easily grand total of the amount opened in the Westside branch. The pivot table is in the “Pivot Table 1” sheet. We have changed the sheet name to “Pivot Table 1” after the creation of the pivot table.
Happy Excelling 🙂