SPREAD SHEET

MICROSOFT EXCEL
Ms-Excel is a package (Software/Programm). Ms-Excel is used for mathematical, statistical, scientific, graphics, tabulation, database and accounting purpose. It consist of rows and columns. Columns are named as A, B, C, D.................................IV and rows are named as 1,2,3,4.........................65536. It has 256 columns and 65536 rows. Ms-Excel provides the ability to perform calculation, format reports, create charts (graphics), and even provides a simple database facility.
HOW TO START MS-EXCEL 
To start Ms-Excel follow the following steps.

1. Click start button.
2. Point out programm, a sub menu display
3. Click Ms-Excel.

DIFFERENCE BETWEEN MANUAL AND ELECTRONIC (COMPUTER) SPREADSHEET
Electronic (Computer Spreadsheet) are far better than manual Spreadsheet in all most all aspect. We will discuss only some of the benefits of using a Electronic spreadsheet.

1. Electronic spreadsheets work thousand of times faster than the manual spreadsheet. Your work in an electronic spreadsheet is better, faster and precise than manual spreadsheet.
2. In the manual spreadsheet, if you find an error after making it, you have to erase it or you will have to make the whole worksheet again. Whereas in electronic worksheet, you can check and correct your worksheet before printing. Also you get the facilities of spell checking, finding and replacing, auto-correcting and many more, which give more part of the work to the computer and less to you.
3. You can copy the formula once you made to all other amounts in the worksheet, while in the manual worksheet you are to calculate the formula for every amount.
4. You can make your worksheet more beautiful and smart by adding charts and graphical objects, while the manual worksheet is a dumb-looking piece of white paper. No charm, no glamor.
5. The electronic worksheet is much bigger than the manual worksheet.
6. Saving your worksheets a big problem you can have with the manual work. Every time you make a worksheet for one purpose, you are to save it in a different file. Also you are to store the previous worksheets to keep track of what going on. In the electronic work, you can save them on you disk, which saves the loos of paper and you can move many worksheets on one floppy disk, USB device in your pocket. Other way, you would have to pick a bundle of papers with you.
7. Other benefits contain the tools for spell-checking, built-in formula list, different fonts and size, automatic alignment, copying and pasting, linking and many more which are difficult to be listed here. However, once you get started with a electronic worksheet, it looks damn difficult to go back to the manual method.

DIFFERENT TERMINOLOGY USE IN MS-EXCEL

WORKBOOK
Ms-Excel file is called workbook. Workbook is combination of sheets. A workbook is the file in which you work and store your data. Because each workbook can contain many sheet, you can organize various kinds of related information in a single file. By default every workbook contains three sheets. We can increase or decrease the number of sheets in workbook.

WORKSPACE
workspace as a group of workbook file. Workspace is used to save a group of workbooks in a workspace. You can open a group of workbooks in one step by creating a workspace file. A workspace file saves information about all open workbooks, such as their locations, window sizes, and screen position. The workspace file does not contain the workbooks themselves, and you must continue to save changes you make to the individual workbooks.

1. Open the workbooks you want to open as a group.
2. Size and position the workbook windows as you want them to appear the next time you use the workbooks.
3. On the File menu, click Save Workspace.
4. In the File name box, enter a name for the workspace file.

WORKSHEET / SPREAD SHEET

Worksheet is used to list and analyse data. You can enter and edit data on several worksheets simultaneously and perform calculations. Think of them as a powerful multi purpose calculator, capable of every thing from simple to complicated calculation. Each worksheet is divided into row, column and cell separate by gridlines. The mane of the sheet apear on tabs at the bottom of the workbook window. To move from sheet to sheet, click the sheet  tabs. The name of the active sheet is bold.

COLUMN
Column are vertical division of worksheet. The column name are letter. The first column is column A, and the last column is called IV. Each worksheet has 256 columns (A through IV).
ROWS
Rows are horizontal division worksheet. Each row is separated by horizontal gridlines. Rows are numbered. The first row is 1 and the last row is known as 65536. Each worksheet has 65536 rows (1 through 65536).
CELL
The intersection of a row and column is a rectangle area is called cell. Cell is the basic unit of spreadsheet. Worksheet consists of columns A,B,C,D,........... and row 1,2,3,............ Column and row when combined make a cell, such as A12, B3, F11, M921............. A cell can contain a value, a formula or a text entry.
CELL ADDRESS
Cell address (reference) are the compbination of column letter and row number. Each cell has uniqe address. For example, the upper left cell of a worksheet is A1.
GRIDLINES
The lines on the worksheet dividing it into rows and columns. These lines are normally non-printable and we easily examine the data, its rows and columns. These gridlines cut each other throughout the worksheet to make the boundaries for each cell.
FORMULA BAR
Formula bar is display below the tool bar and above the sheet column name. The formula bar is used to show the formula.
Othe bar is maximum the same is in Ms-Word.

