Advanced Pivot Table
For this advanced pivot table example we will create a pivot table that shows the total sales for each day, broken down by Product Name and Customer Name. The process for creating this Pivot Table is described below. Note that the initial steps are the same as for the basic pivot table in My previous article of this tutorial. Please read my previous article or Pivot Table creation steps is describe below for recap only:
1. Select any cell within the data range or select the entire data range to be used in your Pivot Table.
Note: If you select a single cell in the data range, Excel will automatically identify, and select the whole data range for your Pivot Table. In order for Excel to successfully do this, the following must be satisfied:
- Each column in the data range has a unique header
- The data does not contain any blank rows
2. Click on the Pivot Table button, which is generally located on the left, within the ‘Insert’ tab.
3. You will be presented with the window entitled ‘Create PivotTable’ (shown on the right).
- Make sure that the selected range is the range that you want to use for your Pivot Table.
- There is also an option asking where you want the Pivot Table to be placed. If you are not sure, select the option ‘New worksheet’
- Click OK
You will now be presented with an empty Pivot Table, and a ‘Pivot Table Field List’, which contains the data fields for your Pivot Table. Note that these are the headers from your data spreadsheet. We want the Pivot Table to show the sums of the sales figures for each day, broken down by Product Name and Customer Name.
Therefore, from the ‘Pivot Table Field List’:
- Drag the ‘Customer Name’ field into the area marked ‘Row Labels’
- Drag the ‘Product Price’ field into the area marked ‘Σ Values’
- Drag the ‘Date Time’ field into the area marked ‘Row Labels’
Which is looks like:
In the above image, you see the Customer Name field is grouped with Data Time field and respective sum of Product price is shown. But if you want to group Date and Time Field with only Data Format which is actually better for summary information, please do the following:
1. Right click on any of the dates in the left hand column of the Pivot Table
2. Select the option Group…
3. A window will pop up. Select the option Months
(note that you can also group dates and times by other time periods, such as quarters, days, hours, etc.)
4. Select Days and Click OK
The resulting Pivot Table (shown in the below) is populated with the daily sales totals for each Product Name and each Customer Name.
However, now you want to Customer Name and Date and Time field by side by side which is known as Tebular format data. To do this:
1. click the drop down menu in the Customer Name filed reside under ROWS section, then select Field setting option.
2. Now a new window is popup. In the “Subtotal and Filters” tab, select None if subtotal is not required or leave it. Now click on “Layout & Print” tab and select “Show item labels in tabular form.
3. Now repeat the 2nd step for rest of the fields reside in the ROWS portion. Please remember, this repetition is required when you have more than two fields in this portion otherwise when if fix it for a field, you will get your result which is looks like below:
Now if you want to filter pivot table data like Customer Name field and Date Time Field, you can do this. The Pivot Table report filter allows you to view the data for a single value in one of your data fields. For example, in the pivot table above, you could view just the data for the Product MS Excel or just the data for the Customer ID 1386. This is illustrated below. In order to view just the data for the product ‘MS Excel’ from the Product Name field, return to the ‘Pivot Table Field List’, and drag the ‘Product Name’ field header into the Report Filter area, as shown on the below.
Now we are working with other features for build a advanced Pivot Table by the attached database.