Ms Excel

Introduction:

Microsoft Company developed this program, so it is known as Microsoft Excel. It is a spreadsheet program where we can insert data, process them, sort them, filter them and create chart from the data etc., in this program we can perform various kinds of Mathematical, financial, statistical, engineering calculations. Using this program we can create bill, voucher, salary sheet, income& expenses statement, mark sheet, purchase book, sales book, balance sheet etc.

            The file of Ms-Excel is called workbook and its extension is, XLS.

Worksheet: - The large working area of a workbook is called worksheet. There are 65,536 rows and

256 columns in a worksheet.

Cell: - The intersection between row and column is called Cell. Each cell contains 32000

Characters.

To start Microsoft Excel 2000/XP

C                 Click on Start cotton.

C                 Click on Programs.

C                 Click on Ms Excel.

                           Or

C                             Click on Start

C                             Click on Run

C                             Then a dialog box appears

C                             Type Excel

C                             Click on OK.

TO CRATE A NEW WORKBOOK.

Þ               Click on file menu

Þ               Click on new

Þ               The a dialog box will appears

Þ               To create a new, blank workbook, click a General tab.

Þ               Choose Workbook icon

Þ               Click on OK button.

TO SAVE A WORKBOOK.

 Click on File menu.

Þ      Click on Save command

Þ      In the Save in list, select the drive and folder where you want to save the workbook.

Þ      [If you want to save the workbook in a new folder, click on Create New folder]

Þ      In the File name box, type a name fir the workbook.

TO CLOSE A WORKBOOK.

Þ      Click on File menu.

Þ      Click on Close command.

[To close all open workbooks without exiting the program, hold down SHIFT and click close all on the File menu.]

TO SAVE A COPY OF WORKBOOK.

Þ      Open the workbook you want to make a copy of.

Þ      Click on file menu.

Þ      Click on Save as.

Þ      In the File name box, type a new name of the workbook

Þ      Click Save.

TO OPEN A WORKBOOK.

Þ      Click on file menu.

Þ      Click on open command.

Þ      In the Look in list, click the drive, folder or location that contains the workbook you want to open and then locate a double-click the folder the contains the workbook.

Þ      Select the required workbook name.

Þ      Click on Open.

Þ      Double click the workbook you want to open.

TO PROTECT WORKBOOK BY PASSWORD

Þ      First open the workbook.

Þ      Click on File menu.

Þ      Click on Save as command.

Þ      Then a dialog box will appear.

Þ      Click on Tools of dialog box and click on General option.

Þ      Type the password in Password to open box.

Þ      Click on ok bottom.

Þ      Then a confirm password box will appear.

Þ      Type the same password Modify again.

Þ      Click on ok button.

Þ      Click on save button.

USING PAGE SETUP COMMAND.

This command is used to set margins, paper source, paper size, page orientation, and other layout options for the active file.

Þ      First open the workbook of which you want to set page.

Þ      Click on File menu.

Þ      Click on Page Setup command.

Þ      Then a dialog box will appear.

Þ      Click on page tab.

Þ      Choose the required orientation. (Portrait or landscape)

Þ      Choose any paper size.

Þ      Click on margin tab.

Þ      Set the required margin for Top, Bottom, and Left, Right, Header& footer.

Þ      Choose other required options.

Þ      Click on OK button.

TO SEE THE PRINT PREVIEW OF WORKSHEET.

Þ      Click on file menu.

Þ      Click on Print Preview command.

Þ      Now you can see the preview of your worksheet.

Þ      Click on Next button to see the next page of sheet.

Þ      Click on Previous button to see the previous page.

Þ      Click on zoom button to reduce or enlarge the page size.

Þ      Click on setup button to open the setup dialog box.

Þ      Click on Margins tab to set the margin.

Þ       Click on close button to close the preview.

TO SET PRINT AREA.

Þ      Select the required part of sheet which you want to set as print area.

Þ      Click on file menu.

Þ      Click on Print area command.

Þ      Choose Set print area.

[To clear print area click on File menu, choose print area and click on clear print area.]

TO PRINT A WORKSHEET.

Þ      Open the worksheet which you want to print.

Þ      Click on File menu.

Þ      Click on Print command

Þ      Then a dialog box will appear.

Þ      Choose the print name from Name box.

