How to create a Pivot Table in Excel

Learn: How to create a Pivot Table in Excel

Pivot Table ki Basics jaankari chaaiye? Toh dekhiye humara pura Video:

Magar pehle aapke mun me jarur ek Sawal aayega ki Pivot Table karta kya hai Excel mai?

Toh usska Jawab janiye, ki agar aapko Excel mai kaam karna hai toh ye trick bohot Jaruri hai (Inside Out).

Chaliye Medan Par!!, Hum aapko Sikhaayege ki kiss tarah ye Data ko Analyise karne mai madat karega?

Chalo Maan lijiye ki Meri Aachar ki Company hai. Ha ji Maanlo!

Me alag alag prakaar k Aachar banata hu, kabhi Tamato toh kabhi chilli toh kabhi Carrot, aapko chaaiye toh aap le bhi sakte ho, par pehle aap Pivot Table dekhiye:

Mujhe Pehle ek Report tayar karni, jaise ki:

  • Kiss State mai mere Aachar k order jaate hai?
  • Konsa Aachar jyaada faida de rha hai mujhe?

Pivot Table bannane se pehle dekhiye Pivot Table hai kaha?

  • Pivot Table hota hai Insert Tab me.

Par Pivot Table ko use karne se pehle aapko kuch chize yaad rakhni hogi, jaise ki:

  1. Koi bhi Header Blank na ho, agar hai toh usse bhar dijiye.
  2. Koi bhi cell Merge na ho, agar merge hai toh simple si baat hai aap unko unmerge kar dijiye.

Chaliye ab Medan Par Khel Chaalu karte hai!, Ji ha hum banate hai Pivot Table.

  1. Pure Data ko Choose Karte hai.
  2. Insert Tab me jaaiye.
  3. Click kijiye Pivot Table par.

Ye lo, ban gya Pivot Table.

Agar aapko Pivot Table Ka Pura faaida uthana hai, ek Setting ko jarur ON kijiyega.

Setting Change karne k liye :

  • Right click kijiye.
  • Pivot Table Option mai jaaiye.
  • Aapko dikhega ek Display Tab, usme jaaiye.
  • Jaldi Jaldi bhagiye Classic Pivot Table Layout option Tab k upar.
  • Click OK.

Waah Bhaiya! Yaha toh Najara he badal gya.

Ab aapke saamne 4 alag alag Sections dikh rhe hai jesse ki:

  • Row Feilds
  • Column Feilds
  • Report Filter Feilds
  • Value Feilds

Aap dekhege ek medaan ka hissa jo sabse Badda hai.

Qki, jitne bhi Gannit(Mathematics) wale kaam hai, dimag waale jaise ki :

  • Sum
  • Average
  • Percentage
  • Count

Vo ye area mai he hoge.

Toh chaliye dekhte hai Report banta kaise hai?

Aapko Right Side pe Feilds dikhege, Haa ji Feilds jo aapke Data k Header par the :

  • Date
  • Name
  • State
  • Pickles
  • Sales

Ye same chize hai Right Side par, taki aap unko Select kar sake aur dekh sake ki Data kiss tarah Report mai tabdil ho rha hai.

Ab agar aapko dekhna hai ki kitne Orders aaye the, mai Sales ko vapas ussi Area mai fekta hu jaha Gannit(Mathamatics) already hai.

Lekin ye kya hua Bhaiya? Same number dubara!, hume Chaaiye jo Count dikha sake.

Magar aap dariye nahi:

  • Aap koi bhi number par Right Click kijiye
  • Aapko option aayega Summaraize Value By
  • Fir aap usko banna sakte ho Count.

Toh aap dekh sakte ho aapko Order = 9 aaye the jisni Total Value = 7245 hai.

Aap Dekh sakte ho, kitne aasani se aapne Sum bhi nikala aur Count bhi.

Abb aapke Mun mai ye Sawaal hai ki Bikri hui vo accha hai par konse Aachar aur konse State mai gaye vo kaise pata chalega?

Chaliye aapko humare Naye Trick k baare mai batata hu :

  • Aap jaao Analyse mai(2010 mai ye Button ka naam hai Options)
  • Ek option milega Insert Slicer(2007 k version mai ye feature available nahi hai)
  • Slicer→Choose kijiye Pickles
  • Press OK

Waah Bhaiyaa! Ek baddasa User Interface mere saamne aagya.

Agar aap Choose Karege Green Chilli toh aapko Report Sirf aur Sirf Green Chilli ka Dikhayega.

Vese he aapko baaki sub k Report Ek Ek Karke Dikhege.

Agar aapka Pivot Table(Report) ready hogya aap alag alag Design me daal sakte hai :

  • Design mai jaaiye or jo aapko pasand hai unme se ek Choose kar sakte ho.

Abb agar aapko dekhna hai ki Total Sales mai Karnataka k Sales mai kitna Percentage aata hai? Ye bhi easy hai,

  • Right Click kijiye vo number par
  • Jaaiye Show Value As par(Value Feild Settings mai bhi same Setting milegi
  • Choose kijiye Percentage(%) of Grand Total

Aapko subka Percentage mil jayega.

Abb aapko Percentage k Sath Number bhi chaaiye Total Sales ka, toh chaliye vo bhi kar dette hai :

Aap dekhoge aapke Screen k Right Side pe Niche ki taraf 4 Box hai aur usske andar Values bhi hai,

Agar aapko lage Sum aapke Kum(Less) ka nahi hai aap ussko hatta sakte haivapis usse OFF kar sakte hai.

Aur ab aapke pass Basic Data hai, aap Press kar sakte hai F11.

  • F11 : Shortcut Key hai ho fatakse ek Chat Bannake dedega.

Chaliye, Jaate Jaate aapko ek Naya Trick Sikha Deta hu jo mujhe bohot madat karta hai Har Din(Almost Daily).

  • Saare Options ko OFF kardo
  • Date ko Row Feilds me le aao
  • Koi bhi ek Date par Right Click karo aur karo Group
  • Select kijiye Years and Months
  • Click OK

Deklo, kitna aasani se aapko Month by Month ka Sales mil sakta hai.

  • Sales ko rakhiye Gannit(Mathamatics) waale Area mai

Mujhe milta hai Sales ka pura Trend.

  • Pickles k naam rakhiye Column Feilds k Havale.

Abb aap dekh sakte hai Alag Alag Accaharo ka Sale kiss Mahine mai Jyaada hua hai:

Toh ye tha humaara Pivot Table ka Bohot he Bohot he Simple or Fast Video.

Aap 10-12 Mins mai sikh sakke, jo mene Saalo mai sikha hai.

Advanced Excel Webinar v3.0

2000+ Participants, different sessions, expertise training, Join Now for the Webinar. Learn Advanced Excel and Enhance your knowledge for better JOB (on 26th Jan 2018)