CREATING, OPENING, RENAMING, SAVING, CLOSING, WORKBOOK/SPREADSHEET

1. CREATE A NEW WORKBOOK (Ctrl + N)
   This command is used to create a new workbook file. Follow the following steps.
• Click on File menu
• Click on New 
• To create a new, blank workbook, click the General tab, and then double click the workbook icon.
2. OPEN AN EXISTING WORKBOOK (Ctrl + O)
   This command is used to openj an existing workbook file by follow the following steps
• Click on the File menu
• Click Open
• In the Open Window select from Look in field dreive, folder, or FTP location that contains the workbook you want to open, and then locate and double click the folder that contains the workbook.
3. RENAME A WORKBOOK/FILE
   This command is used to change the name of an existing workbook or file.
• Click Open
• In the Look in box, click the drive or folder that contains the file which you want to rename.
• In the folder list, double click folders that contains the file you want.
• Right click the file you want to rename, anhd the click Rename option on the shortcut menu.
  Type the new name, and then press ENTER.
4. SAVING A WORKBOOK WITH SAVE AS OPTION (F12)
   This command is used to save a new, unnamed workbook or to save a workbook with a new name.
• Click on File menu, Click Save As
• In the Save As window Save in field select the drive and folder where you want to save the workbook.
• In the File name box, type a name for the workbook.
• Click Save or press Enter.
5. SAVE AN EXISTING WORKBOOK WITH SAVE OPTION (Ctrl + S)
   This command is used to save a workbook with the same name or to save new workbook first time.
• Click Save in File menu or press Ctrl + S.
6. CLOSING A WORKBOOK/SPREAD SHEET (Ctrl + F4 or Ctrl + W)
   This command is used to close an opened workbook by follow the following steps:
• Click on the File menu
• Click on Close option or press Ctrl + W or Ctrl + F4.
  
  To close all open workbooks/spread sheet without exiting the program, hold down SHIFT, click on the File menu and then click Close All. 

 EDITING FUNCTION

1. UNDO (Ctrl + Z)
   This procedure is used to undo any changes you mad. To undo any changes follow the following steps
   
   • Click Undo in Edit menu on Menu Bar
   OR
   UNDO MISTAKES
   • To undo recent actions one at a time, click Undo
     Tp imdp severa; actopms at pmce. c;ocl tje arrow mext to Undo and select from the list.
     Microsoft Excel reverses the selected action and all actions above it.
2. REDO (Ctrl + Y)
   This procedure is used to reverse the action of the Undo command. To use4 Redo follow the following steps:
   
   • Click Redo in Edit menu on Menu bar.
   OR
   To Redo one action at a time, click Redo.
   To Redo more than one action at a time click the arrow next to and the click the Redo icon in Standard tool bar.
3. FILL (Ctrl + D or Ctrl + R)
   This command is used to copies the contents and format of the cell or selected into the cells, down, up, right, and left any one. Follow the following steps.
   
   • Select the range which you want to fill 
   • Point to Fill in Edit menu
   • Select Dowin, Up, Right or Left any one.
4. AUTO FILL
   • Select the cell that contain the data you want to copy.
   • Drag the fill handle across the cells you want to fill, and then release the mouse button.
     OR
   • Enter first cell value and the select range wich you want to fill.
   • Point to fill in Edit menu and click on Series.
   • A Series dialog box appear select different option.
   • Enter the increase value in Step Value Box and end value in the Stop Value Box.
5. DELETE (DEL.key)
   This procedure is used to delete cells, row, or column .
   
   • Select cells, row, or column you want to delete
   • On edit menu Click Delete.
   • Select any radio button from Delete Dialog Box.
   • Press OK button.
6. CUT (MOVE) Cell Entries.(Ctrl +X & Ctrl + V)
   To cut a cell or range of cells contents to another location. Follow the following steps.
   
   • Select the cell or range of cell you want to cut or move.
   • Click on Edit menu.
   • Click Cut. 
   • Move to the cell or select the cell where you want to move.
   • Click on the Edit menu 
   • Click Paste option.
     OR by short cut key
   • Select cell or range of cells 
   • Press Ctrl + X.
   • Move to the cell or select cell where you want Cut.
   • Press Ctrl + V.
7. COPY CELL ENTRIES (Ctrl + C and Ctrl + V)
   To copy a cell or range of cells contents to another location. Follow the following steps:
   
   • Select the cell or range of cells you want to copy.
   • Click on the Edit menu.
   • Click Copy.
   • Move to the cell or select the cell where you want to copy.
   • Click on the Edit menu.
   • Click Paste option.
     Or by short cut key 
   • Select cell or range of cells and then press Ctrl + C. 
   • Move to the cell or select cell where you want to Copy.
   • Press Ctrl + V.
