MS Office Excel Advance Tips & Tricks

 

Excel Advance Practical

1.      Excel Data लाई Shortcut तरिकाले Picture मा Change गर्ने तरिका। (Use Camera Features in Excel)

è Data Select गर्ने à Ctrl + C Press गर्ने à Alt, H, V, U Press गर्ने।

2.      Auto Sum (Alt + =, Enter)

3.      Separate Name & Number

Data Entry Data
9813172555
Aman
9855663388
Kishore
9745665923
Laxmi
9868554892
Kamal
9847145236
Hari

S.N.	Name	Contact

               

èSelect all data à Ctrl + G à Go toà Special à Constantà Text à Tick à OKà Ctrl + C à Paste

èSelect all data à Ctrl + G à Go toà Special à Constantà Number à Tick à OKà Ctrl + C à Paste

4.      Blank Cell  मा सबै डाटाहरुको टोटल एकैचोटी निकाल्ने सर्टकर्ट तरिका।

Name	Hindi	Math	English	GK	Computer	Total	% tage
Krish	78	76	45	56	54	309	61.8
	54	65	34	65	34	252	50.4
	65	67	76	54	56	318	63.6
Total
Krishna	54	23	45	65	76	263	52.6
	53	56	76	86	65	336	67.2
	65	67	76	54	56	318	63.6
Total

 

è Select All data à Ctrl + G à Special à Blanks à OK à Alt + = à Enter

5.      लामो Number हरु Type गर्दा आउने समस्या बाट बच्ने उपाय

è नम्बर टाईप गर्न पर्ने Cell हरु सेलेक्ट गर्ने à Number Format मा गएर Number सेलेक्ट गर्ने à त्यसपछी टाईप गर्ने

6.      Date मा 01-05-2024 यो फर्म्याटमा नम्बर राख्‍न के गर्ने?

è Select Cell à Click Number Format à Custom à Type DD-MM-YYYY à Enter

7.      नाम माथी Click गर्दा Photo आउने बनाउने तरिका?

è नामको Cell मा Right Click गर्ने à Insert Comment मा Click गर्ने à Comment Box मा Right Click गर्ने à Format Comment à Colors and lines à Color à Fill Effect à Picture à Select Picture à आफ्नो फोटो छान्ने à insert गर्ने à OK

8.      Age पत्ता लगाउने तरिका

S.N.	Name	DOB	Year	Month	Day
1.	Laxman	2005-10-12
2.	Bihal	2001-01-15
3.	Melina	1994-06-07

 

è ‍Year =Datedif(Date of birth select, Today(),"Y") à Enter

è ‍Month =Text(Date of birth select, "MMMM") à Enter

è Day  =Text(Date of birth select, "DDDD") à Enter

9.      Increment अथवा Decrement %  निकाल्ने तरिका

Name	Sales 2023	Sales 2024	Increment %
Mobile	30000	40000
Computer	40000	45000

 

è  Increment % = (New –Old)/new 100

è  Decrement % = (Old –New)/Old

10.  First Name  र Last Name जोड्ने तरिका।

S.N.	First Name	Last Name	Full Name
1.	Rabi Kumar	Barma
2.	Kiran	Kasera
3.	Rama Devi	Pradhan

‍

è =Select First Name&"Space"&Select Last Name à Enter

11.  Excel मा Spinner बनाउने तरिका  

è Developer Option मा जाने à Insert मा जाने à Spinner Choose गर्ने à Properties मा जाने à Cell link मा आफुले देखाउने Cell Choose गर्ने।

12.  Department अनुसार नामहरु छुट्याउने तरिका। (Google Sheet)

Department
IT	HR	Finance

Name	Department
Sachin	HR
Kamal	IT
Aditya	Finance
Laxman	HR
Niraj	Finance
Harish	IT
Lakhan	Finance

è =filter(सम्पुर्ण नामहरु लाई Select गर्ने, सबै Department हरुलाई Select गर्ने दुबैलाई F4 बटन दबाएर Fix गर्ने=पहिलो डिपार्टमेन्ट Select गर्ने)à Enter Press गर्ने à drag गरेर Formula Copy गर्ने।

13.  Working Hour निकाल्ने तरिका

Name	In Time	Out Time	Total Time
Kamal	12:57 PM	5:56 PM
Hari	1:18 AM	8:38 PM
Gita	8:02 PM	3:54 AM

 è =mod(Out Time- In Time, 1) à Enter

