Excel

Microsoft Excel MS-Excel is a very powerful and easy to use spreadsheet package: perhaps the most commonly used these days. It is a member application included in the MS- Office suite. It works very well with numbers and their complex calculations. Excel helps prepare the data in an organized, orderly and meaningful fashion. Excel finds its major application in: • Creating Reports • Scientific and Statistical Analysis. • Preparing Balance Sheet. • Profit and Loss Accounts • Tax Statements. • Analytical reports including forecasting and regression analysis. It works well for both simple as well as complex numbers. Good looking charts can be created which depict data in clear and meaningful fashion. It is also useful in establishing relations between different types of data. Data Entry in MS- Excel The smallest unit of addressable data container in Excel is a Cell. A cell is a room created by the intersection of a row and column. Each row has a unique number in a worksheet -1,2,3,……65536; each column has a unique column name in a worksheet- A,B,C…….Z,AA,AB…. And therefore each cell has a unique address in a worksheet like D13. A worksheet is a rectangular collection of many cells. A single Excel worksheet contains as many as 65536 rows, 256 Columns and thus 16777216 cells. Each worksheet in a workbook has a unique name- sheet1, sheet2, sheet3 etc. A workbook is a collection of one or more worksheets. It is a workbook that is saved as a file on a secondary storage. Each workbook is stored in a file having a unique name – Sales.xls. xls is the default extension of the Excel workbook files. Each Excel workbook has about maximum 255 worksheets. To start Excel: 1. Click on the Start Button at Windows Taskbar. 2. Select Program Option. 3. Click at the Microsoft Excel Option. [pic] Excel Window has the following parts: 1. Title Bar: Title bar shows the name of the application and the name of workbook and situated in the top of the window application. 2. Menu Bar: Menu bar contains the various commands under the various topics to perform some special tasks. Menu bar is located under the title bar. 3. Tool Bar: Tool bar is collection of shortcut buttons to the menu options represented by icons. 4. Status Bar: Display current status of the application. 5. Formula Box: Displays and allows editing of formula in the current cell. 6. Name Box: Displays the row number and column name of the active cell. 7. Help Box: Allows the user to type a topic and searched for help. 8. Scroll Box: Allow moving the worksheet in and out of the window view. 9. Worksheet Tabs: Allow making the desired worksheet active. 10. Task Pane: Displays various commands available currently. If the user ends the Excel session without entering anything in the sheets. Excel ends without prompting any message. However, when you enter any data in the worksheet and then end the session, Excel prompts the user with an option for saving changes. Here the user can provide the name of the workbook file. If the data entered in the Excel file is to be modified, it is done by double clicking the cell and then type the value again or press F2 function key on the keyboard. Working with Menus Commands that let the user perform desired actions are grouped into logical groups called menus. The various menus in MS Excel are : [pic] These menus provide groups of related commands. Thus File menu contains all the commands related to file or workbook manipulation. These menus can be opened by clicking at them or by pressing ALT+(underlined letter on the Menu name). Thus ALT+F will open File menu while ALT+O will open Format Menu. File Menu The excel file has the following options : [pic] File-> New Click on the file menu. Then click on the New Option or press CTRL+N through the keyboard. A blank workbook opens in the Excel Window. • File-> Open To open an existing document select the open option from the File menu or press CTRL+O. The open dialog box will popup on the screen as shown below: [pic] Specify the correct folder where the file is saved. Click on the open Button on the right side or press Enter to open the file. • File-> Close To close the current document click at the close command in the file menu. Note that only the current document or folder would be closed as MS word works in a Multi Document Interface. • File-> save and save as For saving the current document click at the Save command in the File Menu or press CTRL+S. If the file is being saved for the first time then the Save as window would popup on the screen as shown below: [pic] Choose the appropriate folder from Save in combo box where the file is to be saved. Write the File name in the File name Text box and press Enter.Save and Save as command are both used to save the data in the document. However, the difference between then is that the Save as command allows the user to save the file with a different name and format also. The Save option will save the document by the same name and format as it was saved for the very first time. • File-> Print Click at the Print command for taking out the print out of the document. The Print Dialog Box appears: [pic] We can set various options before taking the printout: 1. From the name combo box in the Printer frame we can select the printer which is to be used to take the Printout. 2. Page Range is used to mention the pages which are to be printed out. If the Print out of the entire document is required then All option is selected else the Page numbers are specified. 3. Copies option is used to mention the number of copies of the document which are to be printed. 4. In the page per sheet in the Zoom frame we can specify the number of pages in the document that are to be printed on each sheet of paper. 5. From Scale to paper size option we can select the paper size on which the print out is to be taken out. 6. The collate check box can print the copies of the document in proper binding order. Data Entry and Editing At any time one cell is always highlighted which is called the currently active cell. The address of this currently active cell is shown in the ‘Name’ Box which lies in the upper left corner of the screen, exactly above the column letter A. The user can enter variety of values in a cell such as: 1. Numeric value: To enter the number just make the cell active and enter the value. 2. Text: To enter Text Value, make the cell active and type the text. Even if the text goes beyond the confines of the cell, it remains in that cell 3. Formula: A formula may be entered in the cell in a similar manner. A formula is an expression that evaluates to some value. Excel calculates the value of the formula and inserts the same in the cell. 4. Comment: The user can enter a comment in a cell to remind himself some important facts about the cell. View Menu View Menu commands allow users to choose what components of the Excel Window should be visible and what should not be. View-> Toolbars Toolbars are a quicker way to issue a command. Excel comes with many inbuilt toolbars. But by default it displays only two toolbars namely- Standard Toolbar and the Formatting toolbar. To display or to hide a toolbar: 1. Click on the View Menu. 2. Select on the Toolbar Option. 3. Toolbars that are currently displayed have checkmarks in front of them and those which are hidden don’t have any check marks. 4. Checkmark the Toolbar which is to be used. [pic] The other options available with the View menu are :Zoom, Full Screen and Normal. Insert Menu Excel lets the user enter a variety of objects in a worksheet. All the required commands are grouped in the Insert Menu. Insert-> Inserting a Cell MS Excel allows the user to insert a single cell or a number of cells in the worksheet. Insertion of cells is done as follows: 1. Click on the cell where the insertion of the new cell is to be done. 2. From the Insert Menu choose the option cell. 3. Click shift cells right or left as per the requirement. 4. Click the Ok Button. [pic] Insert-> Formula A formula is an expression that performs operations on worksheet data. Formulae can perform mathematical operations such as addition and multiplication, or they can compare worksheet values or join text. Formulae can refer to other cells on the same worksheet or cells on sheets in other workbooks. To Enter the formula in a cell, begin with the = sign. The Equal sign tells the Excel that the succeeding characters constitute a formula. Following the Equal sign are the elements to be calculated which are separated by calculation operators. MS Excel calculates the formula from left to right, according to a specific order for each operator in the formula which can be changed by use of parenthesis. The following example adds 25 to the value in cell B4 and then divides the result by the sum of values in cells D5, E5 and F5. =(B4+25)/SUM(D5:F5) where D5:F5 is the range reference. The parenthesis around the first part forces Excel to calculate B4+25 first and then divide the result by the sum of the values in cells D5,E5 and F5. Insert-> Order of Evaluation of Operators Excel evaluates a formula in a particular order predetermined by the precedence number of the operators being used and the parenthesis placed in the formula. |Precedence |Operator |Description | |1 |: |Range of Cells | |2 |space |Insertion of Cells | |3 |, |Union of Cells | |4 |- |Negation | |5 |% |Percentage | |6 |^ |Exponential | |7 |* |Multiplication | |7 |/ |Division | |8 |+ |Addition | |8 |- |Subtraction | |9 |& |Concatenation | |10 |= |Equal to | |10 |< |Lesser than | |10 |> |Greater than | |10 |= |Greater than or equal to | |10 | |Not Equal to | The cell that contains the formula is known as a dependent cell as its value depends on the values in other cells. Insert-> Functions A Function is a predefined formula that takes Zero or more input values and produces a result. The arguments can be either constant quantities or reference to cells or ranges, as long as data types of all the participating values are consistent. The general structure of a function is : Function_Name (commaseparated Argumnet List) Eg: Sum(A1, B1:B4,30) The result of the above function would be the sum of values stored in the cells A1, B1, B2, B3, B4 and 30. MS Excel includes a wide variety of in built functions that the user can use in the spreadsheet. For convenience, they have been grouped into logical categories such as: • Mathematical: SUM, EXP, POWER, LOG etc. • Statistical: AVERAGE, COVAR, FISHER etc. • Logical: AND, OR, NOT etc. • Date and Time: DATE, DAY, HOUR etc. • Text: LEFT, LEN, MID etc. Inserting a Function Inserting a Function in a cell is very easy as Excel assists the User while using in built functions. Broadly, there are two ways of inserting a function: 1. Manual 2. Using Function Wizard. [pic] To calculate the total salary of the above five people use of SUM function is made. Click on the cell in which the user wants to insert the function. Enter the formula =SUM(D3:D7) in the Cell D9. Now press Enter. The total of all the above values would appear in the specified cell. Now if the salary of any one person is modified then the total would be updated automatically updated. Similarly, all the other functions can be performed easily in MS Excel. Functions can also be inserted using the Function Wizard. This is particularly convenient when the user has no idea related to the function. To invoke function Wizard select the cell in which the user wants to put the formula. The click at the function command in Insert Menu. [pic] The Dialog Box allows the user to search the details of the function to be used by typing the function name in the Search for a Function Box and then by pressing Go Button. If the user does not remember Function’s name or the type of function to be used then select a category in the select a Category Box. All the functions of the selected category are displayed in the select a function box. BY selecting the desired function the required action can be performed. Insert-> Inserting Charts Pictures are more understandable than words. Charts are visually appealing and make it easy for users to see comparisons, patterns and ant kind of trend in the data. Further, they are quick as compared to studying the detailed columns of the worksheet. Charts are created in MS Excel using the chart wizard. It sits in the toolbar. [pic] The process of making a chart is as follows: 1. Select the range of data on which the chart is to be prepared. 2. Select the option chart from the Insert Menu, then the following dialog box appears. [pic] 3. Select the type of chart required. Click on press and hold to view sample button to preview the chart. For instance let us prepare a Pie chart for the following data. [pic] 4. Select the graph type and click on the Next button. [pic] [pic] 5. Click Finish to insert the chart into the Worksheet. Similarly, by providing the data to the Chart Wizard all other type of charts can also be prepared. [pic] Conclusion