Þ      Choose any option from Print range criteria. [All, Page].

Þ      Choose any option from Print what criteria. [Selection, entire workbook etc.]

Þ      Choose the required number of copies to print.

Þ      Click on other required options and Click on OK button.

TO SELECT ENTIRE ROW.

Move the mouse pointer over the row number which you want to select.

Press the mouse button once.

TO SELECT ENTIRE COLUMN.

Move the mouse pointer over the column label which you want to select.

Press the mouse button once.

TO SELECT THE ENTIRE WORKSHEET.

Move the mouse pointer over the top left corner of worksheet.

Press the mouse button once.

USING CUT/ COPY/PASTE.

Þ      Select the required cells which you want to copy or move.

Þ      Click on Edit menu.

Þ      Click on copy or Cut command as required

Þ      Then place the active cell at the required cell

Þ      Click on Edit menu. .

Þ      Click on Paste Command

[You can also use the shortcuts to copy, cut and paste as in Ms Word such as Ctrl+c, Ctrl+x, Ctrl+v]

TO FILL SERIES OF NUMBERS AND DATA.

To Fill Numbers.

Þ      Put the active cell at the required cell.

Þ      Type First and Second number in First and Second cell.

Þ      Then select the both cell.

Þ      Move the mouse pointer over the bottom right corner of second cell.

Þ      Drag the fill handle as required.

To fill date

Þ      Put the active cell at the required cell.

Þ      Type Sunday or January in First cell.

Þ      Then move the mouse pointer over the bottom right corner of first cell.

Þ      Drag the fill handle.

USING CLEAR COMMAND

Þ      Select the required cells which you want to clear.

Þ      Click on Edit menu.

Þ      Click on Clear command.

Þ      Then click on All option to clear everything with formatting, contents etc,

Þ      Click on formats option to clear only the formats.

Þ      Click on Contents option to clear only the contents.

Þ      Click on Comments option to clear the comments only.

TO DELETE CELLS, ROWS, COLUMNS

Þ      Select the required cells or rows or columns which you want to delete.

Þ      Click on Edit menu.

Þ      Click on Delete command.

Þ      If you have selected cells then a dialog box appears.

Þ      Choose any option. [Shift cells left, shift cells up etc.]

Þ      Click on Ok.

TO DELETE WORKSHEET.

Þ      Select the required sheet name which you want to delete.

Þ      Click on Edit Menu,

Þ      Click on Delete sheet command.

Þ      Then a message box will appear.

Þ      click on ok

TO MOVE OR COPY SHEETS.

Þ      Select the required sheet name.

Þ      Click on Edit menu.

Þ      Click on Move or Copy sheet command.

Þ      Then a dialog box will appear

Þ      Choose any option from before sheet list.

[If you want to create a copy of the selected sheet click create a copy option]

Þ      Click on OK.

USING FIND AND REPLACE COMMAND.

Find:

Þ      Click on Edit menu.

Þ      Click on Find Command

Þ      Then a dialog box will appear.

Þ      Type a required text or data in find what box.

Þ      Click on Find next button.

Replace:

Þ      Click on Edit menu

Þ      Click on Replace command

Þ      Then a dialog box will appear.

Þ      Type the required text or data in find what box which you want to replace.

Þ      Type the replacing text or data in Replace with box.

Þ      Then click on Find next.

Þ      Click on Replace or Replace all bottom as required.

USING GO TO COMMAND

Þ      Click Edit menu.

Þ      Click on Go to command.

Þ      Then a dialog box will appear.

Þ      Type the cell name in reference box.

Þ      Click on OK.

TO SHOW OR HIDE FORMULA BAR.

Þ      Click on View menu.

Þ      Click on Formula bar Command to show or hide.

TO ADD HEADER AND FOOTER.

Þ      Click on View menu.

Þ      Click on Header & Footer command.

Þ      Then a dialog box will appear.

Þ      Click on Custom header.

Þ      Put the cursor at the required section (left, center, right).

Þ      Type the header Name.

Þ      Click on Ok.

Þ      Then click on Custom Footer button.

Þ      Type the footer Name in the required section.

Þ      [You can also insert page number, date, time, filename etc as header & footer]

Þ      Click on OK.

TO VIEW THE WORKSHEET IN FULL SCREEN

