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.

Figure 1:

Pivot Table in Excel

Select cell of table and open Pivot table from “Insert” menu option as below image.

Figure 2:

Pivot Table in Excel

When you press ok button then opened one new sheet where you apply condition for display data.

Figure 3:

Pivot Table in Excel

 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.

Figure 4:

Pivot Table in Excel

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

=MONTH (D3:D28)

Pivot Table in Excel

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.

Pivot Table in Excel

  Modified On Nov-29-2017 09:39:48 PM
  1. hi ,
    Another great article by author, well done. As I have previouely commented in your article, I'm new with C# prgramming ,and I found this artile - how to create Pivot table in Excel with C# ,amzing/usefull for those who code.Share with you guys.

    kind regards.

Leave Comment