Computer Training/ Day 9

Micro Soft Excel

Micro soft excel 2003 is developed to fulfill facilities for excel. Its main function is to calculate, create salary sheet, result sheet, billing system, balance sheet etc. It is include in MS-Office. It is called spreadsheet soft ware. It has 256 columns and 65536 rows as per sheet. It is developed by Microsoft Company of U.S.A.

1.     To Run Excel:

·        Click on star menu then click on Programs.

·        Click on Micro Soft Office then click on Micro Soft Excel.

Or

·        Click on Start menu and click on run command.

·        Type Excel and click on ok button. (Then Micro Soft Excel appears)

·         

2.     Billing System:

Kathmandu Mall Departmental Store Sundhara,Kathmandu Ph: 5487548
S.No.	Particular	Qty	Rate	Amount	Discount 10%	Net Amt	VAT 13%	Total Amt
1	Book	30	55
2	Copy	30	20
3	Computer	10	12000
4	Table	10	2000
5	C.P.U.	10	7000
6	Mouse	10	200
7	Keyboard	15	300
8	Chair	12	500
9	Monitor	5	10000
10	Photocopy	3	24000

Formula of Amount

=Qty*Rate 8 (Enter)

Formula of Discount

=Amount*10 %8 (Enter)

Formula of Net Amount

=Amount-Discount 8 (Enter)

Formula of VAT:

=Net Amt*13% 8 (Enter)

Formula of Total Amount

=Net Amt+VAT 8 (Enter)

3.     Salary Sheet

Question: A company paid T.A.D.A as per as his/her post. If they are G.M then they got 20% of their salary, if M.D, Accountant, Sales and Peon they got 15%, 12%, 10% and 5% respectively of their salary. If the company paid 5% Bonus and cuts their P. Fund of 10% then calculate their net salary.

S.No.	Name	Post	Salary	T.A.D.A.	Bonus	P. Fund	Net Salary
1	Namuna Thapa	G.M	15000
2	Mamata Kandel	M.D	18000
3	Kopila Dahal	Acc.	8000
4	Suprava Luitel	Sales	6000
5	Muna Kandel	Peon	4000

Formula of T.A.D.A

=if(Post=“G.M”,Salary*20%,if(Post=“M.D”,Salary*15%,if(Post=“Acc.”,Salary*12%,if(Post=“Sales”,Salary*10%,if(Post=“Peon”,Salary*5%)))))8 (Enter)

Formula of Bonus

=Salary*5%8 (Enter)

Formula of P. Fund

=Salary*10%8 (Enter)

Formula of Net Salary

=Salary+T.A.D.A+Bonus-P.Fund 8 (Enter)

4.     Telephone Bill

Question: If the customers have minimum 175 calls then they will be given Rs. 225 and other additional calls have Rs. 2 as per call. If the office took 10% VAT and 12% Tax then calculate the net amount.

Nepal Telecom Sundhara Kathmandu Ph: 4555566
S.No.	Name	Address	Calls	Amount	VAT	Tax	Net. Amt.
1	Namuna Thapa	Jamal	51
2	Mamata Kandel	Kalopul	850
3	Kopila Dahal	Tinkune	142
4	Suprava Luitel	Ason	540
5	Muna Kandel	Chabahil	186

Formula of Amount:

=if(Total Calls<=175,225,225+(Total Calls-175)*2) 8 (Enter)

Formula of VAT:

=Amount*10%8 (Enter)

Formula of Tax:

=(Amount+VAT)*10%8 (Enter)

Formula of Net Amount:

=Amount+VAT+Tax 8 (Enter) 

Read More

Excel Training/ Day 10

2.     Mark-Sheet

Brother’s English Boarding School Majhitar-7, Sindhuli Mark-Sheet (Class-9)
S.No.	Name	F.M.	P.M.	Eng.	Science	Math	Nep.	Social Studies	E.P.H.	Opt. 1st	Opt. 2nd	Total Marks	%	Result	Division
1	Namuna Thapa	100	40	95	75	86	95	81	88	88	78
2	Mamata Kandel	100	40	90	51	85	93	62	64	84	73
3	Kopila Dahal	100	40	75	35	48	87	77	64	72	89
4	Suprava Luitel	100	40	75	77	85	58	59	61	45	67
5	Muna Kandel	100	40	85	75	85	85	88	81	78	88

Formula of Total Marks:

=Sum(Eng.+Science+Math+Nep.+S.Studies+E.P.H.+Opt.1^st+Opt.2^nd) 8 (Enter)

Formula of Percentage:

=Total Marks/88 (Enter)

Formula of Result:

=If(min(Eng:Opt.2^nd)>=40,“Pass”,“Fail”) 8 (Enter)

Formula of Division:

=if(Result=“Fail”,“***”,if(Percentage>=80,“Distinction”,if(Percentage>=60,“First”,if(Percentage>=45,“Second”,“Third”)))) 8 (Enter)

 