Þ      Click on View menu.

Þ      Click on Full screen command.

Þ      Now your worksheet will appear in full screen.

Þ      To close full screen, click on Close full screen tool.

TO CHANGE THE VIEW OF WORKSHEET.

Þ      Click on View menu.

Þ      Click on Zoom command.

Þ      Then a dialog box will appear.

Þ      Choose any one option. (200%, 100%, 50%, 75% ect.)

Þ      Click on OK.

TO INSERT CELLS

Þ      Select the required cells where you want to insert new blank cells.

Þ      Click on Insert menu.

Þ      Click on Cells command.

Þ      Then a dialog box will appear.

Þ      Choose any one option. (Shift cells right, shift cells down & insert entire Rows, Columns).

Þ      Click on Ok

TO INSERT ROWS

Þ      Select the required rows where you want to insert new rows.

Þ      Click on Insert menu.

Þ      Click on Rows command

TO INSERT COLUMNS

Þ      Select the required columns where you want to insert new columns.

Þ      Click on Insert menu.

Þ      Click on Columns command.

TO INSERT WORKSHEET.

Þ      Select the sheet name where you want to insert new worksheet.

Þ      Click on Insert menu.

Þ      Click on Work sheet command.

            [Now a new worksheet will appear in the sheet tabs]

TO INSERT CHART

Þ      First type the data for chart.

Þ      Select the data range.

Þ      Click on insert menu.

Þ      Click on Chart command.

Þ      Then a chart wizard box will appear.

Þ      Choose any chart type and chart subtype form standard type tab. [For more chart types click on Custom types tab]

Þ      Click on Next button.

Þ      Choose the required option from series in [Rows or Columns]

Þ      Click on Next button.

Þ      Type the chart title in Chart title box.

Þ      Type the X and Y category titles.

Þ      Click on Axes tab and choose the required options.

Þ      Click on Gridlines tab and choose the required options.

Þ      Click on Legend and choose any option.

Þ      Click on Data labels and Data table tab and choose the required options.

Þ      Click on Next button.

Þ      Now choose the required location to place chart.

Þ      Click on Finish button.

[If you want to change the chart type, chart options, chart location etc then select the chart, click on Chart menu and choose the required options.]

Using Formula

Þ      Put the active cell at the required cell where you want to use formula.

Þ      Type (=) equal sign, [Formula always begins with (=) sign]

Þ      Enter the formula with cell references and operators

Þ      Press Enter Key

Functions Operators:-

+          Addition

-           Subtraction

*           Multiplication.

/           Division

%         Percentage.

=          Equal comparison.

<          Less than

>          Greater than.

<=        Less than or equal to

>=        Greater than or equal to

<>        Is not equal to

and/ Or/ Not Logic.

Using Function wizard

Þ      Place the cursor in cell where you want to insert Function.

Þ      Click on Insert menu.

Þ      Click on Function Command.

Þ      Then a function wizard box will appear.

Þ      Select the required Function category.

Þ      Choose any Function name. (Sum, average, min, max etc.)

Þ      Click on Ok.

Þ      Then a dialog box will appear.

Þ      Type the cell references in Number box.

Þ      Click on Ok button.

To insert comment.

Þ      Select the required cells where you want to insert comment.

Þ      Click on Insert menu.

Þ      Click on Comment command.

Þ      Then a comment box appears.

Þ      Type the required comments.

To show or Hide comments.

Þ      Select the required cells that contains comment.

Þ      Press opposite mouse button.

Þ      Choose show comment/Hide comment.

To Edit or Delete comment.

Þ      Select the required cells that contains comments.

Þ      Press opposite mouse button.

Þ      Choose Edit comments or Delete comments.

To insert Clipart.

Þ     Click on Insert menu.

Þ     Click on Picture command.

Þ     Then a Clipart box will appear.

Þ     Click any category.

Þ     Choose any picture and press the mouse button.

Þ     Choose Insert clip

Þ     Then close the clipart box.

To insert Auto shapes.

Þ     Click on Insert menu.

Þ     Click on picture command.

Þ     Click on Auto shapes.

Þ     Then an auto shapes toolbar will appear.

Þ     Choose any shape from toolbar.

Þ     Press the mouse button and drag it to draw auto shapes.