14.  Excel Sheet मा 12 महिनाको नाम राख्‍ने तरिका

è January देखी December सम्म नाम Type गर्ने à Select गरेर Insert मा जाने àPivot Table मा जाने à Month वाला Option लाई समातेर Filter मा राख्‍ने à Pivot Table Option मा गएर Show Report Filter Page मा Click गर्ने à OK गर्ने।

15.  Shortcut  तरिकाले heading हरुलाई Freeze गर्ने तरिका।

è Heading भन्दा तलको Cell लाई Select गर्ने à Alt, W, FF Press गर्ने  (Unfreeze को लागी पनी यहि प्रक्रिया गर्ने)

16.  Shortcut Key बाट Column हरु Change गर्ने तरिका।

è पहिलो Column लाई Ctrl + X press गर्ने à दोस्रो Column लाई Ctrl +Shift + = Press गर्ने

17.  Excel Prank

è सम्पुर्ण Cell हरुलाई Select गर्ने à Number Format मा Click गर्ने à Custom मा जाने à General लाई हटायर à ;;;"Moye Moye"

18.  Free Website for Excel Formula (gptexcel.uk)

19.  Auto Serial Number निकाल्ने तरिका

è ‍=if(दोस्रो Cell लाई Select गर्ने ="","",Row(1:1)) àEnter à जहाँ सम्म S.N. चाहिएको छ त्यहा सम्म Drag गर्ने।

20.  Over Time Calculation

Name	In Time	Out Time	Total Hours	Over Time
Kamal	12:57 PM	5:56 PM
Hari	1:18 AM	8:38 PM
Gita	8:02 PM	3:54 AM

 è Total Hour =mod(Out Time- In Time, 1) à Enter

è Over Time =Total Hours- Time(8,0,0) àEnter

21.  QR coder Generator in Excel (Excel Version 2016 and over)

è Open Excel à Insert menu à Add-insà Get- add ins à QR office à Add à Qr generator open हुन्छ à link Copy गरेर राख्‍ने à Insert मा Click गर्ने।

22.  एउटा Row मा Color र अर्को Row मा नर्मल बनाउने तरिका

è सम्पुर्ण डाटा Select गर्ने à Conditional Formatting मा जाने à New Rule मा जाने àUse a formula to determine which cells to format मा Click गर्ने à Formula Type गर्ने (=mod(Row(),2)=1) à Format मा जाने à Color Choose गर्ने à Ok गर्ने।

23.  Select गरेको डाटा मा Border हाल्न।à Alt, H, B, A

24.  Select गरेको डाटा मा Border हटाउन।à Alt, H, B, N

25.  Excel मा Superscript र Subscript Use गर्ने तरिका

è Superscript गर्नुपर्ने Text Select गर्ने à Ctrl + 1 Press गर्ने à Superscript Select गर्ने à Ok गर्ने।

è Subscript गर्नुपर्ने Text Select गर्ने à Ctrl + 1 Press गर्ने à Subscript Select गर्ने à Ok गर्ने।

26.  पुरै Column अथवा Row Select गर्ने तरिका।

è Column: Ctrl + Shift + Down Arrow

è Row: Ctrl + Shift + Right Arrow

27.  कुनै Row अथवा Column Delete गर्ने तरिका

è  Ctrl +   Press गर्ने à आवश्यकता अनुसार Entire Column अथवा Entire Row Select गर्ने à Ok गर्ने

28.  Data लाई Table मा राख्‍ने Shortcut तरिका।

è Data लाई Select गर्ने à Ctrl + T Press गर्ने à Enter Press गर्ने।

29.  Total Sale निकाल्ने तरिका (Lookup formula)

Name	Jan	Feb	Mar
Robin	1000	2300	500
Krish	4000	6000	5500
Lokesh	5000	5400	7000

Name	Total Sale

 

 è =Lookup(Name को तलको Cell Select गर्ने, सम्पुर्ण नाम Select गर्ने, Jan को Data Select गर्ने + Feb को Data Select गर्ने + Marc को Data Select गर्ने )à Enter Press गर्ने।

30.  Data को पछाडी % को चिन्ह राख्‍ने तरिका

Student Name	Marks %
Aman	48
Golu	90
Nishant	62
Binod	54

 

 

 

 

