Microsoft Excel (part 2)

Inserting Graphics

Insert Picture from Clipart

In MS Excel we can also add variety of graphics objects. Microsoft provide us built in collection of variety of images with MS office known as Clipart.

To add a clip art image to the worksheet, follow these steps:

Click on Insert--> Picture--> Clip Art from the menu bar.

In Office 2000 and earlier version it will display the following dialog box

Click one of the category icons.

Click once on the image you want to add to the worksheet and the following popup menu will appear:

• Insert Clip to add the image to the worksheet.

• Preview Clip to view the image full-size before adding it to the worksheet. Drag the bottom, right corner of the preview window to resize the image and click the "x" close button to end the preview.

• Add Clip to Favorites will add the selected image to your favorites directory that can be chosen from the Insert ClipArt dialog box.

• Find Similar Clips will retrieve images similar to the one you have chosen.

Continue selecting images to add to the worksheet and click the Close button in the top, right corner of the Insert ClipArt window to stop adding clip art to the worksheet.

in Office XP and later versions it will display a task pane in right side of Excel window as sown below:

In the Search Text textbox type the kind of image we are look for (ie flower, computer etc) and click on Search.

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 double-ended 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 three-dimensional shapes, icons, braces, and brackets. Use the drag-and-drop 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 three-dimensional arrows. Drag-and-drop 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 drop-down 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 format-this 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 left-most 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 right-click 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.12574E-17 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(4-5) -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 lump-sum 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 four-year 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 360-day 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 360-day year (twelve 30-day months), which is used in some accounting calculations. Use this function to help compute payments if your accounting system is based on twelve 30-day 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 date-time 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 single-cell 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 case-sensitive 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