To insert word art.

Þ     Click on Insert menu.

Þ     Click on Picture command.

Þ     Click on Word art.

Þ     Then a word art style box will appear.

Þ     Choose any style.

Þ     Click on Ok.

Þ     Now type the required text.

Þ     Click on Ok.

To insert Object (Paint brush picture)

Þ     Click on Insert menu.

Þ     Click on Object command.

Þ     Then a dialog box will appear.

Þ     Choose Paint brush picture.

Þ     Click on Ok.

Þ     Now draw any picture you like.

To insert hyperlink.

Þ     Select the required cells or object where you want to insert hyperlink.

Þ     Click on Insert menu.

Þ     Click on hyperlink command.

Þ     Then a dialog box will appear.

Þ     Click on File of Browser for option.

Þ     Choose the required file.

Þ     Click on Ok.

Þ     Again click on Ok.

To format numbers.

Þ     Select the required cells.

Þ     Click on Format menu.

Þ     Click on Cells command.

Þ     Then a dialog box will appear.

Þ     Click on Number tab.

Þ     Choose Number category.

Þ     Choose the required decimal places.

Þ     If needed choose the Use thousand separators option. Click on Ok.

To change the number in currency format.

Þ     Select the required cells.

Þ     Click on Format menu.

Þ     Click on Cells command.

Þ     Then a dialog box will appear.

Þ     Click on Number tab.

Þ     Choose Currency tab.

Þ     Choose the required decimal places.

Þ     Choose any currency symbol.

Þ     Click on OK.

To change the number in Percentage format.

Þ     Select the required cells.

Þ     Click on Format menu.

Þ     Click on Cells command.

Þ     Then a dialog box will appear.

Þ     Click on Number tab.

Þ     Click on Percentage tab.

Þ     Choose the required decimal places.

Þ     Click on OK.

To merge and center data

Þ     First type the long text in a single cell.

Þ     Select the required cell range.

Þ     Click on Format menu.

Þ     Click on Cells command.

Þ     Then a dialog box will appear.

Þ     Click on Alignment tab.

Þ     Choose center from Horizontal and Vertical alignment.

Þ     Click the three option of text control criteria. (Wrap text, shrink  to fit, merge cells)

Þ     Click on Ok.

[Put the Cursor at the required text and op pressing spacebar unit the text comes to the next line.]

To change the data orientation.

Þ     Select the required cells.

Þ     Click on Format menu.

Þ     Click on Cells command.

Þ     Then click on Alignment tab.

Þ     Choose the required degrees.

Þ     Click on Ok.

To format font.

Þ     Select the required cells.

Þ     Click on Format menu.

Þ     Click on Cells command.

Þ     Then click on Font command.

Þ     Choose the required font, font style size, font color, underline style, effects.

Þ     Click on Ok.

To apply borders & patterns.

1. Select the required cells.
2. Click on Format menu.
3. Click on Cells command.
4. Then click on Borders tab.
5. Choose any boarder style and Color.
6. Click on Outline and inside option.
7. Then click on Patterns tab.
8. Choose any color.
9. Click on OK

To format row

1. Select the required row.
2. Click on Format menu.
3. Click on Row command.
4. Then choose the required options, (Height, Auto fit, hide, unhide)

To format column.

1. Select the required columns.
2. Click on Format menu.
3. Click on column command.
4. Then choose the required option. (Width Auto fit selection, hide, un hided)

To Rename Sheet.

1. Select the required sheet name which you want to rename.
2. Click on Format menu.
3. Click on Sheet command.
4. Click on rename command.
5. Type the name for sheet.
6. Press Enter key.

To Hide/Unhide sheet.

1. Select the required sheet name.
2. Click on format menu.
3. Click on Sheet command.
4. Click on Hide/Unhide.

To change the background of sheet.

1. Click on format menu.
2. Click on Sheet command.
3. Then click on Background.
4. Now a dialog box will appear.
5. Choose any background file.
6. Click on Insert button

To delete background.

1. Click on Format menu.
2. Click on Sheet command.
3. Click on Delete background.

To Auto format cells.

1. Select the required cells.
2. Click on Format menu.
3. Click on Auto format command.
4. Then a dialog box will appear.
5. Choose any format style.
6. Click  on Ok.

Using Conditional formatting command