è कुनै एउटा Cell मा 1% Type गर्ने à त्यसलाई Copy गर्ने à सम्पुर्ण नम्बर Select गर्ने àRight Click गर्ने à Paste Special option मा जाने à Multiply मा Click गर्ने à Ok गर्ने

 31.  Excel मा Select गरेको डाटालाई मात्र PDF मा convert गर्ने तरिका

Student Name	Marks %
Aman	48
Golu	90
Nishant	62
Binod	54

 

 

 

è डाटा Select गर्ने à File Menu जाने à Save as मा जाने à Location choose गर्ने à Save as type मा Pdf choose   गर्ने à Option मा Click गर्ने à Selection मा Click  गर्ने à Ok गर्ने।

 32.  Dropdown List बनाउने तरिका

Courses

MS Excel

MS Word

MS PowerPoint

Photoshop

Tally

S.N.	Student Name	Course	Batch
1	Aditya		2
2	Laxman		3
3	Aakash		2
4	Purnima		1
5	Rekha		3

 è Dropdown list राख्‍नु पर्ने सबै Cell Select गर्ने à Data Menu मा जाने à Data Validation मा जाने à List मा जाने à Source भित्र Course Type गरेका सम्पुर्ण Cell Select गर्ने à Ok गर्ने।

33.  Color Name अनुसार Cell Color Change गर्ने तरिका

Item	Size	Color	Quantity
T-Shirt	L	Red	10
Shirt	M	Black	20
T-Shirt	M	Red	25
Shirt	XL	Green	15
T-Shirt	XXL	Black	30

 è Color Name को Dropdown List बनाउने à Color का सबै Cell हरुलाई Select गर्ने à Home Menu मा जाने à Conditional Formatting मा जाने à Highlight Cell Rules मा जाने à Text that contains मा जाने à एउटा color Choose गर्ने à दाया तिर Dropdown menu मा गएर Custom Format मा Click गर्ने à Fill मा जाने à Color Choose गर्ने à Ok गर्ने à Ok गर्ने

è यसरि नै प्रत्येक Color को लागी Formatting गर्ने

 34.  Column Auto Adjustment on गर्ने तरिका।

è चाहिएको Sheet को नाममा Right Click गर्ने à View Code मा जाने àWorksheet option Choose गर्ने à Cells.EntireCoulumn.Autofit  type गर्ने àTab Press गर्ने àWindow Close गर्ने।

35.  कुनै पनी फाइलको Duplicate Copy Open गर्ने तरिका।

è File Menu मा जाने à Open मा जाने àRecent मा जाने à Recent workbook हरु देखिन्छन à चाहिएको Workbook मा Right Click गर्ने à Open a Copy मा Click गर्ने।

36.  Theme Color Change गर्ने तरिका

è File Menu मा जाने à Account मा जाने à Office Theme मा जाने à चाहिएको Color  Choose गर्ने।

37.   अगाडिका केहि Number हरु Hide गर्ने तरिका। (Using Flash Fill   Office Version 2013 भन्दा माथी सपोर्ट गर्छ )

S.N.	Name	Pan No.	Pan No.
1	Aditya	985623147	*****3147
2	Laxman	985623148
3	Aakash	985623149
4	Purnima	985623150

 

è Cell मा गएर अगाडी ४/५ वटा *(Star) चिन्ह Type गर्ने र पछाडी कति अंकहरु देखाउने हो तिनिहरुलाई टाईप गर्ने à Enter Press गर्ने à Ctrl + E Press गर्ने à बिचको Column Hide गर्ने।

38.  धेरै ठुलो List मा Text को अगाडि एउटै किसिमसको Text Add गर्ने सजिलो तरिका।

S.N.	Name	Name
1	Aditya	Mr. Aditya
2	Laxman	Mr. Laxman
3	Aakash	Mr. Aakash
4	Puran	Mr. Puran

 è ="Mr."&"space"&Select name à Enter

39.  Excel Practice को लागी ठुलो Data Generate गर्ने तरिका

Google मा जाने à Mockaroo search गर्ने à First Website मा Click गर्ने à Format मा Excel Select गर्ने à Row मा कति Row को Data चाहिएको छ Type गर्ने à Generate Data मा Click गर्ने à Data बनेर Download हुन्छ।

40.  Excel मा ठुलो Data Hide र Show गर्ने सजिलो तरिका ।

