Learn Pivot Table - Tutorial & Magical Quotes: Easy way to Learn Pivot Table Step By Step - Tutorial

Using Microsoft Excel 2007

Case1:

Pivot Table:

Write the below data in the excel as shown:

We need to create a report such that,

Names are displayed in Row as above – but unique.

Products in B column should be displayed in row as labels.

The respective cost must be displayed for each name as values.

Solution:

Highlight the values from A1 till D6 in the excel.

Click Insert -> Pivot Table -> Pivot Table.

Click OK.

Now, the empty pivot table is displayed in the new worksheet as below

Now, Drag the Name and Drop inside the Row Labels Box as below

If you like see that Actual Column Name, Change the Row Labels as “name” as below

Now, Drag the 2^nd Column “Product Bought” inside Column Labels. It will show as :

Rename Column labels to “Product Name” (as u like)

We know that, Honey is bought by Prem alone. If you filter only Honey under Product Name, you can see that, only Prem is displayed as below:

Now, Drag the Cost under Values, so that, respective cost is displayed for the name displayed in row mapped to product name displayed in column. Also, Grand Total is automatically calculated as shown

You can rename as below, for your understanding.

If you want to hide the Row Labels and Column Labels,

Under Options -> unhighlight “Field Headers”. It will show as:

On which date, who purchased, which product, that day turnover?

For this, Add the Date Purchased inside Report Filter. This will add a new row at the top, from which you can filter based on date, and corresponding data gets displayed in the existing pivot table.