Introduction to Excel
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.
Cell reference:
There are 65536 rows and 256 columns in one worksheet. The first cell is labeled as ‘A1’ (column as A and Rows as 1) or R1C1.
Workbook:
The file of Ms-Excel is called workbook and its extension is .XLS. It is collection of one or more worksheets. Workbook is like a binder and every worksheet as a page in a binder. Each workbook contains 16 worksheets.
Worksheet:
The large working area of a workbook is called worksheet. There are 65,536 rows and 256 columns a worksheet. It is a page of workbook.
Grid line:
The line that makes or separates a cell in a worksheet of a workbook is called grid line.
Cell:
The intersection between row and column is called Cell. Each cell contains 32000 Character.
Title bar Menu bar Formula bar Column heading
Sheet Tabs Status bar Scroll bar
To start Microsoft Excel 2000/XP
1. Click on Start cotton.
2. Click on Programs.
3. Click on Ms Excel.
Or
4. Click on Start
5. Click on Run
6. Then a dialog box appears
7. Type Excel
8. Click on OK.
TO CREATE A NEW WORKBOOK:
1. Click on file menu
2. Click on new
3. The a dialog box will appears
4. To create a new, blank workbook, click a General tab.
5. Choose Workbook icon
6. Click on OK button.
TO SAVE A WORKBOOK:
1. Click on File menu.
2. Click on Save command
3. In the Save in list, select the drive and folder where you want to save the workbook.
4. [If you want to save the workbook in a new folder, click on Create New folder]
5. In the File name box, type a name for the workbook.
TO CLOSE A WORKBOOK:
1. Click on File menu.
2. Click on Close command.
3. To close all open workbooks without exiting the program, hold down SHIFT and click close all on the File menu.
COPY OF TO SAVE A WORKBOOK:
1. Open the workbook you want to make a copy of.
2. Click on file menu.
3. Click on Save as.
4. In the File name box, type a new name of the workbook
5. Click Save.
TO OPEN A WORKBOOK:
1. Click on file menu.
2. Click on open command.
3. 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 that contain the workbook.
4. Select the required workbook name.
5. Click on Open.
6. Double click the workbook you want to open.
TO PROTECT WORKBOOK BY PASSWORD:
1. First open the workbook.
2. Click on File menu.
3. Click on Save as command.
4. Then a dialog box will appear.
5. Click on Tools of dialog box and click on General option.
6. Type the password in Password to open box.
7. Click on ok bottom.
8. Then a confirm password box will appear.
9. Type the same password Modify again.
10. Click on ok button.
11. Click on save button.
USING PAGE SETUP COMMAND:
1. This command is used to set margins, paper source, paper size, page orientation, and other layout options for the active file.
2. First open the workbook of which you want to set page.
3. Click on File menu.
4. Click on Page Setup command.
5. Then a dialog box will appear.
6. Click on page tab.
7. Choose the required orientation. (Portrait or landscape)
8. Choose any paper size.
9. Click on margin tab.
10. Set the required margin for Top, Bottom, and Left, Right, Header& footer.
11. Choose other required options.
12. Click on OK button.
TO SEE THE PRINT PREVIEW OF WORKSHEET:
1. Click on file menu.
2. Click on Print Preview command.
3. Now you can see the preview of your worksheet.
4. Click on Next button to see the next page of sheet.
5. Click on Previous button to see the previous page.
6. Click on zoom button to reduce or enlarge the page size.
7. Click on setup button to open the setup dialog box.
8. Click on Margins tab to set the margin.
9. Click on close button to close the preview.
TO SET PRINT AREA:
1. Select the required part of sheet which you want to set as print area.
2. Click on file menu.
3. Click on Print area command.
4. Choose Set print area.
5. [To clear print area click on File menu, choose print area and click on clear print area.]
TO PRINT A WORKSHEET:
1. Open the worksheet which you want to print.
2. Click on File menu.
3. Click on Print command
4. Then a dialog box will appear.
5. Choose the print name from Name box.
6. Choose any option from Print range criteria. [All, Page].
7. Choose any option from Print what criteria. [Selection, entire workbook etc.]
8. Choose the required number of copies to print.
9. Click on other required options and Click on OK button.
TO SELECT ENTIRE ROW:
1. Move the mouse pointer over the row number which you want to select.
2. Press the mouse button once.
TO SELECT ENTIRE COLUMN:
1. Move the mouse pointer over the column label which you want to select.
2. Press the mouse button once.
TO SELECT THE ENTIRE WORKSHEET:
1. Move the mouse pointer over the top left corner of worksheet.
2. Press the mouse button once.
USING CUT/ COPY/PASTE:
1. Select the required cells which you want to copy or move.
2. Click on Edit menu.
3. Click on copy or Cut command as required
4. Then place the active cell at the required cell
5. Click on Edit menu. .
6. Click on Paste Command
7. [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.
1. Put the active cell at the required cell.
2. Type First and Second number in First and Second cell.
3. Then select the both cell.
4. Move the mouse pointer over the bottom right corner of second cell.
5. Drag the fill handle as required.
To Fill Date
1. Put the active cell at the required cell.
2. Type Sunday or January in First cell.
3. Then move the mouse pointer over the bottom right corner of first cell.
4. Drag the fill handle.
USING CLEAR COMMAND:
1. Select the required cells which you want to clear.
2. Click on Edit menu.
3. Click on Clear command.
4. Then click on All option to clear everything with formatting, contents etc,
5. Click on formats option to clear only the formats.
6. Click on Contents option to clear only the contents.
7. Click on Comments option to clear the comments only.
TO DELETE CELLS, ROWS, AND COLUMNS:
1. Select the required cells or rows or columns which you want to delete.
2. Click on Edit menu.
3. Click on Delete command.
4. If you have selected cells then a dialog box appears.
5. Choose any option. [Shift cells left, shift cells up etc.]
6. Click on Ok.
TO DELETE WORKSHEET:
1. Select the required sheet name which you want to delete.
2. Click on Edit Menu,
3. Click on Delete sheet command.
4. Then a message box will appear.
5. click on ok
TO MOVE OR COPY SHEETS:
1. Select the required sheet name.
2. Click on Edit menu.
3. Click on Move or Copy sheet command.
4. Then a dialog box will appear
5. Choose any option from before sheet list.
6. [If you want to create a copy of the selected sheet click create a copy option]
7. Click on OK.
USING FIND AND REPLACE COMMAND:
Find:
1. Click on Edit menu.
2. Click on Find Command
3. Then a dialog box will appear.
4. Type a required text or data in find what box.
5. Click on Find next button.
Replace:
1. Click on Edit menu
2. Click on Replace command
3. Then a dialog box will appear.
4. Type the required text or data in find what box which you want to replace.
5. Type the replacing text or data in Replace with box.
6. Then click on Find next.
7. Click on “Replace” or “Replace All” as required.
USING GOTO COMMAND:
1. Click Edit menu.
2. Click on Go to command.
3. Then a dialog box will appear.
4. Type the cell name in reference box.
5. Click on OK.
BAR TO SHOW OR HIDE FORMULA:
1. Click on View menu.
2. Click on Formula bar Command to show or hide.
TO ADD HEADER AND FOOTER:
1. Click on View menu.
2. Click on Header & Footer command.
3. Then a dialog box will appear.
4. Click on Custom header.
5. Put the cursor at the required section (left, center, right).
6. Type the header Name.
7. Click on Ok.
8. Then click on Custom Footer button.
9. Type the footer Name in the required section.
10. [You can also insert page number, date, time, filename etc as header & footer]
11. Click on OK.
TO VIEW THE WORKSHEET IN FULL SCREEN:
1. Click on View menu.
2. Click on Full screen command.
3. Now your worksheet will appear in full screen.
4. To close full screen, click on Close full screen tool.
TO CHANGE THE VIEW OF WORKSHEET:
1. Click on View menu.
2. Click on Zoom command.
3. Then a dialog box will appear.
4. Choose any one option. (200%, 100%, 50%, 75% etc.)
5. Click on OK.
TO INSERT CELLS:
1. Select the required cells where you want to insert new blank cells.
2. Click on Insert menu.
3. Click on Cells command.
4. Then a dialog box will appear.
5. Choose any one option. (Shift cells right, shift cells down & insert entire Rows, Columns).
6. Click on Ok
TO INSERT ROWS:
1. Select the required rows where you want to insert new rows.
2. Click on Insert menu.
3. Click on Rows command
TO INSERT COLUMNS:
1. Select the required columns where you want to insert new columns.
2. Click on Insert menu.
3. Click on Columns command.
TO INSERT WORKSHEET:
1. Select the sheet name where you want to insert new worksheet.
2. Click on Insert menu.
3. Click on Work sheet command.
4. [Now a new worksheet will appear in the sheet tabs]
TO INSERT CHART:
1. First type the data for chart.
2. Select the data range.
3. Click on insert menu.
4. Click on Chart command.
5. Then a chart wizard box will appear.
6. Choose any chart type and chart subtype form standard type tab. [For more chart types click on Custom types tab]
7. Click on Next button.
8. Choose the required option from series in [Rows or Columns]
9. Click on Next button.
10. Type the chart title in Chart title box.
11. Type the X and Y category titles.
12. Click on Axes tab and choose the required options.
13. Click on Gridlines tab and choose the required options.
14. Click on Legend and choose any option.
15. Click on Data labels and Data table tab and choose the required options.
16. Click on Next button.
17. Now choose the required location to place chart.
18. Click on Finish button.
19. [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:
1. Put the active cell at the required cell where you want to use formula.
2. Type (=) equal sign, [Formula always begins with (=) sign]
3. Enter the formula with cell references and operators
4. Press Enter Key
Functions Operators:
1. + Addition
2. - Subtraction
3. * Multiplication.
4. / Division
5. % Percentage.
6. = Equal comparison.
7. < Less than
8. > Greater than.
9. <= Less than or equal to
10. >= Greater than or equal to
11. <> Is not equal to
12. And/ Or/ Not Logic.
Using Function wizard:
1. Place the cursor in cell where you want to insert Function.
2. Click on Insert menu.
3. Click on Function Command.
4. Then a function wizard box will appear.
5. Select the required Function category.
6. Choose any Function name. (Sum, average, min, max etc.)
7. Click on Ok.
8. Then a dialog box will appear.
9. Type the cell references in Number box.
10. Click on Ok button.
To insert comment:
1. Select the required cells where you want to insert comment.
2. Click on Insert menu.
3. Click on Comment command.
4. Then a comment box appears.
5. Type the required comments.
To show or Hide comments:
1. Select the required cells that contain comment.
2. Press opposite mouse button.
3. Choose show comment/Hide comment.
To Edit or Delete comment:
1. Select the required cells that contain comments.
2. Press opposite mouse button.
3. Choose Edit comments or Delete comments.
To insert Clipart:
1. Click on Insert menu.
2. Click on Picture command.
3. Then a Clipart box will appear.
4. Click any category.
5. Choose any picture and press the mouse button.
6. Choose Insert clip
7. Then close the clipart box.
To insert Auto shapes:
1. Click on Insert menu.
2. Click on picture command.
3. Click on Auto shapes.
4. Then an auto shapes toolbar will appear.
5. Choose any shape from toolbar.
6. Press the mouse button and drag it to draw auto shapes.
To insert word art:
1. Click on Insert menu.
2. Click on Picture command.
3. Click on Word art.
4. Then a word art style box will appear.
5. Choose any style.
6. Click on Ok.
7. Now type the required text.
8. Click on Ok.
To insert Object (Paint brush picture):
1. Click on Insert menu.
2. Click on Object command.
3. Then a dialog box will appear.
4. Choose Paint brush picture.
5. Click on Ok.
6. Now draw any picture you like.
Insert Hyperlink To:
1. Select the required cells or object where you want to insert hyperlink.
2. Click on Insert menu.
3. Click on hyperlink command.
4. Then a dialog box will appear.
5. Click on File of Browser for option.
6. Choose the required file.
7. Click on Ok.
8. Again click on Ok.
To format numbers:
1. Select the required cells.
2. Click on Format menu.
3. Click on Cells command.
4. Then a dialog box will appear.
5. Click on Number tab.
6. Choose Number category.
7. Choose the required decimal places.
8. If needed choose the Use thousand separators option. Click on Ok.
To change the number in currency format:
1. Select the required cells.
2. Click on Format menu.
3. Click on Cells command.
4. Then a dialog box will appear.
5. Click on Number tab.
6. Choose Currency tab.
7. Choose the required decimal places.
8. Choose any currency symbol.
9. Click on OK.
To change the number in Percentage format:
1. Select the required cells.
2. Click on Format menu.
3. Click on Cells command.
4. Then a dialog box will appear.
5. Click on Number tab.
6. Click on Percentage tab.
7. Choose the required decimal places.
8. Click on OK.
To merge and center data:
1. First type the long text in a single cell.
2. Select the required cell range.
3. Click on Format menu.
4. Click on Cells command.
5. Then a dialog box will appear.
6. Click on Alignment tab.
7. Choose center from Horizontal and Vertical alignment.
8. Click the three options of text control criteria. (Wrap text, shrink to fit, merge cells)
9. Click on Ok.
10. [Put the Cursor at the required text and op pressing spacebar unit the text comes to the next line.]
To change the data orientation:
1. Select the required cells.
2. Click on Format menu.
3. Click on Cells command.
4. Then click on Alignment tab.
5. Choose the required degrees.
6. Click on Ok.
To format font:
1. Select the required cells.
2. Click on Format menu.
3. Click on Cells command.
4. Then click on Font command.
5. Choose the required font, font style size, font color, underline style, effects.
6. 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 two 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, and sheet tabs etc.]
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.
Worked out examples:
1. Mark – sheet
Name English Math Science Nepali Account Total Percent Result Division Remarks
Partha Chudal 78 56 82 76 85
Ajay Ku. Sah 89 65 78 68 90
Shristy Dhungel 78 98 89 75 89
Rajan Khadka 56 56 23 45 54
Samjhana Khakuel 65 65 78 68 90
Pradeep Budhathoki 98 89 75 89 90
Suju Payani 89 78 68 90 54
Kajal Pradhan 78 56 56 23 45
Nitu Yadav 89 54 23 45 36
Umesh Sujakhu 78 54 56 56 23
Formulae:
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
2. Basic Salary Sheet
Name B. Salary Traveling allowance10
% House rent allowance12
% Medical allowance10
% Gross salary Income tax Provident fund10
% Net salary
Partha Chudal 7500
Ajay Ku. Sah 6000
Shristy Dhungel 3500
Rajan Khadka 3000
Samjhana Khakuel 2500
Pradeep Budhathoki 4500
Suju Payani 4000
Kajal Pradhan 6500
Nitu Yadav 12000
Umesh Sujakhu 9000
Formulae:
Traveling 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
3. Tele-Communication Sheet
Name Total Calls Rental Charge Extra charge Total charge Vat 10% Govt. tax10% Net charge
Partha Chudal 250 150
Ajay Ku. Sah 300 150
Shristy Dhungel 90 150
Formulae:
=if(and(total call<=100),0,2*(total call-100))
Condition for Extra charge
total calls<=100
0
otherwise Per call Rs.*(total call-100)
Per call Rs. 2
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
e.g. =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
Overall Statement of a Factory
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
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
Income Statement of Gemini Trade Link
Income
Some Functions in excel:
CONCATENATE
Joins several text strings into one text string.
Syntax: CONCATENATE (text1, text2...)
DOLLAR:
This function converts a number to text using currency format, with the decimals rounded to the specified place.
Syntax: DOLLAR (number, decimals)
TRIM:
Removes all spaces from text except for single spaces between words
Syntax: TRIM (text)
EXACT
Syntax: EXACT (text1, text2)
Text1 is the first text string.
Text2 is the second text string.
LEFT
LEFT returns the first character or characters in a text string
Syntax: LEFT (text, num chars)
RIGHT
RIGHT returns the last character or characters in a text string
Syntax: RIGHT (text, num chars)
LOWER
Converts all uppercase letters in a text string to lowercase
Syntax: LOWER (text)
REPT
Repeats text a given number of times, Use REPT to fill a cell with a number of instances of a text string
Syntax: REPT (text, number times)
COUNTIF:
Counts the number of cells within a range that meet the given criteria
Syntax: COUNTIF (range, criteria)
PROPER:
Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter, converts all other letters to lowercase letters
Syntax: PROPER (text)
AVERAGE:
Returns the average (arithmetic mean) of the arguments
Syntax: AVERAGE (number1, number2...)
PERCENTRANK:
Returns the rank of a value in a data set as a percentage of the data set, this function can be used to evaluate the relative standing of a value within a data set. For example, you can use PERCENTRANK to evaluate the standing of an aptitude test score among all scores for the test
Syntax: PERCENTRANK (array, x, significance)
MOD:
Returns the remainder after number is divided by divisor. The result has the same sign as divisor
Syntax: MOD (number, divisor)
SUM
Adds all the numbers in a range of cells
Syntax: SUM (number1, number2 ...)
SUMIF:
Adds the cells specified by a given criteria
Syntax: SUMIF (range, criteria, sum_ range)
SIN:
Returns the Sine of the given angle
Syntax: SIN (number)
Number is the angle in radians for which you want the sine.
TRUE
Returns the logical value TRUE
Syntax: TRUE ( )
Log:
Returns the logarithm of a number to the base you specify
Syntax: LOG (number, base)
COS:
Returns the cosine of the given angle
Syntax: COS (number)
TAN:
Returns the tangent of the given angle
Syntax: TAN (number)
POWER:
Returns the result of a number raised to a power
Syntax: POWER (number, power)
PRODUCT:
Multiplies all the numbers given as arguments and returns the product
Syntax: PRODUCT (number1, number2...)
CONCLUSION:
Hence MS Excel is word processing software with complete flexibility of mathematical, logical and word operation. It also helps you to perform sting operation. It has been widely used in offices and other professional fields.
0 comments:
Post a Comment