PIVOT TABLE in excel

Pivot tables are one of Excel‘s most powerful features. A pivot table allows you to extract the significance from a large, detailed data set.

The Basic Difference between Normal table & Pivot table:

  1. Normal tables are fixed & Pivot tables are Flexible.
  2. Normal Tables give you entire data while pivot table give you summarized data or Distinguishes records.

Note: While preparing Pivot table make sure cells in your data in excel sheet should not be blank.

Our data set contain values with 6 Fields, Date, Name, Region, Unit, Price per Unit & cost of Goods Sold (COGS).

pivot table 1
Picture 1

It simplifies our work, as this makes our work easier, we do not have to apply SUMIF or COUNTIF formula again.

First Step,

Select datà Insert à Pivot Chart à Pivot Chart & Table

pivot table
Picture 2

Next step, select whether you want your Pivot table should be in NEW WORKSHEET or EXISTING WORKSHEET. We have selected existing worksheet with location (Location is cell where you want your pivot table to be shown), as pic shown below:

pivot table
Picture 3

After selecting cell, your table will appear like the picture given below, 6 fields of data will appear in Pivot Table Fields, from here we need to drag fields & drop it to various fields Like: Columns, Rows, Values. ( Remember- In values fields, you need to put numerical values only)

pivot table
Picture 4

Next step is to arrange data according to need, here we dragged Name field in Column area, Region in Row Area & COGS in Value area. As a result, you will get detailed knowledge of each & every region & Name wise Cost of Goods sold.

pivot table
Picture 5

This data can be changed according to your desire.

If you want to count how much sales done by each person then just need to drag Name field in Rows & in Values both, as shown in Pic below:

pivot table
Picture 6

Here in Picture, It’s a shortcut to COUNTIF formula.

If you want to calculate COGS under each name, then drag COGS field in Values & Equivalent to SUMIF Formula.

pivot table
Picture 7

If you want to see detail of each person Sales Date wise or Region wise, you can double click on COGS i.e, click on 65000 cell, you will find detail of Jatin in next Worksheet, as shown in Picture below, you can see every detail:

pivot table
Picture 8


Pls find following articles.
https://cmaindiagroup.com/hlookup-formula/

For Blogger Blog – http://pnautiyal.blogspot.com

Leave a Comment

//oagnatch.com/4/3629851