Creating an Excel Pivot Table Manually

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 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.

What is a pivot table

In the Create PivotTable dialog box, you tell Excel where the data is and where you want the place the pivot table.

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.

What is a Pivot Table

We shall use this PivotTable Fields task pane to build our pivot table.

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 of 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 PivotTable Fields task pane to lay out 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:

  1. 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.
  2. You can also place a check mark 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.
  3. 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:

  1. Drag the Amount field into the Values area. The pivot table will display the total of all the values in the Amount column.
  2. Drag the AcctType field into the Rows area. The pivot table will show now the total amount for each of the account types.
  3. 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 total amount of each AccType is calculated on the right side of the pivot table. At the same time, total amount opened in every branch is also calculated at the bottom of the pivot table.
What is a pivot table

Dragging the fields to the lower part of PivotTable.

The following figure gives us our desired Pivot Table. From this Pivot Table, we can find out easily grand total of amount opened in Westside branch. The pivot table is in “Pivot Table 1” sheet. We have changed the sheet name to “Pivot Table 1” after the creation of pivot table.

What is s pivot table

The pivot table is showing the summary of our data.

Happy Excelling 🙂

Download Sample File

Bank-accounts.xlsx


Hello! Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply. Keep in mind this African proverb: "If you want to go fast, go alone, If you want to go far, go together." Let's together explore Excel deeply! ☕

2 Comments
  1. Reply
    TG December 28, 2017 at 6:18 PM

    Hi, what an excellent article series!

    Straight and to the point, very easy to follow, practice and understand.

    Many thanks!

    P.S. Why does Excel 2016 show “security warning – external data connection” after downloading the example Excel files?

    Leave a reply