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:
- Normal tables are fixed & Pivot tables are Flexible.
- 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).
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
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:
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)
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.
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:
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.
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:
Pls find following articles.
https://cmaindiagroup.com/hlookup-formula/
For Blogger Blog – http://pnautiyal.blogspot.com