1. Select the required cells.
2. Click on format menu.
3. Click on Conditional formatting command.
4. Then a dialog box will appear.
5. Choose any condition. (Cell value is less than, greater than, equals to etc.)
6. Type the value.
7. Click on Format menu.
8. Choose any Formatting.
9. Click on Ok.
10. If you want to add other conditions also click on add button and set the other condition.
11. Click on Ok.

Using Auto correct command.

1. Click on Tools menu.
2. Click on Auto correct command.
3. Then a dialog box will appear.
4. Click the required options.
5. Type the common mistakes in Replace box.
6. Click on Add button.
7. Click on Ok.

To protect worksheet.

1. Click the worksheet name which you want to protect.
2. Click on tools menu.
3. Click on protection Command.
4. Click on Protect sheet.
5. Then a dialog box will appear.
6. Choose the required options, (Contents, Objects etc)
7. Type a password.
8. Click on Ok.
9. Again type the same password.
10. Click on Ok.

To protect workbook.

1. Click on Tools menu.
2. Click on Protection command.
3. Click on Protect workbook.
4. Then a dialog box will appear.
5. Choose the required options(Structure, Window)
6. Type a password.
7. Click n Ok.
8. Again type the same password.
9. Click on Ok.

Using Goal Seek command.

1. On the Tools menu, click Goal seek.
2. In the Set cell box, enter the reference for the cell that contains. The formula you want to resolve.
3. In the To value box, type the result you want.
4. In the By changing cell box, enter the reference for the cell that contains the value you want to adjust. This cell must be referenced by the formula in the cell you specified in the Set Cell box.
5. Click on Ok button.

Using Auditing command.

1. Click on Tools menu.
2. Click on Auditing command.
3. Then click on the required options. (Trace precedents, trace dependents, trace errors)

To remove trace arrows

1. Click on Tools menu.
2. Click on Auditing command.
3. Click on Remove all arrows.

To Show/Hide gridlines, row & columns header, scrollbar, sheet tabs etc.

1. Click on tools menu.
2. Click on Options command.
3. Then a dialog box will appear.
4. Click on View tab.
5. Choose the required options to from windows options.
6. [Gridlines, row & column headers, Horizontal scrollbar, sheet tabs et c.]

To sort data.