8.  PASTE (Ctrl + V)
   To paste procedure is used to insert the contents of the clipboard at the insertion point. This command is only available if you have cut or copied an object, text, or contents of a cell or range of cells. Follow the following steps:
   
   • Click on Pastte in edit menu. Or press Ctrl + V. 
9. CLEAR
   This procedure is used to erase or remove the contents of a cell or a range of cell.
   
   • Select the cells you want to clear.
   • Click on the Edit menu.
   • Point to Clear.
   • Click any one from (All, Contents, Formats or Comments).
   Or
   
   • Select the cell or range of cells.
   • Press Delete key.
10.  FIND TEXT (Ctrl + F)
    This procedure is used to find / search selected cells or sheet for the character you specify follow the following steps:
    
    • Click on Edit menu in Menu Bar.
    • Click on Find, a find dialog box appear.
    • Type the character to search in the Find What Field.
    • Click on  Find Next to find one by one.
11. REPLACE TEXT: (Ctrl + H)
    This procedure is used to replace character or word with another character or word in the current worksheet. To replace a particular word or piece of character, follow the following steps:
    
    • Click on Edit menu on Menu Bar.
    • Click Replace, a replace dialog box appear.
    • Type the character to search in the Find Field.
    • Type the character or word to replace in Replace With Field.
    • Click Replace to replace one by one,  Or
      Click Replace All to replace all words.

 INSERTING

1.  HEADER AND FOOTER
   A header is text added to the top margin of page and footer is text added to the bottom margin of page.
   The following procedure is used to insert / change header and footer at each page in a worksheet.
   
   • Click on the worksheet to which put header and footer.
   • Click on the View menu.
   • Click on Header or Footer.
   • Click in the Left section, Center section, or Right section box, enter any text or data for header or footer or click the the buttons to insert on header or footer othe information - such as the page number - that you want in that section.
2. INSERT CELLS / INSERT BLANK CELL
   This command is used to insert a cell or range of cells in a worksheet. Inserts cells starting at the insertion point. You can choose to shift other cells in the table to the right or down.
   
   • Select the number of cells as you want to insert.
   • Click on the Insert menu.
   • Click Cells.
   • A dialog box appear on screen.
   • Click Shift cells right or Shift cell down radio button.
3. INSERT ROW
   Insert a Row
   This procedure is used to insert a row in a worksheet, follow the following steps.
   
   • Click a cell in the row where you want to insert a row.
   • Click on Insert menu.
   • Click on Row.
   TO INSERT MULTIPLE ROWS
   This procedure is used to insert more than one row in a worksheet. Follow the following steps.
   
   • Select cells in the column where you want to insert rows.
   • Click on Insert menu.
   • Click on Rows.
4. INSERT COLUMN
   Insert a column (Single Column)
   This command is used to insert a column in a worksheet. Follow the following steps:
   
   • Click a cell in the column where you want to insert a column.
   • Click on Insert menu.
   • Click on Column.
   INSERTION OF MULTIPLE COLUMNS
   This command is used to insert range of columns in a worksheet. Follow the following steps:
   
   • Select range of cells in the column where you want to insert column.
   • Click on Insert menu.
   • Click on Column.
5. WORKSHEET
   Insert a new Worksheet
   This procedure is used to add a single new worksheet to the left of selected sheet. tab.
   
   • Click on the Insert menu.
   • Click on Worksheet.
   INSERT MULTIPLE WORKSHEETS
   This procedure is used to add new multiple worksheets to the left of selected sheet tab.
   
   • Hold down SHIFT.
   • Click the number of worksheet tabs you want to add in the open workbook.
   • Click on Worksheet in Insert menu.
    

WORKING WITH FORMULA 

What is Formula? 

A formula is an equation that performs operations on worksheet data. Formula can perform mathematical operations, such a addition and multiplication, or they can compare worksheet values or join text. Formula can refer to other cells on the same worksheet, cells on other sheets in the same workbook, or cells on sheets in other workbooks.

1. ENTERING FORMULA WITH THE HELP OF FORMULA BAR
   • Click the cell in which you want to enter the formula.
   • To start the formula with the function, click Edit Formula Symbol (=) in the formula bar.
   • Click the down arrow next to the Functions box. 
   • Click the function you want to add to the formula. If the function does not appear in the list, click More Functions for a list of additional functions.
   • Enter the argument that is reference of cell or value.
   • When you complete the formula, press ENTER.
2. ENTERING FORMULA DIRECTLY IN TO A CELL
   This procedure is used to enter a formula directly in cell in an opened sheet.
   • Click the cell in which you want to enter the formula.
   • Type = (an equal sign).
   • Enter the formula and press Enter key.
   For example in above figure:
   =SUM(A1:A3) equals 6
   =PRODUCT (A1:A3) equal 6
   =2+3*4 equal 14