è Hide गर्नु पर्ने Column अथवा Row हरु लाई Select गर्ने à Data Menu मा जाने à Group Option मा जाने  Click गर्ने à अब heading को माथी – (Minus) को चिन्ह देखिन्छ à त्यसमा Click गर्ने Data Hide हुन्छ। à Data Hide भएपछी माथी + को चिन्ह देखिन्छ à त्यसमा Click गरेर फेरी Data Show गर्न सकिन्छ।

41.  Multiple Sheet मा Same Data एकैचोटी Type गर्ने तरिका।

è कुन –कुन Sheet हरुमा Data Entry गर्नुपर्ने हो Ctrl press गरेर Select गर्ने à त्यसपछि Data Type गर्ने à अब Data सबै Sheet हरुमा आउँछ।

42.  Data बाट Largest Number र Second Largest Number अथवा Third Largest Number निकाल्ने तरिका।

Student Name	Marks %
Aman	48
Golu	90
Nishant	62
Binod	54
Reman	68
Laxmi	29
Gita	93

 

 

 

 

 

 è Large Number को लागी =large(Select All Number) àEnter

è Second Large Number को लागी =large(Select All Number,2) à Enter

è Third Large Number को लागी =large(Select All Number,3) à Enter

43.  Excel मा काम गर्दा यदि तपाई टेवल जस्ता देखिने लाईन (Gridline) हटाउने तरिका।

è View Menu मा जाने à Gridlines को अगाडि को Tick mark हटाउने à अब तपाईको अगाडी Simple Paper देखिन्छ।

44.  Excel मा अनावश्यक Space हटाउने तरिका

è Data Select गर्ने à Ctrl + H Press गर्ने à Find मा ~(tilde) Sing press गर्ने र Space दिने à Replace All मा Click गर्ने à Ok गर्ने à अब अनावश्यक Space हट्दछ।

45.  Age Calculation गर्ने New Trick

S.N.	Name	DOB	Age
4.	Laxman	2001-10-12
5.	Bihal	2005-01-15
6.	Melina	2015-06-07

 

è =Yearfrac(Select DOB,Today()) à Enter

46.  Excel मा कुनै पनी Text को Barcode बनाउने तरिका।

è ‍="*"&Select Text&"*" à Enter à Formula लगाएको Cell Select गर्ने à Font मा गएर Librebarcode39 नामको Font Choose गर्ने à अब Barcode तयार हुन्छ। यदि यो font तपाईसंग छैन भने Google मा गएर Librebarcode39 Download Search गरेर सजिलै Download गर्न सक्नु हुनेछ।

47.  Data लाई Image मा Convert गर्ने तरिका ।

è Data Select गर्ने à Copy गर्न à Paste Special मा जाने à Picture Choose गर्ने।

48.  Rank निकाल्ने तरिका

Student Name	Marks %	Rank
Aman	48
Golu	90
Nishant	62
Binod	54
Reman	68
Laxmi	29
Gita	93

 

 

 

 

 

è =Rank(पहिलो विद्यार्थिको mark Select गर्ने, सबै विद्यार्थिको Mark Select गर्ने F4 Button Press गर्ने) à Enter

49.  Stock लाई Filter गर्ने तरिका। (Google Sheet को प्रयोग गरेर)

Inventory	Stock
Pen drive	10
Hard Disk	0
Keyboard	15
Mouse	5
Monitor	0

In Stock	Out Stock

 

 

è In Stock =Filter(all Inventory Select ग्ने F4 Press गर्ने, All Stock Select गर्ने F4 Press गर्ने >0) à Enter

è Out Stock =Filter(all Inventory Select ग्ने F4 Press गर्ने, All Stock Select गर्ने F4 Press गर्ने <=0) à Enter

50.  Excel मा Active Cell हरुलाई highlight गर्ने तरिका।

è Data Select गर्ने à Conditional Formatting मा जाने àNew Rule मा जाने à Use a Formula to determine which cells to format मा Click गर्ने à =Cell("address",A2)=Cell("address") Format मा जाने Color Choose गर्ने à Ok  गर्ने à Sheet मा जाने àRight Click गर्ने à View Code मा जाने à Worksheet Choose गर्ने à Target.Calculate Type गर्ने à Window Close गर्ने à Sheet मा आएर हेर्न सक्छौ। à जहा Click गर्दा त्यहा highlight हुन्छ।