1. Select the required cells.
2. Click on Data menu.
3. Click on Sort command.
4. Then a dialog box will appear.
5. Choose the required column name for sort by option.
6. Choose Ascending or Descending order. [If you want to sort multiple columns click on Then by option and choose the required column name.
7. Choose Header row or No header row from My list has option.
8. Click on Ok.

To Auto filter data.

1. Select the required columns.
2. Click on Data menu.
3. Click on Filter command.
4. Then click on Auto filter.
5. Now Click on drop down arrow list of column and choose the required options. (All, Top 10, custom etc)

To remove Auto Filter.

1. Click n Data menu.
2. Click on Filter command.
3. Click on Auto filter.

Creating Data form to edit data.

1. Select the required cell.
2. Click on Data menu.
3. Click on Form command.
4. Now a data form appears.
5. Click New button to add new record.   Type the data.
6. To move to the next field, press TAB key.
7. When you finish typing data, press ENTER to add the record.
8. Click on Find next button to find the next records a Find prev button to find the previous records.
9. Click on Delete button to delete the displayed record.
10. Click Close to close the data form.

Mark - sheet
Name	English	Math	Science	Nepali	Account	Total	Percent	Result	Division	Remarks
Ramesh	78	56	23	45	54
Sangita	89	65	78	68	90
Amir	78	98	89	75	89
Bikash	56	56	23	45	54
Pranita	65	65	78	68	90
Dinesh	98	89	75	89	90
Kapil	89	78	68	90	54
Rupendra	78	56	56	23	45
Sunil	89	54	78	68	90
Ganesh	78	54	56	56	23
Formulas
Total:
=Sum(English:Account)
Percent:
=total/no. Of subjects
Condition for result
All subjects>=32		Pass
Otherwise		Fail
=IF(AND(English>=32,Math>=32,Science>=32,Nepali>=32,Account>=32),"Pass","Fail")
Condition for division
Percent>=60			First
Percent<60,Percent>=50			Second
Percent<50,Percent>=40			Third
Otherwise			***
=if(and(Percent>=60),"First",if(and(Percent<60,Percent>=50),"Second",if(and
(Percent<50,Percent>=40),"Third","***")))
Condition for Remarks:
Percent>=80			A grade
Percent<80,Percent>=60			B grade
Otherwise			C grade
Do yourself

Basic Salary Sheet
Name	B. Salary	Travelingallowance10%	House rentallowance12%	Medicalallowance10%	Gross salary	Income tax	Providentfund10%	Net salary
Ramesh	7500
Sagita	6000
Amir	3500
Bikash	3000
Pranita	2500
Dinesh	4500
Kapil	4000
Rupen	6500
Sunil	12000
Ganesh	9000
Furmulas:
Travelling All. 10%
=10%*B=Salary
House rent all.12%
=12%*B=Salary
Medical All. 10%
=10%*B.Salary
Gross Salary
=sum(B.Salary:Medical all.)
Condition for Income tax:
B.Salary>=9000			10%*B.Salary
B.Salary<9000,B.Salary>=5000			5%*B=Salary
B.Salary<5000,B.Salary>=3000			2%*B.Salary
Otherwise			0
=if(and(B.Salary>=9000),10%*B.Salary,if(and(B.Salary<9000,B.Salary>=5000),5%*B.Salary,if(and
(B.Salary<5000,B.Salary>=3000),2%*B.Salary,0)))
Provident Fund:
=10%*B.Salary
Net salary
=Gross salary-Income tax-Provident fund

Tele-Communication Sheet
Name	Total Calls	Rental Charge	Extra charge	Total charge	Vat 10%	Govt.tax10%	Net charge
Binay	250	150
Dines	300	150
Anil	90	150
Mina	65	150
Gita	100	150
Condition for Extra charge
total calls<=100		0
otherwise		Per call Rs.*(total call-100)
Per call Rs. 2
Formula:
=if(and(total call<=100),0,2*(total call-100))
Total charge
=Rental charge+Extra charge
Vat 10%
=10%*Total charge
Govt. tax10%
=10%*Total charge
Net charge
=total charge+Vat+Govt. tax

Some Important Formulas:
To calculate Average value
=average(data range)
To calculate Maximum Value
=max(data range)
To calculate minimum Value
=min(data range)
To calculate Total
=sum(data range)
To calculate Standard deviation
=stdev(data range)
To calculate Square root of any number
=sqrt(n)
To calculate power of the value
=power(X,n)
where X is main value, n is power
eg. =power(5,2)=25
To insert current date
=today()
To insert current date and time
=now()
To calculate the value of sin, cos, tan, log etc
=sin(n)
=cos(n)
=tan(n)
=log(n)
To convert radians to angle
=degrees(angle)
To count the values or numbers
=count(data range)

Practical Portion

Temperature Conversion of Yearly Temperature                                                                                                                   

Month	Celsius	Fahrenheit
January	-40	=9/5*Celsius + 32
February	20
March	24
April	28
May	30
June	0
July	34
August	32
September	37
October	24
November	100
December	37

Income

Sales		100000
Less : Manufacturing Cost
Material	12000
Labour	18000
Total M Cost		=Material+Labour
Contribution		=Sales-total M. Cost
Less : Sales and Administrative
Advertisement	10000
Administrative	15000
Total S & A		=Advertisement+Administrative
Profit (Loss)		=contribution-Total S & A

                                                                                                                                               

Income Statement

Of Gemini Trade Link

Product Price	Rs.40
Product Cost	Rs.25
Overhead	15%
Quarter 1	Quarter 2	Quarter 3		Quarter 4
Units sold                         Rs.10,000	Rs. 20,000	Rs.30,000		Rs.40,000
Sales Revenue                Units sold * Product price
Cost of Sales                   Units sold * Product cost
Gross Margin                   Sales - Cost
Sales Force                      Rs. 8,000	Rs. 8,000		Rs.8,000	Rs. 8,000
Advertisement                  Rs. 10,000	Rs. 10,000		Rs.10,000	Rs.10,000
Corp. Overhead               Sales Revenue * Overhead
Total Costs                       Sales Force + Adv. + Corp. Overhead
Profit                                  Gross margin - Total Cost
Profit Margin       Profit / Sales Revenue