3. USE OF ARITHMETIC & LOGICAL FORMULA
   • Use Arithmetic Formula
     
     Formula calculate values in a specific order. A formula in Microsoft Excel always begins with an equal sign (=). The equal sign tells Excel that the succeeding characters constitute a formula. Following the equal sign are the elements to be calculated (the operands), which are separated by calculation operator. Excel calculates the formula from left to right, according to a specific order for each operator in the formula. You can change the order of operation by using parentheses.
     In the example below, the parentheses around the first part of the formula force Excel to calculate B4+25 first and then divide the result by the sum of the values in cells D5, E5, and F5.
     =(B4+25)/SUM(D5:F5)
     OR
     =(2+3)/5 first calculate 2+3 is equal to 5 and the divide by 5 result is 5. It is arithmetical formula. 
   • Logical Formula
     Formula that calculate values and gives result true or false is called logical formula. the If function is called logical function. The IF worksheet function checks a condition that must be either true or false. If the condition is true, the function returns one value; if the condition is false; the function returns another value. The function has three arguments: the condition you want to check, the value to return if the condition is true, and the value to return if the condition is false.
     =IF(logical_text,value_if_true,value_if_false)
     e.g. =IF(B4<10,"Less than 10","equal or greater than 10")
     if value in B4 is less than 10 it will give result Less than 10, if value in B4 not less than 10 it will give result Equal or greater than 10.

EDIT FORMULA

This procedure is used to edit a formula for modification.

1. Double click the cell that contains the formula you want to edit or press F2.
2. Make changes to the formula.
3. Press Enter button.

Display Formula / Values in a cell 

This procedure is used to display formula or formula values (result) in a cell of a sheet.

Displaying formula

For displaying formula use the following steps:

1. Click on Tools menu on menu bar.
2. Click on option.
3. Option dialog box display on screen.
4. Click on View Tab.
5. Click on Formula check box to tick it.
6. Click OK button.

Displaying Values of Formula (Result) 

For displaying formula values (result) use the following steps:

1. Click on Tool menu on menu bar.
2. Click on Option.
3. Option Dialog box display on screen
4. Click on View Tab.
5. Click on Formula Check Box to UN-check it.
6. Click OK button.

Freeze Formula Value 

This procedure is used to freeze the formula value that is if we change the values of the cells given in formula range the value of the formula does not change from it previous calculated value.

Step:

1. Click on Tool menu.
2. Click on Option an option dialog box display on screen.
3. Click on calculation tab on option dialog box..
4. Click on manual radio button to on it (check).
5. Click on OK button.

If you want to recalculate press F9 key on key board.

Relative, Absolute and Mixed Cell Reference 

A Cell or range of references
A reference identifies a cell or a range of cells on a worksheet and tells Microsoft Excel where to look for the values or data you want to use in a formula. With references, you can use data contained in different part of a worksheet in one formula or use the value from one cell in several formulas. You can also refer to cells on other sheets in the same workbook, to other workbooks, and to data in other programms. References to cells in other workbooks are called external references. References to data in other programms are called remote references.

Relative References  

Relative cell references, which are references to cells relative to the position of the formula. Relative references automatically adjust when you copy them. 

When you create a formula, references to cells or ranges are usually based on their position relative to the cell that contains the formula. In the following example, cell A5 contains the formula =SUM(A1:A3) Microsoft Excel finds the value from A1 to A5. this known as relative reference.

When you copy a formula that uses relative references, Excel automatically adjusts the references in the pasted of the formula. In the following example, the formula in cell A5 =SUM(A1:A3), which is from A1 to A3, has been copied to cell B5. Excel has adjusted the formula in cell B5 to =Sum(B1:B3), which refers to the cells. from B1 to B3.

Absolute Reference 

Absolute references, which are cell references that always refer to cells in a specific location. If a dollar sign precedes the letter and / or number, such as $A$1, the column and / or row reference is absolute. Absolute references don't adjust when you copy them.

The Difference Between Relative & Absolute References

If you don't want Excel to adjust references when you copy a formula to a different cell, use an absolute reference. For example, if your formula multiplies cell A2 with cell C2=(A2*C2) and you copy the formula to another cell, Excel will adjust both references. You can create an absolute reference to cell A2 by placing a dollar sign ($) before the parts of the reference that do not change. To create an absolute reference to cell A2, for example, add dollar signs to the formula as shown in the follows figure =$A$2*B5
In cell C2 we enter formula =$A$2*C2. We copy this formula from C2 to C3 and upto C5. The value of B2 change because it is relative and the value of $A$2 does not change because it is absolute value.

Mix Cell Reference
If you want Excel to adjust references when you copy a formula to a different cell, use an absolute reference and relative both. For example, if your formula divide cell C2 by $B2 and multiply by 100 as in D2 =(C2/$B2*100) and you copy the formula to another cell, Excel will adjust both references. You can create a relative references to cell C2 and an absolute reference to column B by placing dollar ($) sign before the part of the reference that don not change and relative to row 2 that change its value from 2 to 4. To create an absolute reference to cell A2, for example, add dollar signs to the formula as shown in the follows figure
=$A$2*B5
In cell D2 we enter formula =C2/$B2*100. We copy this formula from D2 to D3 and D4. The value of C2 change because it is relative and the value of $B does not change because it is absolute value and the value of 3 in $B3 changes because it is relative and 100 is constant so =C2/$B2*100 is Mixed formula.

