MS Excel Tutorial Beginners Course Part 2 – Sort, Filter, Find, Replace

ms-excel-in-hindi-full-tutorial-beginner-course-free-part-2

Sort and Filter in Hindi + Find and Replace Kya Hai Sikhe with examples

Yeh article humare free Excel course in Hindi ka 2nd part hai.

Iss tutorial mein hum aapko sort and filter in Hindi, Excel mein filter kaise lagate hai aur find and replace kya hai sikhayenge ek example ke sath. Example ki sheet aap FREE mein download kar sakte hai aur apne computer mein saath saath practice kar sakte hai.

Chaliye sikhte hai MS Excel in Hindi iss tutorial ke madhyam se.

Yeh MS Excel tutorial baaki excel courses se behtar hai kyonki:

  • Yeh hindi excel tutorial 100% FREE hai
  • Iss course mein humne aapko simple examples se excel ke baare mein bataya hai
  • Yeh course beginner level se shuru hoke advanced level tak hai

Aapko Beginner course part 2 ki zarurat hai agar:

  • Aapko MS excel ke basics thoda bahut aate hain aur aap data ko analyse karna excel ke madhyam se Hindi mein sikhna chahtein hain
  • Aap fresher hai aur excel ke baare mein zyada pata na hone se aapki naukri nahi lag rahi hai ya naukri ke liye aap excel sikhna chahte hai

Yeh MS Excel full course tutorial in Hindi ka second part hai. Isko complete karne ke baad aap baki 3 tutorial bhi kar sakte hain. Niche diye gaye links par click karke aap uss tutorial pe ja sakte hai –

MS Excel in Hindi full tutorial Beginner course part 1 – Agar excel bilkul nahi aati

MS Excel in Hindi full tutorial Advance course part 1 – MS Excel formulas basic level wale Hindi Mein

MS Excel in Hindi full tutorial Advance course part 2 – MS Excel advance formulas in Hindi


Chaliye dekhtein hain ki aap iss beginner part 2 mein kya sikhenge.

Beginner Part 2 MS Excel tutorial in Hindi mein hum sikhenge

Sort kaise karte hain in Hindi

Excel mein filter kaise lagate hain

Find and Replace kya hai

Inke baare me hum ek example ke through samjhenge.

Pehle hum dekhte hain ki excel mein Sort, Filter, Find aur Replace option hote kya hain.

SORT AND FILTER IN HINDI

Sort

Sort data ka option Home tab mein sabse right side hota hai, ye humne aapko apne last article mein bataya that. Sort ke feature se aap apne data ko text, numbers, ya colors ke basis par sort kar sakte hain. Excel mein data ko sort karke aap apne according jaankari pata kar sakte hain.

Filter

Excel mein kisi database mein se apne use ke according data ko extract ya nikalne ke liye Filter ka use kar sakte hain. Ye option bahut powerful option hai.


FIND AND REPLACE KYA HAI

Find

Excel mein Home tab mein Find and Select command ka use text ya number ko search karke select karne ke liye kiya ja sakta hai, ise directly aap Ctrl+F press karke bhi directly open kar sakte hain. Excel mein Find and Select bahut useful command hai.

Replace

Ye option aapko Home tab mein Find and Select ke under miljayega ya agar aap Ctrl+H press karenge toh bhi ye option screen par appear hojayega. Iss option ka use kisi bhi already present text ko replace karne ke liye kiya jata hai.


Chaliye ab ye sab cheezein detail mein ek example lekar samjhein kyonki example se sabse acchi tarah aapko saare features samajh aayenge.

Excel mein sort aur filter example se sikhe

Main example le rahi hun 10th class ke students ka marks record jise humne kuch aise enter kiya hai.


Iss data se hum kayi cheezein apply karenge jaise-

  • Merge and Center kaise kare – Merge and Center option
  • Alphabetically apne data ko kaise arrange kare- Sort option
  • Total kaise nikalein -SUM fn
  • Percentage kaise nikalein- Multiplication operator
  • Grading aur pass/fail kaise pata kare – IF and IFS function use
  • Kaise sirf males ya females ka data nikalein -Filter option
  • Kaise kisi bhi student ka data find kare – Find option
  • Kaise kisi student ke wrongly entered data ko correct kare – Replace option

Chaliye dekhein pehli jaankari-

Merge and Center kaise kare – Merge and Center option

Hum ismein Merge and Center ka use karke kuch cells ko merge kar sakte hai jaise ki maine kiya Subject Marks ko, kyonki saare subjects marks Subject Marks ke under hi aayenge. Dekhiye aise –


Aise hi aap ye S.no. aur Name ke liye bhi kar sakte hain.

Ye phir kuch aisa lagega-


Chaliye ab dekhein kaise hum apne data cleaning kar sakte hain.

Sort kaise karte hain

