Click on the Image you want to insert in the worksheet.
Insert Picture from File  Beside Clipart wer can also add picture form existing file. Follow these steps to add a photo or graphic from an existing file:  1. Select Insert>Picture>From File on the menu bar.   2. Click the down arrow button on the right of the Look in: window to find the image on your computer.   3. Highlight the file name from the list and click the Insert button.  
Insert WordArt  By using Word Art, we can insert decorative text. We can create shadowed, skewed, rotated, and stretched text, as well as text that has been fitted to predefined shapes.  Such as:   To insert a WordArt follow these steps:  Click on Insert>Picture>WordArt   From the dialog box select the Style   In the Edit WordArt Text type the text you want to appear as WordArt text and Click on OK.    
Insert Auto Shape  The AutoShapes toolbar will allow you to draw a number of geometrical shapes, arrows, flow chart elements, stars, and more on the worksheet.  Activate the AutoShapes toolbar by selecting Insert>Picture>AutoShapes or View>Toolbars>AutoShapes from the menu bar.  Click the button on the toolbar to view the options for drawing the shape.   • Lines  After clicking the Lines button on the AutoShapes toolbar, draw a straight line, arrow, or doubleended arrow from the first row of options by clicking the respective button. Click in the worksheet where you would like the line to begin and click again where it should end. To draw a curved line or freeform shape, select curved lines from the menu (first and second buttons of second row), click in the worksheet where the line should appear, and click the mouse every time a curve should begin. End creating the graphic by clicking on the starting end or pressing the ESC key. To scribble, click the last button in the second row, click the mouse in the worksheet and hold down the left button while you draw the design. Let go of the mouse button to stop drawing.  • Connectors  Draw these lines to connect flow chart elements.  • Basic Shapes  Click the Basic Shapes button on the AutoShapes toolbar to select from many two and threedimensional shapes, icons, braces, and brackets. Use the draganddrop method to draw the shape in the worksheet. When the shape has been made, it can be resized using the open box handles and other adjustments specific to each shape can be modified using the yellow diamond handles.   • Block Arrows  Select Block Arrows to choose from many types of two and threedimensional arrows. Draganddrop the arrow in the worksheet and use the open box and yellow diamond handles to adjust the arrowheads. Each AutoShape can also be rotated by first clicking the Free Rotate button on the drawing toolbar . Click and drag the green handles around the image to rotate it. The tree image below was created from an arrow rotated 90 degrees.   • Flow Chart  Choose from the flow chart menu to add flow chart elements to the worksheet and use the line menu to draw connections between the elements.  • Stars and Banners  Click the button to select stars, bursts, banners, and scrolls.  • Call Outs  Select from the speech and thought bubbles, and line call outs. Enter the call out text in the text box that is made.  • More AutoShapes  Click this button to choose from a list of clip art categories.   Each of the submenus on the AutoShapes toolbar can become a separate toolbar. Just click and drag the gray bar across the top of the submenus off of the toolbar and it will become a separate floating toolbar.  
Printing a Workbook   Using Page Setup  Select File>Page Setup from the menu bar to format the page, set margins, and add headers and footers etc.  • Page  Select the Orientation under the Page tab in the Page Setup window to make the page Landscape or Portrait. The size of the worksheet on the page can also be formatting under Scaling. To force a worksheet to print only one page wide so all the columns appear on the same page, select Fit to 1 page(s) wide.    • Margins  Change the top, bottom, left, and right margins under the Margins tab. Enter values in the header and footer fields to indicate how far from the edge of the page this text should appear. Check the boxes for centering horizontally or vertically on the page.    • Header/Footer  Add preset headers and footers to the page by clicking the dropdown menus under the Header/Footer tab.    • Sheet  Check Gridlines if you want the gridlines dividing the cells to be printed on the page. If the worksheet is several pages long and only the first page includes titles for the columns, select Rows to repeat at top to choose a title row that will be printed at the top of each page.  
Setting Print Area in Excel  We can set print in MS Excel to define the area we want to print or view while print previewing the worksheet. By default one page in excel is equal to approximate 50 rows and 9 columns but using Set Print Area command we set the area we that should be printed or shown in print preview.  To set Print Area follow the following steps:  1. Select the cells.   2. Click on File >Print Area > Set Print Area.   Selected area has been set as print area  
Adding Header & Footer  To add Header & Footer to your Page Click on View >Header & Footer   Page setup dialog box will appear with Header & Footer tab pre selected .   To modify a preset header or footer, or to make your own, click the Custom Header and Custom Footer buttons. A new window will open allowing you to enter text in the left, center, or right on the page.   Format Text  Click this button after highlighting the text to change the font, size, and style.  Page Number  Insert the page number of each page.  Total Number of Pages  Use this feature along with the page number to create strings such as "page 1 of 15".  Date  Add the current date.  Time  Add the current time.  File Name  Add the name of the workbook file.  Tab Name  Add the name of the worksheet's tab.  Remember Header & Footer is displayed in Printed hard copy only or we can view our header & footer text while print previewing the worksheet. 
Print Previewing a Worksheet  Select File>Print Preview from the menu bar to preview how the worksheet will print.   A Print Preview Dialog will be displayed as shown below:   Click the Next and Previous buttons at the top of the window to display the pages and click the Zoom button to view the pages closer. Make page layout modifications needed by clicking the Page Setup button. Click Close to return to the worksheet or Print to continue printing. 
Print a Worksheet  To print the worksheet, select File >Print from the menu bar or press Ctrl+ P from keyboard or click on print[ ] button on toolbar it will display the following dialog box.   • Print Range  Select either all pages or a range of pages to print.  • Print What  Select selection of cells highlighted on the worksheet, the active worksheet, or all the worksheets in the entire workbook.  • Copies  Choose the number of copies that should be printed. Check the Collate box if the pages should remain in order.  Click OK to print. 
Cell Formatting   Formatting Toolbar  The contents of a highlighted cell can be formatted in many ways. Font and cell attributes can be added from shortcut buttons on the formatting bar. If this toolbar is not already visible on the screen, select View>Toolbars>Formatting from the menu bar.  
Formatting Number  We often need to format the numbers to display the appropriate number of decimals, $ signs, percentage, red (for negative dollar), etc. It is best to keep numbers describing similar items as uniform as possible.  If we have the number 3.53262624672423, we would probably have to make the column wider and at the least bore most people. We need to set the number of decimal places to what is important. If this was a dollar figure that had calculated tax it should be $35.5.  Numbers in Excel can be formatted in a variety of ways: as whole numbers, as decimals, as percentages, and even as currency. By default, numbers use the General formatthis displays numbers exactly as you enter them (although Excel rounds the numbers using decimals or scientific notation if the cells are too narrow to accommodate what you've entered).  To apply a different number format to the contents of a cell:  1. Select the cell.   2. From the Format menu, select Cells.  The Format Cells dialog opens.  3. Select the Number tab.   4. All the available number formats are listed under Category. Select a category, such as Currency.   5. Formatting options for that category appear on the right side of the dialog. Select the options you want, such as the number of decimal places to use.  6. Click OK.  7. The formatting is applied to the selected cell (or range of cells).  
Text Alignment  Justification changes are also common modifications in a spreadsheet. On the middle of the Formatting toolbar are four buttons:  If we have the number 3.53262624672423, we would probably have to make the column wider and at the least bore most people. We need to set the number of decimal places to what is important. If this was a dollar figure that had calculated tax it should be $35.5.   "Align Left"   "Align Center"   "Align Right"   "Center Across Columns"  The left, center or right options place information at that location within its cell. If the text or number is as wide as the cell, then changing the alignment has no effect. You can change alignment on a single cell or a block of cells.  The "Center Across Columns" option is useful for centering. Several cells, for example A1 through H1, are highlighted and then this alignment is selected. The text in A1 will appear to be centered across the cells. The text is still located in its original location (A1). For this feature to work correctly, the heading must be in the leftmost cell in the block range.   To angle your text  Select the cell(s) and choose Cells>Alignment from the Format menu. In the Orientation section, specify the angle (in degrees), then click OK.    To set vertical alignment  Select the cell(s) and choose Cells>Alignment from the Format menu. In the Vertical section, choose Top, Center, Bottom or Justify. Click OK.  
Font  Spreadsheets can be pretty dry, so we need some tools to dress them up a little. We can use most of the tricks in our word processor to do the formatting of text. We can use : bold face, italics, underline, change the color, align (left, right, center), font size, font, etc.  Additional font options are available in the Format Cells dialog:  1. Select the cell whose contents you want to format.
2. From the Format menu, select Cells. You can also rightclick the cell (or range of cells) and select Format Cells from the shortcut menu.  The Format Cells dialog is displayed:   3. Select the Font tab.  Select the font, Font Style, Size and Click on OK to apply.
Here, you can see a sample of the options you select in the Preview pane. 
Border & Shading  Applying Border  Select the cells you want to apply a Border and click on Format?Cells, the Format Cells Dialog box will be displayed:   Select the Border tab select the Style, Color and type and Click on OK to apply changes.   Applying Shading  Select the cells you want to apply Shadings and click on Format?Cells, the Format Cells Dialog box will be displayed:   Select Patterns tab from the dialog box.  Select Pattern and Color for Cell shading and click on OK to apply. 
Auto Formatting a Worksheet  To Format a worksheet we can also use Auto Format command from format menu. To auto format worksheet select the cell(s) and Select Format>AutoFormat from the menu bar.  On the AutoFormat dialog box, select the format you want to apply to the table by clicking on it with the mouse. Use the scroll bar to view all of the formats available.    Click the Options... button to select the elements that the formatting will apply to.  Click OK when finished.  
Conditional Formatting  What is conditional formatting?  A format, such as cell shading or font color, that Excel automatically applies to cells if a specified condition is true.   Apply Conditional Formatting  Select the cell(s) you want to use conditional formatting with and click on Format? Conditional Formatting, a dialog box will be displayed.  Select the condition    enter the value that will be used to verify condition and select a appropriate format by clicking on Format button.   and click on OK to apply conditional formatting.   We can add three conditions for conditional formatting. 
Format Painter  Format painter is used to copy format from a selected object or cell and applies it to the object or text we click.   How to use Format Painter?  To copy formatting from an object or cell select the cell you want to copy format of and click on format painter button on standard toolbar and then click on the cell where you want to apply this formatting.  Select the condition 
Formula & Functions
Mathematical Functions  Excel Provide us a wide variety of Mathematical & Trigonometry functions  Function  Description  ABS  Returns the absolute value of a number  ACOS  Returns the arccosine of a number  CEILING  Rounds a number to the nearest integer or to the nearest multiple of significance  COMBIN  Returns the number of combinations for a given number of objects  COS  Returns the cosine of a number  COSH  Returns the hyperbolic cosine of a number  DEGREES  Converts radians to degrees  EVEN  Rounds a number up to the nearest even integer  FACT  Returns the factorial of a number  FLOOR  Rounds a number down, toward zero  POWER  Returns the result of a number raised to a power  PRODUCT  Multiplies its arguments  ROUND  Rounds a number to a specified number of digits  SIGN  Returns the sign of a number  SIN  Returns the sine of the given angle  SQRT  Returns a positive square root  SUM  Adds its arguments    ABS  Returns the absolute value of a number. The absolute value of a number is the number without its sign.  Syntax  ABS(number)  Number is the real number of which you want the absolute value.  Example  Formula  Description (Result)  =ABS(2)  Absolute value of 2 (2)  =ABS(2)  Absolute value of 2 (2)    ACOS  Returns the arccosine, or inverse cosine, of a number. The arccosine is the angle whose cosine is number. The returned angle is given in radians in the range 0 (zero) to pi.  Syntax  ACOS(number)  Number is the cosine of the angle you want and must be from 1 to 1.  Remark  If you want to convert the result from radians to degrees, multiply it by 180/PI() or use the DEGREES function.  Example:  Formula  Result  =ACOS(0.6)  2.214297  =ACOS(0.6)*180/PI()  126.8699    ACOSH  Returns the inverse hyperbolic cosine of a number. Number must be greater than or equal to 1. The inverse hyperbolic cosine is the value whose hyperbolic cosine is number, so ACOSH(COSH(number)) equals number.  Syntax:  ACOSH(number)  Number is any real number equal to or greater than 1.  Example  Formula  Result  =ACOSH(10)  2.993222846  =ACOSH(20)  3.688253867    CEILING  Returns number rounded up, away from zero, to the nearest multiple of significance. For example, if you want to avoid using pennies in your prices and your product is priced at 14.48, use the formula =CEILING(14.48,0.05) to round prices up to the nearest nickel.  Syntax  CEILING(number,significance)  Number is the value you want to round.  Significance is the multiple to which you want to round.  Remarks  • If either argument is nonnumeric, CEILING returns the #VALUE! error value.
• Regardless of the sign of number, a value is rounded up when adjusted away from zero. If number is an exact multiple of significance, no rounding occurs.
• If number and significance have different signs, CEILING returns the #NUM! error value.  Example  Formula  Result  =CEILING(14.48,0.05)  14.5  =CEILING(19.43,1)  20    COMBIN  Returns the number of combinations for a given number of items. Use COMBIN to determine the total possible number of groups for a given number of items.  Syntax  COMBIN(number,number_chosen)  Number is the number of items.  Number_chosen is the number of items in each combination.  Remarks  • Numeric arguments are truncated to integers.
• If either argument is nonnumeric, COMBIN returns error value.
• If number < class="textbold">COMBIN returns error value.
• A combination is any set or subset of items, regardless of their internal order. Combinations are distinct from permutations, for which the internal order is significant.  Example  Formula  Result  =Combin(28,2)  378  =COMBIN(28,3)  3276    COS  Returns the cosine of the given angle.  Syntax  COS(number)  Number is the angle in radians for which you want the cosine.  Remark  If the angle is in degrees, either multiply the angle by PI()/180 or use the RADIANS function to convert the angle to radians.  Example  Formula  Result  =COS(1.42)  0.15022547  =COS(90*PI()/180)  6.12574E17    COSH  COSH(number)  Number is any real number for which you want to find the hyperbolic cosine.  Example  Formula  Result  =COSH(5)  74.20994852  =COSH(EXP(2))  809.0893049    DEGREES  Converts radians into degrees.  Syntax  DEGREES(angle)  Angle is the angle in radians that you want to convert.  Example  Formula  Result  =DEGREES(PI())  180  =DEGREES(PI()/2)  90    EVEN  Returns number rounded up to the nearest even integer. We can use this function for processing items that come in twos.  Syntax  EVEN(number)  Number is the value to round.  Remarks  • If number is nonnumeric, EVEN returns error value.
• Regardless of the sign of number, a value is rounded up when adjusted away from zero. If number is an even integer, no rounding occurs.  Example  Formula  Result  =Even(2.5)  4  =even(1.5)  2    FACT  Returns the factorial of a number. The factorial of a number is equal to 1*2*3*...* number.  Syntax  FACT(number)  Number is the nonnegative number you want the factorial of. If number is not an integer, it is truncated.  Example  Formula  Result  =FACT(4)  24  =FACT(6)  720    FLOOR  Rounds number down, toward zero, to the nearest multiple of significance.  Syntax  FLOOR(number,significance)  Number is the numeric value you want to round. Significance is the multiple to which you want to round.  Remarks  • If either argument is nonnumeric, FLOOR returns error value.
• If number and significance have different signs, FLOOR returns error value.  Example  Formula  Result  =FLOOR(25.5, 1)  25  =FLOOR(25.5, 2)  24    POWER  Returns the result of a number raised to a power.  Syntax  POWER(number,power)  Number is the base number. It can be any real number.
Power is the exponent to which the base number is raised.  Remark  The ^ operator can be used instead of POWER to indicate to what power the base number is to be raised, such as in 5^3.  Example  Formula  Result  =POWER(5,3)  125  =POWER(15.2,3.3)  7944.81594    PRODUCT  Multiplies all the numbers given as arguments and returns the product.  Syntax  PRODUCT (number1,number2,...)  Number1, number2, ... are 1 to 30 numbers that you want to multiply.  Remarks  • Arguments that are numbers, logical values, or text representations of numbers are counted; arguments that are error values or text that cannot be translated into numbers cause errors.
• If an argument is an array or reference, only numbers in the array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored  Example  Formula  Result  =PRODUCT(10,20,30)  6000    SIGN  Determines the sign of a number. Returns 1 if the number is positive, zero (0) if the number is 0, and 1 if the number is negative.  Syntax  SIGN(number)  Number is any real number.  Example  Formula  Result  =SIGN(0.001)  1  =SIGN(45)  1    ROUND  Rounds a number to a specified number of digits.  Syntax  ROUND(number,num_digits)  Number is the number you want to round.
Num_digits specifies the number of digits to which you want to round number.  Remarks  • If num_digits is greater than 0 (zero), then number is rounded to the specified number of decimal places.
• If num_digits is 0, then number is rounded to the nearest integer.
• If num_digits is less than 0, then number is rounded to the left of the decimal point.  Example  Formula  Result  =ROUND(192.54647,2)  192.55  =ROUND(192.54647,1)  190    SQRT  Returns a positive square root.  Syntax  SQRT(number)  Number is the number for which you want the square root.  Remark  If number is negative, SQRT returns the #NUM! error value.  Example  Formula  Result  =SQRT(36)  6  =SQRT(50)  7.071067812    SUM  Adds all the numbers in a range of cells.  Syntax  SUM(number1,number2, ...)  Number1, number2, ... are 1 to 30 arguments for which you want the total value or sum.  Remarks  • Numbers, logical values, and text representations of numbers that you type directly into the list of arguments are counted. See the first and second examples following.
• If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, or text in the array or reference are ignored. See the third example following.
• Arguments that are error values or text that cannot be translated into numbers cause errors.  Example  Formula  Result  =SUM(10,20,30,40)  100   Autosum  Use the Autosum function to add the contents of a cluster of adjacent cells  1. Select the cell that the sum will appear in that is outside the cluster of cells whose values will be added. Cell C2 was used in this example.
2. Click the Autosum button (Greek letter sigma) on the standard toolbar.
3. Highlight the group of cells that will be summed (cells A2 through B2 in this example).  4. Press the ENTER key on the keyboard or click the green check mark button on the formula bar.  
Financial Functions  Excel Provide us a wide variety ofFinancial functions some of them are listed below:  Function  Description  IPMT  Returns the interest payment for an investment for a given period  PMT  Returns the periodic payment for an annuity    IPMT  Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.  Syntax  IPMT(rate,per,nper,pv,fv,type)  Rate is the interest rate per period.  Per is the period for which you want to find the interest and must be in the range 1 to nper.  Nper is the total number of payment periods in an annuity.  Pv is the present value, or the lumpsum amount that a series of future payments is worth right now.  Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).  Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.  Set type equal to  If payments are due  0  At the end of the period  1  At the beginning of the period   Remarks  • Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a fouryear loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper.
• For all the arguments, cash you pay out, such as deposits to savings, is represented by negative numbers; cash you receive, such as dividend checks, is represented by positive numbers.  Example  Rate  Per  Nper  Pv  Fv  Type  IPMT  0.1  1  3  100000   0  ($833.33)    PMT  Calculates the payment for a loan based on constant payments and a constant interest rate.  Syntax  PMT(rate,nper,pv,fv,type)  For a more complete description of the arguments in PMT, see the PV function.  Rate is the interest rate for the loan.  Nper is the total number of payments for the loan.  Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal.  Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.  Type is the number 0 (zero) or 1 and indicates when payments are due.  Set type equal to  If payments are due  0  At the end of the period  1  At the beginning of the period  
Date & Time Functions  Excel Provide us a wide variety of Data & Time functions, some of them are listed below:  Function  Description  DAYS360  Calculates the number of days between two dates based on a 360day year  NOW  Returns the serial number of the current date and time  TODAY  Returns the serial number of today's date    DAYS360  Returns the number of days between two dates based on a 360day year (twelve 30day months), which is used in some accounting calculations. Use this function to help compute payments if your accounting system is based on twelve 30day months.  Syntax  DAYS360(start_date,end_date,method)  Start_date, end_date are the two dates between which you want to know the number of days. If start_date occurs after end_date, DAYS360 returns a negative number. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.  Method is a logical value that specifies whether to use the U.S. or European method in the calculation.  Remarks  Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.  Example  Start Date  End Date  Formula  Difference  1/30/2007  2/1/2008  =DAYS360(1/30/2007,2/1/2008)  361   NOW  Returns the serial number of the current date and time. If the cell format was General before the function was entered, the result is formatted as a date.  Syntax  NOW( )  For a more complete description of the arguments in PMT, see the PV function.  Formula  Result  =NOW()  Current Date & Time    Today  Returns the serial number of the current date. The serial number is the datetime code used by Microsoft Excel for date and time calculations. If the cell format was General before the function was entered, the result is formatted as a date.  Syntax  TODAY( )  Formula  Difference  =TODAY()  Current Date  
Text Functions  Excel Provide us a wide variety of Text Functions, some of them are listed below:  Function  Description  CHAR  Returns the character specified by the code number  CODE  Returns a numeric code for the first character in a text string  CONCATENATE  Joins several text items into one text item  EXACT  Checks to see if two text values are identical  LOWER  Converts text to lowercase  PROPER  Capitalizes the first letter in each word of a text value  REPLACE  Replaces part of a Text String with different text String  SUBSTITUTE  Substitutes new text for old text in a text string  TRIM  Removes spaces from text  &UPPER;  Converts text to uppercase    CHAR  Returns the character specified by a number. Use CHAR to translate code page numbers you might get from files on other types of computers into characters.  Syntax  CHAR(number)  Number is a number between 1 and 255 specifying which character you want. The character is from the character set used by your computer..  Example  Formula  Result  =CHAR(36)  $  =CHAR(90)  Z    CODE  Returns a numeric code for the first character in a text string .The returned code corresponds to the character set used by your computer.  Syntax  CODE(text)  Text is the text for which you want the code of the first character.  Example  Formula  Result  =CODE(“Z”)  90  =CODE(“$”)  36    CONCATENATE  Joins several text strings into one text string.  Syntax  CONCATENATE (text1,text2,...)  Text1, text2, ... are 1 to 30 text items to be joined into a single text item. The text items can be text strings, numbers, or singlecell references.  Remarks  You can also use the ampersand (&) calculation operator instead of the CONCATENATE function to join text items. For example, =A1&B1 returns the same value as =CONCATENATE(A1,B1).  Example   A  B  C  D  1    Formula   2  Hello  World!  =CONCATENATE(A2,B2)  Hello World!  3        EXACT  Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. Remember EXACT is casesensitive but ignores formatting differences. We can use EXACT to test text being entered into a document .  Syntax  EXACT(text1,text2)  Text1 is the first text string.
Text2 is the second text string.  Example   A  B  C  D  1    Formula  Result  2  Hello  World!  =Exact(A2,B2)  FALSE  3        LOWER  Converts all uppercase letters in a text string to lowercase.  Syntax  LOWER(text)  Text is the text you want to convert to lowercase. LOWER does not change characters in text that are not letters.  Example   A  B  C  D  1    Formula  Result  2  Hello  World!  =LOWER(A2)  hello world!  3        REPLACE  REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string.  Syntax  REPLACE(old_text,start_num,num_chars,new_text)  Old_text is text in which you want to replace some characters.  Start_num is the position of the character in old_text that you want to replace with new_text.  Num_chars is the number of characters in old_text that you want REPLACE to replace with new_text.  Num_bytes is the number of bytes in old_text that you want REPLACEB to replace with new_text.  New_text is the text that will replace characters in old_text.  Example   A  B  C  D  E  1  OLD Text  Start_num  Num_chars  Formula  Result  2  Hai User  1  3  =REPLACE(A2,1,3,"Bye")  Bye User  3         SUBSTITUTE  Substitutes new_text for old_text in a text string. We can use SUBSTITUTE when we want to replace specific text in a text string, otherwise we can use REPLACE when we want to replace any text that occurs in a specific location in a text string.  Syntax  SUBSTITUTE(text,old_text,new_text,instance_num)  Text is the text or the reference to a cell containing text for which you want to substitute characters.  Old_text is the text you want to replace.  New_text is the text you want to replace old_text with.  Instance_num specifies which occurrence of old_text you want to replace with new_text.  If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.  Example   A  B  C  1   Formula  Result  2  Hi Vishwjeet  =SUBSTITUTE(D31,Vishwjeet,Ravish)  Hi Ravish    TRIM  Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.  Syntax  TRIM(text)  Text is the text from which you want spaces removed.  Example   A  B  C  1   Formula  Result  2  Hi Ravish  =TRIM(A2)  Hi Ravish    UPPER  Converts text to uppercase for example: hello to HELLO etc.  Syntax   UPPER(text)  Text is the text you want converted to uppercase. Text can be a reference or text string.  Example   A  B  C  1   Formula  Result  2  hi ravish  =UPPEAR(A2)  HI RAVISH  
Statistical Functions  Excel Provide us a wide variety of Text Functions, some of them are listed below:  Function  Description  AVERAGE  Returns the average of its arguments  COUNT  Counts how many numbers are in the list of arguments  COUNTA  Counts how many values are in the list of arguments  COUNTIF  Counts the number of nonblank cells within a range that meet the given criteria    AVERAGE  Returns the average (arithmetic mean) of the arguments.  Syntax  AVERAGE(number1,number2,...)  Number1, number2, ... are 1 to 30 numeric arguments for which you want the average.  Example   A  B  1  10   2  20   3  29   4  22   5  =AVERAGE(A1:A4)  Formula  6  20.25  Result    COUNT  Counts the number of cells that contain numbers and counts numbers within the list of arguments. Use COUNT to get the number of entries in a number field that in a range.  Syntax  COUNT(num1,num2,...)  num1, num2, ... are 1 to 30 arguments that can contain or refer to a variety of different types of data, but only numbers are counted.  Example   A  B  1  10   2  20   3  29   4  Hello   5  =COUNT(A1:A4)  Formula  6  3  Result    COUNTA  Counts the number of cells that are not empty and the values within the list of arguments. We can use COUNTA to count the number of cells that contain data in a range.  Syntax  COUNTA(Num1,Num2,...)  Num1, Num2, ... are 1 to 30 arguments representing the values you want to count.   A  B  1  10   2  20   3  29   4  Hello   5  =COUNTA(A1:A4)  Formula  6  4  Result    COUNTBLANK  Counts empty cells in a specified range of cells.  Syntax  COUNTBLANK(range)  Range is the range from which you want to count the blank cells.  Remark  Cells with formulas that return "" (empty text) are also counted. Cells with zero values are not counted.  Example   A  B  1  10   2  20   3    4  Hello   5  =COUNTBLANK(A1:A4)  Formula  6  1  Result  

No comments:
Post a Comment