Menu Bar:

1.     Work sheet: The worker is the basic tool for all the work you do with Excel. The number of worksheet is not more than 255.

2.     To Sat Print Area:

·        Select required data and click on file menu. Then click on print area.

·        Than click on set print area. Than check it by clicking on print preview.

3.     To clear print area:

·        Click on file menu than click on print area.

·        And click on clear print area.

4.     Delete: This option is used to delete particular cell, row or column.

·       Select the cursor in concern in concern place and click edit menu and click on delete.

·       Then choose shift cell left or shift cell up to delete data only.

·       Again choose entire row or entire column to delete row or column and click on ok.

5.     Delete sheet: This option is used to delete particular sheet.

·       Click on edit menu and click on delete sheet command.

·       Then click on ok button.   

6.     To Show or Hide Formula Bar:

·       Click on view menu and click on formula bar.

7.     To Insert Particular Row or Column:

·       Click on insert menu then click on row or column command.

·       Then click on row or column.

·       If you want to insert more than one row and column then select required row or column.

8.     Insert Worksheet:

·       Click on Insert menu and click on worksheet.

9.     Chart: This option is used to insert chart or graph.

Column Chart:

Countries	1990	1995	2000	2005
Nepal	1500	1800	2400	2500
India	1200	2500	2400	2200
U.S.A.	2500	2500	2400	2600
Japan	3500	3200	3100	3000

·       Have a data on cells.

·       Then select it and click on insert menu.

·       Then click on chart command.

·       Choose Chart type (Column)

·       And click on next button then rows or columns from series in list.

·       Click on next button. Then click on title button and type chart title, X-axis and Y-axis.

·       Click on next button and click on finish button.

Pie-Chart:

Category	Amount (Lakh)
Education	55
Agriculture	15
Health	4
Transportation	15
Others	20

·        Select the data then click on insert menu and click on chart command.

·        Then choose Pie-Chart and click on next button and choose columns from series.

·        Again click on next button. Click on legend and choose placement.

·        Click on data labels and choose series name, category, percent or values.

·        At last click on next button and click on finish button.

Line Chart:

Countries	1990	1995
Nepal	1500	1800
India	1200	2500
U.S.A.	2500	2500
Japan	3500	3200

·       Have a data on cells then select it and click on insert menu.

·       Then click on chart command.

·       Choose Chart type (Pie-Chart)

·       And click on next button then rows or columns from series in list.

·       Click on next button. Then click on title button and type chart title, X-axis and Y-axis.

·       Click on next button and click on finish button.

10.            To Insert Comment:

·       Have a data on data on cells then keep the cursor in concern place.

·       Click on Insert menu then click on Comment command.

·       Then type comment on comment box and click on outside of comment box.

11.            To show or hide all comments: Click on view menu and click on comment command.

12.            To Delete Comment:

·       Keep the cursor in comment cell and click on edit menu.

·       Then click on clear command and click on comment.

Format Menu:

1.     Cells: This option is used to format font, border, pattern, alignment and number of selected cells.

·        Select required cells and click on format menu and click on cells.

·        Then set font, border, pattern, alignment and number from clicking tab button.

·        If finish then click on ok button.

2.     To Modify Particular row Height:

·        Click on Format menu and click on row and click on height command.

·        Then type height and click on ok button.

3.     To Modify Particular Columns Width:

·        Click on format menu and click on column and click on width command.

·        Then type width and click on ok button.

4.     To Auto Fit Selection:

·        Have a complete data then select the data and click on format menu.

·        Then click on column and click on Auto Fit Selection.

5.     To Rename Particular Sheet Name:

·        Click on format menu and click on sheet command then click on Rename.

·        Then type any name and press enters key from keyboard.

6.     To Change sheet Background:

·        Click on format menu and click on sheet command and click on background.

·        And choose picture file and click on insert button.

7.     To Delete Background:

·        Click on format menu and click on sheet and click on delete background.

Tools Menu

1.     Protect Sheet: It is used to protect particular sheet. We can't modify or delete protected sheet.

·        Have a data and click on tools menu then click on Protection.

·        Click on protect sheet, then type password and click on ok button.

·        Again type same password and click on ok button.

2.     To Unprotect Sheet:

·        Click on tools menu and click on protection.

·        Click on unprotect Sheet then type password and click on ok button.

Data Menu

1.     Sort: This option is used to arrange the data from selected cells.

·        Select the data then click on data menu. And click on Sort command.

·        And choose field name from sort by box.

·        Then choose ascending or descending and click on ok button.

Window

1.     Arrange: This option is used to arrange the opened files

·        Open or create one more files. Then click on windows menu.

·        And click on arrange command.

·        Choose arrange style and click on ok button.

2.     Windows: Windows is one of the operating software. It has different version such as Windows 95, windows 98, windows 2003, windows XP etc. all this windows are developed by Microsoft Corporation of America. 

Read More