Agar aapko pata nahi hai ki SORT kya hota hai to yaha click kare

Alphabetically apne data ko kaise arrange kare -Sort option

Agar aap sab students ka data unke name ke according alphabetically arrange karna chahte hain toh iske liye aap pehle jiss data ko sort kana chahte hai vo select kare, ye selection aapko dhyan se karna hoga. Maine students ke names (excluding the heading Name) aur unke corresponding marks ke cells (excluding names of subject) ko select kiya hai kuch aise-

Ab selection karne ke baad jayein Home tab > Sort and Filter > Sort A to Z.

Ye finally kuch aisa dikhne lagega-

Total kaise nikalein -SUM function

Aap in saare marks ka total nikal sakte hai SUM function ka use karke.

SUM function apply karne ke liye aapko jiss cell mein TOTAL MARKS chahiye uss cell mein ye type karna hoga –

 =SUM(C10:G10) 

NOTE: Mere cells jinmein subject marks hain unki range C10 to G10 hai isliye upar mere function mein C10:G10 hain. Aapke cells ki range alag bhi ho sakti hai. Iskeliye sabse best hai ki aap =SUM(   likhar jiss range ka sum karna chahte hai usse select karlein, isse aapki required range apne aap function mein appear hojayegi jaise meri hui-

 =SUM(C10:G10


Ismein bracket close kijiye aise–

=SUM(C10:G10)

Ab Enter press kariye. Aapko apna total miljayega.

Ye function saare students ke liye apply karne ke liye, pehle aap apne total kiye hue cell ko select kare aur fir use niche jahan tak same formula apply karna hai drag kare, ye cheez Fill feature ki wajah se hoti hai. Aap niche jahan tak drag karenge vaha tak automatically ye formula fill feature se baaki cells mein apply hojayega.

Percentage kaise nikalein- Multiplication operator

Percentage nikalne se pehle main ek column max. marks ka add kar rahi hun aise-

Percentage nikalne ke liye aapko ye formula use karna hoga –

% of total marks scored = (Total marks scored/MAx. Marks) * 100                

Isse excel mein apply karne ke liye aap pehle jiss cell mein aapko percentage nikalni hai usmein operators ka use karke formula likhein jaise maine kiya-

=(H10/I10)*100             
 


Iske baad press Enter aur aapko apni percentage miljayegi.



Ise sab students ke liye apply karne ke liye jiss cell mein abhi aapne percentage ka formula apply kara usse niche drag karde jahan tak aapko ye formula similarly apply karna hai.

Grading aur pass/fail kaise pata kare – IF and IFS function ka use

GRADING

Grading karne ke liye aapko IFS ka formula use karna hoga.

Humne yahan apna grading system ye liya hai-

Ab chaliye dekhein ki kaise IFS function grading ke liye apply kare.

Iss function ko lagaane ke liye aapko jiss column mein grades chahiye usmein above criteria ke according ye statement likhni hogi-

NOTE: Ye function excel ke latest version mein hi hai. Aap apni columns ke according ye function lagayein jaise meri percentage J10 mein thi jiske according maine IFS function mein har jagah J10 cell ka use kara hai.

Ye function lagane par aapko ye milega-

Ye sab mein apply karne ke liye aap iss cell ko niche drag kar sakte hain dekhiye aise-


PASS/FAIL

Grading ke baad aapke liye pass/fail nikalna bahut easy hoga. Ye aap IF function ka use karke bahut easily kar sakte hain.

NOTE: Maine yahan apne data ko thoda manipulate kiya hai taaki aapko PASS/FAIL zyaada acchi tarah se pata chale kyonki humare pehle ke data ke according sab pass hain.

Maine pass/fail criteria ye liya hai-


Isko apply karne ke liye pehle aap uss cell par jayiye jahan aapko ye nikalna hai aur wahan ye formula likh dijiye-

Press Enter.

Isko aap niche drag karke saare students ke liye bhi nikal sakte hain.


Filter kaise lagate hain

Kaise sirf males ya females ka data nikalein – Filter option

Iske liye pehle aap ismein ek blank column Insert karlein GENDER ke liye aise-


Ab Insert karne ke baad aap ismein gender related data type karde.

Males/Females/Other ka data Filter karne ke liye aap apne data ko sahi se select kare aise –

Iske baad aap ye kare- Home tab > Sort and Filter > Filter.

Iske baad aap dekhenge ki aapki screen par headings ke paas small scroll buttons appear ho rahein honge aise-

Ye scroll buttons filter options hain. Ab jaise mujhe sirf females ka data chayiye ya females data filter karna hai toh aap GENDER ke side ke scroll par click karke sirf ‘Female’ par tick karde aur isse aapko sirf females ka data miljayega aise-


Female ka filter lagane ke baad aapko screen par bas females ka data dikhayi dega –

Aise hi aap failed students ke liye filter laga sakte hain, ye aap ‘PASS/FAIL’ ke scroll mein jakar select kar sakte hain aise-

Find aur Replace ka kya use hai

Kaise kisi student ka data find kare – Find option

Chaliye ab jaane ki kaise hum kisi bhi student ka data uske name ke through find kare. Yeh feature sabse zyada useful hota hai jab aapke paas bahut sara data ho aur aap khud saare data mein se aise hi dekhakar kisi specific cheez usko find na kar sake.

Iske liye pehle aap jayein Home tab > Find and Select > Find. Ya agar aap iskeliye Ctrl+F shortcut key ka use karenge toh bhi Find option ki window screen par appear hojayegi.


Find option par click karne se aapke screen par yeh window appear hogi –

‘Find what’ ke corresponding aap vo text type kariye jiske baare mein aapko janana hai. Maine yahan “Krish” type kiya hai. “Krish” type karte hi aap observe karenge ki aapki cell select directly uss cell par jismein “Krish” likha hai uss par chala jayegega aise-

Ab jab aapko “Krish” ke cell ki position milgayi hai toh usse related information easily dekh sakte hain.

Iss option se agar aapne kisi ka data wrong enter kiya hai toh uske name ko find karke aur fir uski details edit kar sakte hain.

Kaise kisi student ke wrongly entered data ko correct kare – Replace option

Chaliye ab dekhte hai iss article ka last topic.

Imagine kare ki aapne galti se kisi student ka name galat likh diya hai aur aapko usee replace karna hai, toh iskeliye aap Replace option use kar sakte hain.

For example, maine “Krishna” ka naam galti se Krish enter kardiya, toh main ise replace karne ke liye jaungi – Home tab > Find and Select > Replace . Ya aap iss option ke liye Ctrl+H shortcut key ka bhi use kar sakte hain.

Replace option par click karne ke baad ek small window appear hogi-

Ab ismein ‘Find what’ ke corresponding vo type karein jise aap replace karna chahte hai. ‘Replace with’ mein vo type kare jo corrected text hai ya jiss text se aap pehle wale ko replace karna chahte hain. Yahan main “Krish” ki jagah “Krishna” replace karna chahti hun. Ye option sabse zyada useful hota hai jab humne same spelling mistake kayi jagah ki ho, usse ‘Replace all’ karke aap ek saath change kar sakte hain.

Click Replace.

Iske baad aap dekhenge ki “Krish” change hojayega “Krishna” mein.

Aise hi aap aur bhi examples lekar apne data ko Sort aur Filter kar sakte hain, aur data ko clean aur useful way mein store kar sakte hain.

Agar aap chahein toh ye raw data aur final excel data sheet download kar sakte hain aur check kar sakte hain apne answers.
Sheets ka link niche diya hua hai.

Isse hum apna article finish karte hai, I hope ye article aapko helpful laga hoga aur ise padkar aapko excel ke kuch advance basics samajh aagaye honge. Aur agar aapko iss article ya Excel se related koi bhi query ya doubt hai to aap niche comments section mein comment zarur kare.


Yeh tutorial Hindi MS Excel full course in Hindi ka seconda part tha. Isko complete karne ke baad aap baki 3 kar sakte hain. Niche diye gaye links par click karke aap uss tutorial par ja sakte hain.

MS Excel in Hindi full tutorial Beginner course part 1 – Agar excel bilkul nahi aati

MS Excel in Hindi full tutorial Advance course part 1 – MS Excel formulas basic level wale Hindi Mein

MS Excel in Hindi full tutorial Advance course part 2 – MS Excel advance formulas in Hindi


Join Josh Skills Basic Computer Skills Course

Aaj kal computer ka zamana hai. Har voh kaam jisme pehle bahut time,effort aur money waste hota tha abh asani se computer pe kiya ja sakta hai. Computer ka istamaal aaj kal har taraf kiya jata hai. Basic computer skills ana aaj ke waqt mai bahat hi zaruri hai. Basic computer skills sikhna isliye zaruri hai take aap apne worklife ko easy aur efficient bana sako. Aur is computer technology ka wisely istamaal kar sako.

Is course mai aap Computer ke Basics pe gaur denge jaise:

  • Basics of a Computer system and it’s operation.
  • Internet Technology
  • Email and Communication
  • Use of Microsoft word, Microsoft Powerpoint and Microsoft Excel
  • Introduction to Advanced Computer skills and tools
  • Jobs that require computer skills

Toh aaj hi shuru kare apni learning journey Josh Skill ke courses ke sath.

Himanshi is an economics student at Delhi University. She likes to juggle around with numbers and take part in data-driven analytical projects. In her spare time, she likes to watch political comedies and is also an aspiring member of ’The Avengers’ ;)

LEAVE A REPLY

Please enter your comment!
Please enter your name here