Switching Between Relative and Absolute References
 If you created a formula and want to change relative references to absolute (and vice versa), select the cell that contains the formula. In the Formula Bar, select the reference you want to change and press F4. Each time you press F4. Excel toggles through the combinations: absolute column and absolute row (for example, $C$1); relative column and absolute row (C$1); absolute column and relative row ($C1); and relative column and relative row(C1). For example, if you select the address $A$1 in a formula and press F4, the reference becomes A$1. Press F4 again and the reference becomes $A1, and so on.

USE OF BUILT IN FUNCTION
About Using Function to Calculate Values
Functions are predefined formulas that perform calculation by using specific values, called arguments, in a particular order, or structure, for example; the SUM function adds values or ranges of cells.

Arguments
Arguments can be numbers, text, logical values such as TRUE or FALSE. The argument you designate must produce a valid value for that argument. Arguments can also be Constants formulas, or other functions. For more information about using a function as an argument for another function, also known as nesting functions, click.

Structure
The structure of a function begins with the function name, followed by an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis. If the function starts a formula, type a equal sign (=) before the function name. As you create a formula that contains a function, the Formula Palette will assist you.

1. DATE AND TIME FUNCTION
   
   • DATE
     This function is used to return a particular date.
     Syntax
     =DATE(year, month, day)
     Year
     The year argument can be one to four digits. Excel interprets the year argument according to the date system you are using.
     =DATE(2004,2,19) equals February 19th, 2004.
   • TIME
     This function is used to return a particular time according to a specified format.
     Syntax
     =TIME(hour,minute,second)
     Hour is a number from 0 (zero) to 23 representing the hour.
     Minute is a number from 0 to 59 representing the minute.
     Second is a number from 0 to 59 representing the second.
     Example
     TIME(12, 0, 0) equal to 12:00:00 PM
     TIME(16, 45, 10) equal to 04:45:10 PM
     TEXT(TIME(23, 18, 14), "hh:mm:ss AM/PM" equal to "11:18:14 PM"
     In the above figure
     =TIME(18, 12, 15) equal to 06:12 PM or 18:12:15
    
2. CONCATENATE
   This function is used to 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, number or single - cell references.
   Remarks
   The "&" operator can be used instead of CONCATENATE to join text items.
   Example
   CONCATENATE("Total","Value") equals "Total Value". This is equivalent to typing
   " Total"&" "&"Value"
   Suppose in a stream survey worksheet, C2 contains "species", C5 contains "brook trout", and C8 contains the total 32.
   CONCATENATE("Stream population for", C5," "C2," is ", C8,"/mile")
3. SUM
   This function is used to Sum two or more number.
   For example:
   =SUM(2,4) To add 2, 4 the result is 6.
   =SUM(A1:A10) To add number from cell A1 to cell A10 etc.
4. IF
   Return one value if a condition you specify evaluates to TRUE and another value if the evaluate to FALSE.
   Use IF to conduct conditional test on values and formulas.
   Syntax
   IF(logical_test,value_if_true, value_if_false)
   Logical_text is any value or expression that can be evaluated to TRUE or FALSE.
   For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.
   Value_if_true is the value that is returned if logical_test is TRUE. For example, =IF(A1>10,"Greater","Less"), if the value in cell A1>10 True the result will be Greater. Value if false is the value that is returned if logical test is FALSE
   For example, =IF(A1>10,"Greater","Less") if the value in cell A1>10 False the result will be Less.
5. NESTE IF FUNCTION 
   You can use IF in nested form in the following example.
   =IF(B2>=60,"First",IF(B2>=40,"Second",IF(B2>=33,"Thrid",IF(B2<33,"Fail))))
6. MAX 
   Return the largest number in a set of values.
   =MAX(number1,number2,......)
   Number1, number2,....... are 1 to 30 numbers for which you want to find the maximum value.
   Example
   If A1=10, A2=7, A3=9, A4=27 AND A5=2 then:
   =MAX(A1:A5) equal to 27
   =MAX(A1:A5, 30) equal to 30
7. MIN
   Returns the smallest number in a set of values.
   Syntax
   =MAX(number1,number2,.......)
   Number1, number2,...... are 1 to 30 numbers for which you want to find the minimum value.
   Example
   If A1=10, A2=7, A3=9, A4=27 AND A5=2 then:
   =MIN(A1:A5) equal to 2
   =MIN(A1:A5, 0) equal to 0
8. AVERAGE
   This function 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
   If A1=10, A2=7, A3=9, A4=27 AND A5=2 then:
   =AVERAGE(A1:A5) equal to 11
   =AVERAGE(A1:A5, 5) equal 10
   =AVERAGE(A1:A5) equals
   =SUM(A1:A5)/COUNT(A1:A5) equal to 11
9. COUNT
   This function is used to counts the number of numeric cells within a range.
   =CONT(range)
   For example in figure
   =COUNT(A1:A6) equals 4 because there are 4 numeric between A1 to A6
   COUNTA: This function is used to Counts the number of cells that have alphabetic data or numeric data or alphanumeric data.
   Syntax
   =COUNTA(range)
   for example in above figure
   =COUNTA(A1:A6) equal to 5
   because there are f fill cells between A1 to A6
   COUNTBLANK: This function is used to counts the number of blank cells in specified range.
   Syntax
   =COUNTBLANK(range)
   In the given figure
   =COUNTBLANK(A1:A6) EQUAL 2
10. NOW()
    Return the current date and time in cell.
11. MONTH
    Return the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (January) to 12 (December).
    Syntax
    MONTH("Year/Month/Day")
    Example
    MONTH("6-May") equal to 5
    MONTH(2004/04/01") equal to 4
12. DAY
    Return the day of a date represented by a serial number. The is given as an integer, ranging from 1 to 31.
    Syntax
    DAY("Year/Month/Day")
    =Day("4-Jan") equal to 4
    =DAY("15-Apr-2012") equal to 15
    =DAY(("8/11/2012") equal to 11
    =DAY("2012/10/10") equal to 10
13. YEAR
    Return the year corresponding to a date. The year is returned as an integer in the range 2000 to 2999
    Syntax
    YEAR("Year/Month/Day")
    For example =YEAR("7/5/2012") equal to 2012
    =YEAR(2006/05/01") equal to 2006
14. UPPER
    This function is used to change the text in to upper case.
    Syntax
    UPPER(text)
    Text is the text you want to converted to uppercase. Text can be a reference or text string.
    Example
    =UPPER("total") equal to TOTAL
    =If A1 contains "gcms", then:
    =UPPER(A1) equal to "GCMS"
15. LOWER
    This function is used to converts all uppercase letters in a text string into 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
    =LOWER("GCMS") equal to "gcms"
    =LOWER("Apt.2B) equal to "apt.2b"
16. LEFT
    LEFT returns the first character or characters in a text string, based on the number of characters you specify.
    Syntax
    =LEFT(text, num_chars)
    Text is the text string that contains the characters you want to extract.
    Num_chars specifies the number of characters you want LEFT to extract.
    
    • Num_chars must be greater than or equal to zero.
    • If num_chars is greater than the length of text, LEFT returns all of text.
    • If num_chars is omitted, it is assumed to 1.
    Example
    =LEFT("Sale Price",4) equal to "Sale"
    If A1 contains "KHALID", then:
    =LEFT(A1) equal to "K"
17. RIGHT
    RIGHT returns the last character or characters in a text string, based on the number of characters you specify.
    Syntax
    RIGHT(text, num_chars)
    Text is text string containing the characters you want to extract.
    Num_chars specifies the number of characters you want RIGHT to extract.
    Num_chars must be greater than or equal to zero.
    If num_chars is greater than length of text, RIGHT returns all of text.
    If num_chars is omitted, it is assumed to be 1.
    Example
    =RIGHT("Sale Price", 5) equal to "Price"
    =RIGHT("KHLID") equal to "D"

TOOLS AND DATA

1. SPELL CHECK (F7)
   This procedure is used to checks spelling in the active workbook. To apply spell check follows the following Steps:
   
   • Click on Spell in Tools Menu.
   • Spell check dialog box appear.
   • Select anu suggestion for every word.
   • Click on Change button.
   • IF you don't select any option then press Ignore button
2. AUTO CORRECT
   This procedure is used to correct word automatically. As you enter a word computer automatically replace. It to its store style.
   
   • One the Tool menu, click AutoCorrect.
   • In the Replace box, type a word or phrase that you often want to replace e.g. usu.
   • In the With box, type the word that you want replace with e.g. usually.
   • Click Add.
3. PROTECTION
   This procedure is used to protect a workbook or worksheet. To protect a sheet than you cannot change the content of cell.
   
   • Click on Tool menu.
   • Point to Protection, in drop down menu click Protect Sheet or Protect Workbook. OR Protect and Shared Workbook.
   • Click different option, enter password in Protect Sheet dialog box. And click on OK button.
4. DATA SORTING AND FILTERING DATA MENU
   • SORT
     This command is used to rearranges the information in selected rows or cells alphabetically, numerically, or by date in ascending or descending order.
   • Select rang of cells to sort.
   • Click on the Data menu.
   • Click on Sort a sort dialog box display on screen.
   • Select column on which you want sort as shown above Column A and you may also select other option.
   • Click Sort Ascending or Descending.
   • Click OK button.
   •  FILTER
     Filtering is a quick and easy way to find and work with a subset of data in a list. A filtered list displays only the rows that meet the criteria you specify for a column, Microsoft Excel provides two command for filtering lists:
     
     • AutoFilter, which includes filter by selection, for simple criteria.
     • Advance Filter, for more complex criteria.
     Unlike sorting, filtering does not re-arrange a list. Filtering temporarily hides rows, which you do not want to display.
     
     • Select range of cells to filter.
     • Click on the Data menu.
     • Click Filter.
     • Select AutoFilter or AdvanceFilter.
     • Click arrow in corner of select range and apply different option.

FORMATTING

1. FORMAT CELL AND TEXT
   This procedure is used to change the layout (format) of a cell or range of cell that is font, border, alinement etc.
   
   • Select a cell or range of cells you want to format.
   • Click on the Format menu.
   • Click Cell.
   • Select different option from Format Cell Dialog Box.
   • Click OK.
   TO INSERT DECIMAL PLACES
   
   • Select a cell of range of cells you want to format.
   • Click on the Format menu.
   • Click Cells.
   • Format Cell Dialog Box appear.
   • Select number from category list box.
   • Different format number appear, select any format from them
   • Enter number of decimal you want in decimal places box.
   • Press OK button.
   TO CONVERT SELECT CELLS IN TO TEXT
   This procedure is used to convert selected cells into text even if numerical will be treated as text and no calculation is performed on text.
   
   • Select a cell or range of cells you want to format.
   • Click on the Format menu. 
   • Format Cell Dialog Box appear.
   • Select Text from category list box.
   • Press OK button.
2. BORDER AND SHADING
   
   • BORDER
     This procedure is used to select border for selected cells.
     
     • Select the cells you want to add borders to. 
   • Click on the Format menu.
   • Click Cells.
   • Format Cell Dialog Box appear.
   • Click on Border tab, different border option appear.
   • Select different option according to your requirement.
   • Press OK button. 
   • SHADING
     This procedure is used to select fill color for selected cells.
   • Select the cell or range of cells, you want to add shad to.
   • Click on arrow next to Fill Colour button on Formatting Toolbar.
   • Select any color from color palette.
3. POSITIONING CELLS AND TEXT
   This procedure is used to change the position of the selected cells. That is horizontal and vertical etc. Follow the following steps:
   
   • Select the Cells you want to change the position.
   • Click on the Format menu.
   • Click Cells.
   • Format Cell Dialog Box appear.
   • Click on Border tab, different border options are appeared.
   • Click on Alignment tab, different options are appeared.
   • Select Left or Right or Center from alignment text box. 
   • Select Top or Bottom or Center or Justify from vertical text box. 
   • Select other option if you want.
   • Press OK button.
   
   
4. APPLY AN AUTOFORMAT TO A RANGE
   This procedure is used to convert the selected cell data into preset table format.
   
   • Select the range, you want to format.
   • On the Format menu, click AutoFormat.
   • Click the Format you want.
    
5. CATEGORIES OR A NUMBER
   There are three categories of a number are General, Number and Currency. General format cells have no specific number format, Number format is used for general monetary values. To convert format of selected range into any category in to any category follow the following steps.
   
   • Select a cell range of cell you want to format.
   • Click on Format menu.
   • Click on Cells.
   • Format Cell Dialog Box appear.
   • Select General or Number or Currency from category list box.
   • Different format number appear, select any format from them.
   • Enter number of decimal you want in decimal places box.
   • Press OK button.
    
6. ABOUT CELL AND RANGE REFERENCE
   A reference identifies a cell or a range of cells on a worksheet and tells Microsoft Excel where to look for the values or data you want to use in a formula. With references, you can use data contained in different parts of a worksheet in one formula or use the value from one cell in several formulas. You can also refer to cells on other sheets in the same workbook, to other workbooks, and to data in other programms. References to the cells in other workbooks are called remote references.
   The A1 vs. the R1C1 reference style;
   The A1 Reference Style
   By default, Excel uses the A1 reference style, which refers to columns with letters (A through IV, for a total of 256 column) and refers to rows with numbers (1 to 65536). These letters and numbers are called row and column headings. To refer to a cell, enter the column letter followed by the row number. For example, D50 refers to the cell at the intersection of column D and row 50. To refer to a range of cells, enter the reference for the cell in the upper-left corner of the range, a colon (;), and then reference to the cell in the lower-right corner of the range. The following are examples of of references.
   =sum(A1:A10) A1:A10 is range cell reference that is from A1 to A10.
7. ALIGNMENT
   This procedure is used to align data at the top, center, bottom, left or right of a cell. Follow the following steps:
   
   • Select cells you want to change Alignment.
   • On the Format menu, click Cell, and then click the Alignment tab, select left or right etc from Horizontal box.
   • In the Vertical box, click top, bottom, center or justify.
8. DATE AND TIME 
• DATE:The procedure is used to select style for date and time. Follow the following steps.

• Select a cell or range of cells to change date of it. 
• Click on the Format menu.
• Click Cells.
• Format Cell Dialog Box appear.
• Select Date from category list box.
• Click  Ok button. 
• TIME 
  This procedure to select style for time. Follow the following steps.
• Select a cell or range of cells to change Time of it.
• Click on the Format menu.
• Click Cells.
• Format Cell Dialog Box appear.
• Select Time from category list box.
• Select Time Style from type list box.
• Click Ok button

WORKING WITH CHART 

1. CREATE CHART
   The procedure is used to create chart. Follow the following steps.
• Select cells that contain the data that you want to appear in the chart. If you want the column and row labels to appear in the chart, include the cells that contain them in the selection.
• Click Chart Wizard or click the Chart in the Insert menu.
• Select chart type.
• Follow the instructions in the Chart Wizard.
• At the last click on Finish button.
2. EDITING CHART
   CHANGE CHART LABELS, TITLES AND OTHER TEXT
   Most chart text - such as category axis label, data series names, legend text, and data labels - is linked to the cells on the worksheet used to create the chart. If you edit the text of these items on the chart, they are no longer linked to the worksheet cells. To change the text of these items and maintain links to worksheet cells, edit the text on the worksheet.
   Change Category Axis Lables
   • To change category axis labels on the worksheet, click the cell that contains the label name you want to change, type the new name, and than press ENTER.
   • To change category axix labels on the chart, click the chart, and then click Source Data on the Chart menu. In the Category Axis Labels box on the Series tab, specify the worksheet range you want to use as category axix labels. You can also type the labels you want to use, separated by commas.
   
   Change Data Labels
   
   • to change data labels on the worksheet, click the cell that contains the information you want to change, type new text or value, and the press ENTER.
   • To change data labels on the chart, click once on the data label you want to change to select the data labels for the entire series, and then click again to select the individual data label. Type the new text or value, and then press ENTER.
     If you change the data label text on the chart, it is no longer linked to worksheet cell.
   
   Edit Chart and Axis Titles
   
   • Click the title you want to change.
   • Type the new text you want.
   • Press ENTER.

LEGEND

Legend is box that identifies the pattern or color that are assigned to the data series or categories in a chart.

Add A Legend To A Chart

1. Click the chart to which you want to add a Legend.
2. On the Chart menu, click Chart Options, and then click the Legend tab.
3. Select the Show Legend check box.
4. Under Placement,click the option you want.

Change Colour In A Surface Chart 

To format the colors of the levels in a surface chart, you must format the Legend Key.

1. If the chart does not have a Legend, add one.
2. Click the Legend once to select it, and then click the Legend Key that represents the surface level you want to change.
3. On the Format menu, click Selected Legend Key, and then click the Patterns tab.
4. Click the color you want, and then repeat steps 2 and 3 for each level you want to format.

Font and Rotated Text 

You can format text in a chart as you would any other text. Click the text or item, then change the font, size, and colour by clicking the buttons on the Formatting tool bar.

Change the Font & Size of Text in a Chart
If a chart Title or Text Box is linked to a worksheet cell, you can change the formatting of all characters in the tiltle or text box at the same time, but you cannot change individual characters.

1. Click the chart text, or select the individual characters you want to format.
   To change the formatting for all of the text in the chart at the same time, click the blank area between the border of the chart and the Plot Area to select the Chart Area.
2. On the Formatting toolbar, click a button for the format which you want.

Rotate Text in a Chart Title or Along an Axis
You can rotate, or "Angle", text in a chart Title or along an Axis. You cannot rotate Legend text.

1. Click the Axis or the Title you want to format.
2. If you click an Axis, click Selected Axis on the Format menu.
   if you clicked an Axis Title, click Selected Chart Title on the Format menu.
3. Click the Alignment tab.
   If you don't see the Alignment tab, click Cancel, click outside of the text you want to format, and then repeat steps 1-3.
4. To rotate text, under Orientation, click a degree point, or drag the indicator to the posittion you want.

PRINTING  

1. SELECTING PRINTER
   Set the Printer
   This procedure is used to select printer name for printing.
   Follow the following steps:
• Click the worksheet.
• On the File menu, click Print.
• Under Printer, click arrow to next name list.
• Printer will be appear, select any one.
• Click OK button.
2. PRINT SELECTED AREAS
   Print Selected Area in the Active Sheet
   This procedure is used to print selected area of active. If you select a range of cells to print follow the following steps:
   
   • On the File menu, click Print.
   • Print dialog box appear.
   • Click Select Radio button.
   Page Selection
   Set the Size of the Paper
   
   1. On the File menu, click Page Setup, and then click the Page tab.
   2. In the Paper size box, click the size of paper you want, & press OK button.