articles

Home / DeveloperSection / Articles / Pivot Table in Excel

Pivot Table in Excel

AVADHESH PATEL4197 22-Mar-2013

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


Updated 07-Sep-2019
Avadhesh Kumar Patel District Project Manager - Aligarh 14 months work experience in Panchayati Raj Department Sector as District Project Manager & 12 months work experience in IT Sector as Software Engineer. :-)

Leave Comment

Comments

Liked By