In this article I’m going to described, how to used Pivot table for displaying record in query based. In another works we can filter and display data according our requirement. Suppose you've a large list of data in excel sheet. You're now ready to extract some meaningful information from the data, and find answers to questions like:
What are the total sales of each region?
What is total sales amount by each sales man?
What is total amount of per month?
To answer these and other questions, you can create a Pivot Table report an interactive table.
For demonstration I have used following data.
Select cell of table and open Pivot table from “Insert” menu option as below image.
When you press ok button then opened one new sheet where you apply condition for display data.
From figure 3, we have seen tow red rectangle shape. Top is display column name and bottom option used for apply condition according our requirement.
For example if you want to see total sales of regain wise then simple drag and drop “region” column into “column labels” box, “Sales Person” column into “Row Labels” box and “Amount” column into “Values” box and see output.
Another example of Pivot table is, suppose you want to display total sale amount month wise then first add one more column with “Month of Sale” and used formula to calculate months based on “Sale Date”. For calculating month, select cell where you display result, here I have selected G3 Cell and enter below formula into formula bar and see output as below image (figure 5).
Select Pivot Table and drag-drop “Sales Person” into “Row Labels”, “Month of Sale” into “Column Labels” and “Amount” into “Values” box and see output as below image.