The Use Of Microsoft Excel Computer Science Essay

Microsoft Excel, popularly called as ‘Excel’ is a versatile software application and is a part of the Microsoft Office Suite applications. It is a full-featured spreadsheet program that allows the user to perform a specific work related to decision making in the areas of business and economic applications. The specific work may be related to organizing statistical data, performing statistical and mathematical calculations, drawing inferences related to statistical applications, drawing graphs and charts based on the data availability, and also help in developing professional looking of reports. For better understanding, excel can be divided in three major heads:

Parts

Function

Worksheets

It allows the user to enter, calculate, manipulate and analyze data of both numerical and textual

Charts and graphs

Charts and Graphs are the pictorial representation of data series. Excel helps the users to represent the data to draw two-dimensional and three-dimensional column charts, pie-charts and many other types of charts and graphs.

Databases

The third important function of Excel is of managing data. For instance, once the user entered a set of data in the excel worksheet, it is having the facility to sort the data, if required, can search specific data, and can select that specific data that is needed by the user.

STARTING EXCEL?

There are a number of ways to start in Excel. The one possibility is to click on the ‘START’ button, then select ‘PROGRAMS’ from the start menu and finally, click on ‘Microsoft Excel’. The second possibility is to create a shortcut of the MicrosoftExcel icon on the desktop. When excel needs to open, double click on the icon directly. The third possibility is that if the user is already developed a spreadsheet file by assigning a name to that file, then the procedure is to double click on that file. For example, there is an excel file which is saved by a user in the file name ‘namita.xls’. Double clicking on the icon of this file will directly open this file. All the above three operations will display white grid in the screen with rows of menus and icons at the top of the screen.

EXPLORING THE WINDOWS:

When Excel is opened, mostly two types of windows appears on the screen namely the application window and the document window. The identification of the application windows is one that contains running program and they have menu bars. Where as, the document window appears inside the application window without the menu bars.

FIGURE-FA1

Following are the brief descriptions on various uses of different bars.

a. Title bar:

It shows the name of the application, document, group, directory, or file. If more than one window is open at a time, the title bar for the active window (that window where the user is working at present) has a color or intensity different from the other title bars.

b. Menu bar:

This bar contains the available menus from which the user can choose command based on his/her need.

c. Tool bar:

The tool bar(s) provides the user with a quick method of working with various parts of the worksheet. Tool bars can be displayed on the screen based on the use of the user. They can be customized and also multiple tool bars can be displayed at the same time.

d. Scroll bar:

The scroll bar(s) enable the user while moving the spreadsheet when the entire spreadsheet is not coming within the display area in the computer screen. Here the user has to click the scroll arrows with the mouse to move through the spreadsheet or to see one line at a time.

e. Window border:

The window border is the outside edge of a window. The user can change the window size by lengthening or shortening the border on each side of the window.

f. Commands:

Commands are accessed through the menu bar of the file. The user has to select the menu item and then the commands associated with that menu will be displayed. The user has to click on that command which he/she required.

g. Formula bar:

It displays any formulas, address, or variables used to get the information in the cell.

The pictorial description of an Excel workbook is derived in Figure-FA2 below.

FIGURE-FA2: AN EXCEL WORKBOOK

WORKING IN THE SPREADSHEET:

A spreadsheet is a tabular form of values arranged in rows and columns. When an Excel file is clicked, a workbook is opened. The normal file type in Excel is referred as a workbook. Hence, spreadsheets are created on the worksheets. The first blank workbook displayed in the monitor of the computer is called Book1. Each workbook contains sheets which are called as worksheets when they contain in spreadsheet. But when include a graph is called as chartsheet. Workbooks are rectangular grid containing columns (vertical marked A, B, C….Z, AA, AB,AC,…AZ..) and rows (horizontal marked numeric values 1,2,3,4,….). Thus, a letter above each column identifies each column and a number on the left side of the grid identifies each row. Each worksheet in a workbook has 256 columns and 65,536 rows.

Cells are the building blocks of plants and animals …and spreadsheets too! Just like living cells do inside the living organisms, spreadsheet cells similarly can perform many functions. The cell is the basic unit of the worksheet and the data is entered in a cell. Each cell can be identified by a unique cell address otherwise called as ‘cell references’, which is the interaction of a column and a row. In order to identify a cell the column letter is specified first followed by the row number. For example, in the above figure-FA2 the highlighted single cell reference is B4 (as B stands for column and 4 stands for row). Similarly, multiple cells can also be identified by the user. In such a case, the user has to identify an active cell first, then hold the left mouse button, then drag to down and have to move the mouse right based on the desired numbers of cells. This facilitates all cells within that range will be highlighted.

Data has to be entered in the active cell. The active cell in the workbook can be identified by a heavy border surrounding the cell. In addition, the active cell is listed in the ‘reference area’ immediately above column A and row 1 referred as A1.

NATURE OF DATA:

Three types of data are generally entered in a workbook. They are:

Nature of data

Description

Text

To identify a set of work, some identification is needed. These identifications are expressed in qualitative descriptions. For example, titles of work like ‘A regression analysis’, ‘Household budget analysis’, etc. and for identifying the columns it also requires the textual variables like sales, productions, income, expenditure, profit, interest, etc.

Numbers

Numbers is the second items that can be entered in a workbook. Numbers consists of the digits zero through nine (i.e., 0, 1, 2, 3, …, 9) and some special characteristics like +, -, ( ), /, . , $, %, #, etc.

Formulas

One of the greatest advantage of Excel application is the usefulness of formulas. A formula is a sequence of the values and the cells reference in a cell that produces a new value by using the existing values which is available with the user. Formula can be entered in uppercase or lowercase, and space can be added between the arithmetic operations to make the formulas easier to read. The detailed use with formulas is carried out in below derived sections.

CREATING A WORKSHEET:

A sample worksheet is created in Figure-FA3 below for the better understanding of the readers. First of all the user has to is to enter correct values in the required cells. Once the required information are entered in the cell the user can make the whole worksheet attractive by using some formatting options like bold text, different alignment and adding multiple colors to various texts to make the matter easily distinguishable and so on. Few easy steps of entering data are derived below.

Click with mouse on the desired cell (in our example it is A2)

Press the ENTER key from the key board to switch to next cell (or use Arrow key) or click on the next cell through mouse

Type the column title to identify your variable (like A3 for the data of GDP, in B3 data on Agricultural sector, C3 Industrial sector etc.)

By using step-2, enter the entire data in columns A, B, C, and D

Then click in B2 to enter the next title of the data and enter data in each cell till it completed.

Enter the data in other columns based on the availability of data.

After entering the data, the entire spreadsheet will looks like as.

FIGURE-FA3: A Sample Worksheet

If the user has noticed any mistake while entering a data in a cell, then by using the back space key that value can be corrected. On the other hand, in case of mistakes in any cell where the data is already entered, make that cell as active cell. When the user clicks on a cell, the value entered in that cell is displayed in the formula bar of the worksheet. By using the backspace at the formula bar the mistake needs to be corrected.

HOW FORMULAS WORK:

The basic concern of this book is to narrate the detailed use of calculations by using the formulas and its execution procedure. Each formula has to be entered with the formula operator which is the ‘equal sign’ i.e., =. When an equation sign is entered in a cell, Excel understands that whatever will be followed will be either a numeric value or may be an address of the cells which must contains a numeric value. For example, =C6 implies put in the value that is in the cell with the address of column C and row 6. For the better understanding of the readers, the process of working with formulas is discussed in three different heads below:

Simple mathematical calculations

Data analysis and

Drawing charts

1. Simple Mathematical calculations:

Data are the raw materials for spreadsheets. They generally are of constant values or simple values. Mathematical calculation by using the available data can be done by using formula (s). Hence, formulas are the command that instructs Excel to carry out a calculation. There are four independent basic mathematical operations and group operations of these four types can be easily carried out in Excel. The operators used for mathematical calculations are:

Operator

Pronunciation

Task

Operator

Pronunciation

Task

‘+’

Plus sign

Addition

‘*’

Asterisk

Multiplication

‘-‘

Minus sign

Subtraction

‘/’

Forward slash

Division

‘%’

Percent

Per cent

‘^’

Hat

Exponentiation

‘&’

Absciant and

Text Joining

Addition: Any two or more values entered in separate cells can be added by using formulas. The first step is to start with =SUM(beginning cell address:ending cell address) and then press the ENTER key. In figure-FA4 below, a user wants to sum two values entered in cell A2 and cell B2 and wants to get the value at column F3. The formula has to be entered as written in the figure in F3 column and by pressing the ENTER key the value will be displayed as 28.66.

In the second process the user can add different values entered in different cells by using the ‘+’ sign. For example, =first cell address+second cell address+…, then press ENTER key. Let suppose that the user wants to add only three values viz., A2, B3 and A4 and wants the result to be displayed in cell G3. The formula will be as displayed in the G3 column and after pressing ENTER the value will be displayed in that cell as 42.47.

Excel makes it easy to quickly add a column or row of numbers. To illustrate the AutoSum feature, consider the above Figure-FA4. Let that the user wants to sum value entered in B column. Click on cell B6 which is the active cell, then click on the AutoSum botton displayed in the tool bar with a sum symbol like ∑ and click on ENTER key for the value. In such a case, the sum function automatically selects the values of the entire column. Here the user is not required to enter the address of all the cells manually. The AutoSum value of the data entered in column B as displayed in B6 cell is 60.21.

ii. Subtraction: Two or more values can be subtracted by using the ‘-‘ operator. The formula can be used as = first cell address-second cell address then press ENTER key. For example, one wants to subtract two values entered in cell B3 to A4 by using the values of above Figure-FA4 and wants to get the value in F5 cell. The formula will be as displayed in the F5 column and after pressing ENTER the value will be displayed in that cell as 5.26.

FIGURE-FA4: VARIOUS MATHEMATICAL CALCULATIONS

Multiplication: two or more values can be multiplied by using the multiply operator ‘*’. The formula can be used as =first cell address*second cell address*… and then press ENTER key. Let for example, one wants to multiply three values as entered in cell A1, B2 and A4 by using the values of above derived Figure-FA4 and wants to display the multiplication value in cell G4. The formula will be as displayed in the G4 column and after pressing ENTER the value will be displayed in G4 cell as 2533.073.

Division: For the use of division in Excel, the ‘/’ operator is used. The formula will be =first cell address/second cell address and then press ENTER key. For example, one wants to divide two values entered in cell B4 to A2 by using the values of above Figure-FA4 and wants to get the value in G5 cell. The formula will be as displayed in the G5 column and after pressing ENTER the value will be displayed in that cell as 1.378.

Group expressions: In many calculations the situation demands for a grouped calculation. In such calculations parentheses (or brackets) are used to combine two independent functions into a group. The rule is that any part of the formulas expressed within the brackets is done first and the next operation is carried out then after. For example, the expression 8+3/2 will be 9.5 where as (8+3)/2 will be 5.5. Similarly, the value of 7+3/2 is 8.5 (not 5) where as (7+3)/2 is 5.

Group calculations in Excel can be done as: =(first cell address then the operator(let it be +)second cell address, so on)the next operator (let it be *) by the constant value 2 (or if the value is entered in any cell then the cell address of that cell). By using the above Figure-FA4, some other possibilities are illustrated for the better understanding of the reader.

Table-TA1: MATHEMATICAL FORMULA MODELS

By using

Formula in Excel

Value

Multiplication and Addition

=(E2+E4)*2 then ENTER key

=(E2+E4)*C3 then ENTER key

52

330.2

Multiplication and subtraction

=(B3-E3)*5 then ENTER key

16.05

Multiplication and Division

=(E4*A4)/3 then ENTER key

61.65

Addition and subtraction

=(A3+A4+E3)-B4 then ENTER key

20.64

There are many other such possibilities of calculating values by using the group function. To illustrate the concept more clearly, an attempt has been made to calculate the ‘PAY ROLL’ in Excel of few employees in the Box-A1 derived below.

Box-A1: CALCULATION OF PAY ROLL OF M/S. DIKSHYA INDUSTRIES PVT LTD

Mr. J.P.Voga, since his joining as the Managing Director of M/S. Dikshya Industries last month had passed various resolutions in his industry. One such resolution is that the salary of its 385 employees should transform to their accounts positively on the 1st day of each month. Mr. P.K. Jha, the Head, Accounts Department of that industry, is a retired government auditor who is well habituated with manual calculations. But after joining this industry, he is learning the use of Excel since last few months casually as he managed himself with this industry with his earlier expertise. But, Mr. Voga’s order pressurized him a lot and he at the end finalized to develop the pay roll of each departments of the industry in Excel. For this he approached Mr. Vinay Jalota, his sub-ordinate at office who is at the post of Assistant Finance Officer at his industry. They decided to formulate separate pay roll for each department separately. Since, Mr. Jha is formulating the pay roll for the first time, they decided to start with the IT-Department of the company which is having 16 employees starting from the top management to the bottom employee. The steps followed are as follows:

Step-1: In the first place it has been decided to divide the pay calculation in two separate heads like Basic and Allowances and the second phase is the deductions. The basic and allowances section consists all the gains to the employees i.e., credit amounts and the deduction section consists of debit amounts or looses to employ.

Step-2: From the model pay roll attached in Figure-FA5 one can find that column A and Column B are the serial numbers of the employees and the name of the employees along with designation and basic pay respectively.

Step-3: Column C is the number of days in that month that the employee had worked. The entry 31 in this column indicates that this month is having 31 days (this calculation is on May 2010). One can see that against each employees 31 is entered which implies all have worked in this month without any without pay.

Step-4: Then in column D salary is calculated in respect of the basic of each employee separately. The formula =ROUND(VALUE(21400/B3*C6),0) is entered. In this formula, ROUND(VALUE is entered to round the fractions to nearest 0. The entry 21400 is the basic of the employee and it is first divided by B3 column i.e., to get per day salary of that month and then multiplies by C6 which is the number of days the employee worked. That is out of the 31 days in May how many days that particular employee worked. Once this formula is entered then copy the formula and paste it in the D7 column to get the salary of the next employee. But the basic of each employee differs. Hence, new basic needs to be edited in the copied formula in the formula bar. In this way in column D salary for each employee has been calculated.

Step-5: Column E is the addition of the 50per cent of employees. The formula entered as =ROUND(VALUE(D6/50%),0). As one can see D6 column estimates the basic salary based on the number of days that employee worked on that month. A 50% of this amount has been calculated. Then copy this formula and paste it against each employee in column E. Since this formula is fixed for all, hence, there will be no change in the formula. But this formula doesn’t fit for that employee who is in consolidated pay salary.

Step-6: Column F is the added value of column D and column E. This amount will be the basic salary amount of the employees. The formula entered for calculation is =ROUND(VALUE(D6+E6),0). This formula is remained unchanged against all employees who are in the regular pay scale, of course except those who are in consolidated pay scale.

Step-7: Based on the total amount calculated in column F, 64% is to be added as DA allowances. The formula is =ROUND(VALUE(F6*64%),0). This is applicable for all the employees except consolidated pay packagers.

Step-8: Special amount per month has been fixed by the employer based on the basic of each employee. This amount as applicable has been added against each employees. The formula is = ROUND(VALUE(4600/B3*C6),0). This formula implies that per month special amount is Rs. 4600. this amount is first divided by total days in that month (B3) to get per day amount and then multiplied by numbers of days the employee worked in that month (C6).

Step-9: HRA is at the 5% of the total salary calculated at column F. Hence, the formula is = ROUND(VALUE(F6*5%),0). But those employees who are residing in the quarters provided by the Industry are not entitled to get this amount. Hence, these columns for some employees are left vacant without any calculation.

Step-10: Similarly Transportation, Traveling and other allowances, Medical, extra special allowances, Personal Pay and Telephone allowance as per applicable has been separately calculated by using the same logic i.e., at first divided by numbers of days in that month and then multiplies by numbers of days the employee worked. For example, the formula for Transportation is = ROUND(VALUE(400/B3*C6),0) i.e., Rs. 400 is the medical allowances. Similarly, Traveling is calculated by the formula = ROUND(VALUE(6000/B3*C6),0) and so on.

Step-11: The Gross salary of each employee will be the addition of total amount of the above allowances. In column P row 6 i.e., in P6 to calculate the gross salary of the first employee the formula is = (F6+G6+H6+I6+J6+K6+L6+M6+N6+O6). This formula then copied and past in against all the employees in P column. The format in Excel sheet is shown in figure-FA5 below.

Step-12: Then deductions are to be deducted from the gross salary of each employee. The items include TDS, EPF, License Fees, Maintenance, Electricity, and Advance. Among these items, the items like TDS and EPF are applicable for all the employees but the amount differs based on the gross salary. There fore, these amounts as per applicability have been simply entered in the respective column of each employee. License fee is applicable to those who are the residents of industry provided quarters. The formula is = ROUND(VALUE(F7+G7*10%),0). This implies that, license fee is the 10% of total column plus DA i.e., F7+G7. The readers should not be confused with the entry of row 7 in the formula because employee entered in the model pay roll is not a resident of industry provided quarter. Similarly, the residents of industry quarters have to pay Maintenance and Electricity. Electricity bill is not fixed for each employee rather depends on their consumption, thus fluctuates from employ to employ. Hence, are entered directly against the employee. Where as maintenance is the 10% of the license fee calculated in column S. The formula is = ROUND(VALUE(S7*10%),0).

FIGURE-FA5: PAY ROLL FORMAT

Step-13: The net payable or net salary of each employee is calculated as the total gross salary (column P) minus the deductions. The formula for the first employee is =P6-Q6-R6-S6-T6-U6-V6. Simply copy the formula and pasted it against each employee. The deduction portion of the model pay roll is shown in the Figure-FA6 below.

Step-14:.Then by setting the pay roll worksheet by using the page setup options, the page can be printed by using the print option.

FIGURE-FA6: PAY ROLL FORMAT (DECUCTIONS)

This process when over surprised Mr. Jha. He could not believe him self that a person who is not touched the key board since last more than 30 years successfully developing a complete format of pay roll in Excel. This task of Mr. Jha increased his self confidence.

Data analysis:

Excel can perform a very large number of functions, which are formulas that perform a specific calculation beyond simple mathematical calculations. When the FUNCTION option in the INSERT menu is selected, it will display a large number menu of functions. This task of the Excel is more useful by the researchers for the simple statistical and econometric analysis of data. Excel’s FUNCTION option is very simple and hence, user friendly than other available statistical packages like SPSS, LIMDEP, STATA, STASTICA, GRITL, E-VIEWS, etc. But one think that the users should keep in mind that Excel’s data analysis wizard is not helpful for advanced data analysis purposes. Rather it is best suit for the beginners, particularly for the students and entry level researchers. Following are the steps of using the FUNCTION button in Excel worksheet for the purpose of drawing some statistical inferences.

The first step is to create a data base in the worksheet containing the data series to be calculated.

Then, click on INSERT button on menu bar and choose FUNCTION button. A small Excel window will appear showing the dialogue box. The user has to select the type of analysis it requires. The window is sheen as displayed in Fgure-FA7 below. The best option is to go for ‘All’ type which will display all the options available under this function.

FIGURE-FA7: INSERT FUNCTION USE

Then specify the function (i.e., whether analysis is a standard deviation or correlation analysis, and so on.)

Input the required cell address as per the need of the function and click OK.

The final result will be displayed by the Excel application.

The above steps can be well understood with a step wise calculation of CORRELATION between two variables as derived in Box-A2 below. Readers who are interested on the subject of correlation are advised to refer chapter-14 for a detailed analysis.

Box-A2: Indian Textile Industry: Domestic Purchase is the Key

The Indian textile industry faces many challenges in its efforts to become a major player in the global arena. Though it has many positive factors such as a multi-fibre raw material base, low cost labour base and big domestic market, it has been severely affected by insufficient power supply, steep increase in minimum support price for cotton and announcement of minimum wages on one side and stiff competition from other textile producing countries like China, Pakistan and Bangladesh. However, among the variables that positively influence the growth of the industry, the share of domestic purchase plays a very crucial role. For this we are having around eight years’ data on the total production of Indian textile sector and total domestic purchase by the Indian households. The detailed data set is shown in the Table-TA2 below.

TABLE-TA2: TRENDS IN DOMESTIC PURCHASE

(Quantity measured in million meters)

Year

Total Production

Domestic Purchase

2000-01

40333

17969

2001-02

41390

19154

2002-03

41311

19860

2003-04

41721

20957

2004-05

44685

21784

2005-06

48808

22859

2006-07

52665

24030

2008-09

55023

25493

Source: Compiled from industry data base.

In order to know whether there really exists strong relationship between the two variable i.e., between the total production of textile and domestic purchase, the correlation between the two variables are calculated with the help of Excel software and the steps followed in the process of calculation are outlined below:

Step-1: The above data set is entered in the Excel spreadsheet and the file is saved as ‘Correlation Analysis.xls’.

Step-2: The FUNCTION button from INSERT menu has been selected.

Step-3: The window is now asking to specify the required function. In the ‘Data type’ option ‘All’ has been selected. Then ‘CORREL’ option for correlation analysis is to be selected. The window appears as:

FIGURE-FA8: INSERT FUNCTION WITH CORRELATION OPTION

Step-4: When OK button is clicked, the next window will flash by asking for specifying the data range. In Excel’s language it is referred as Array1 and Array 2. Array 1 asks for first data series and Array 2 asks for second data series. In our above data set, year is only for the purpose of interpretation. Hence, while calculating the correlation value the column containing the Year data is not considered. After specifying the two data cell range, the window will looks like as

FIGURE-FA9: FUNCTION ARGUMENTS IN CORRELATION ANALYSIS

Step-5: By a click on OK button shows the value of correlation between the two variables which is measured by coefficient of correlation as 0.9559.

The simple interpretation of the value says that both the variables considered under study are related with each other by up to 95 per cent. In other wards, there are other 5 per cent of variables, except domestic purchase contributes to total production of textile in India. Hence, the policy makers are to be serious enough with this result and they should pave necessary step to strength the domestic market for further growth of textile industry.

Using Analysis tool pack

Beside the FUNCTION option, data can also be analyzed by using the ‘Analysis Toolpack’ and ‘Analysis toolpack-VBA’ options. In some computers, these packs are not loaded automatically. To load this option in the computer click on TOOLS in menu bar and click on ‘Add-ins’. A small window asking the addition will display in the screen. Tick on the two options i.e., on ‘Analysis Toolpack’ and ‘Analysis toolpack-VBA’ with mouse and then click OK. The window will be as:

FIGURE-FA10: INITIALIZATION OF ANALYSIS OF TOOL PACK

For using the analysis tool pack, in the first place enter data in the Excel sheet and save the sheet by assigning a name. Then click on TOOLS and select DATA ANALYSIS option. A small window will appear showing numbers of statistical options. Specify on the desired one based on the need through mouse. From the below derived Excel window one can see that the ‘regression’ option has been selected.

FIGURE-FA11: DATA ANALYSIS OPTION FOR REGRESSION

An OK click in the above process opens another window asking for specifying the cell address of the data series based on the desired statistical analysis. The user has to specify the cell address where as some functions need adjustments on basis of statistical properties and nature of the study. In order to explain the above steps more clear a Box-A3 has been developed below where by taking the sectoral growth rate, a multiple regression result has been calculated in Excel sheet.

Box-A3: Contributions of regional sources in growth acceleration

A nation is an aggregation of its regions. Thus, if we add the incomes of all states (regions), we should get the national income. This premise does not strictly hold in India given the problems in measurement and the data availability issues for more states and union territories. However, such problems are not considered serious in terms of magnitude and are, therefore, ignored in India [Dholakia, 2009].

In order to derive the contribution of states in the national growth acceleration the growth rate of the states during 1991-92 to 2003-04 for all the three sectors and the growth rate of the economy as a whole has been considered. The relevant concept of income is the gross state domestic product (GSDP) originating within the geographical boundary of the state and is measured at constant (1993-94) prices. The raw data is then converted into natural logarithms by taking ‘ln’ value to make the data smooth one. A multiple regression model has been developed by considering the lnGSDP as dependent variable (Y) and other three variables i.e., contribution of primary sector (referred as lnPRI), contribution of secondary sector (referred as lnSECO) and the contribution of Tertiary sector (referred as lnTER) are considered as the dependent variable.

Step-1: Enter the data in Excel sheet.

Step-2: Click on TOOLS and select DATA ANALYSIS option

Step-3: Select ‘Regression’ option and click on OK.

Step-4: The regression is now asking to specify the cell address of ‘Y’ value and the cell address of independent variables. As discussed earlier, type the cell address against each row or click through mouse on the right bar and drag the cells. In this present study confidence level is fixed at 95% confidence level (users can change based on their requirement). Then in ‘output option’ Excel is asking the place where the result is to be displayed. Select any one option out of the three available options. In this illustration, same workbook has been specified and, hence, the cell range is specified. The window after fixing all these options looks like as:

FIGURE-FA12: REGRESSION OPTIONS SETTING WINDOW

Step-5: Close OK and the output window will be displayed in the specified cell address. The complete window (data and output) after a slight editing will looks like as:

FIGURE-FA12: REGRESSION OUTPUT WINDOW

Note: The basic purpose of developing this Box is to make the readers familiar about the use of ‘Analysis of tool pack’ of Excel software. The readers who want to go in detail on regression model building and its result validity along with interpretation of the results are advised to refer Chapter-14 for details. The data on Ln value of average relative shares of all states in GSDP by broad sectors (Omitting the name of the states) are entered in the Excel sheet.

Drawing charts:

One of the greatest advantages Excel is having is of the option of drawing graphs and charts. Excel helps the user to create charts in two or three dimensions based on data entered in a worksheet. The user is having perfect control over all most all dimensions of the chart while drawing it in the worksheet. Following are the steps of drawing a simple chart in Excel worksheet.

The initial step is to create a data base in the worksheet containing the data series which the user wants to draw a chart. In Box-A1 derived below, the cell range A3 to B6 contains the data series that is to be drawn as a chart.

In the next step, the data of which charts are to be drawn are to be selected. After selecting the data series, the user can see that the data series are highlighted.

In the third step, click on the Chart Wizard button at the tool bar. This is basically a colored bar diagram shaped button.

This will open a short window written as ‘Chart Wizard-Step 1 of 4- Chart Type’. Again in between this window under the sub title ‘Standard Types’ the user can see two shall windows. The left hand window is with the heading ‘Chart Types’ where the user is having various types of chart options. On the other hand, at the right hand of ‘chart type’ wizard in each chart type Excel is again giving different shapes of the chart under the sub title ‘chart sub type’. The user has to select an appropriate type of chart based on the available data. For example, if the available data is a scientific data it is very accurate to choose one of the types of scatter plots (marks or no marks; straight lines or curves, etc.). For detail analysis of the chart type selection refer to chapter-7 of this book.

After selecting the chart type, click on ‘Next’ button. A small window with the chart will be displayed followed by the data range and the selected data series. If all the displayed facts are correct, then click on the ‘Next’ button and proceed further. If any change is required then click on the ‘Back’ button and the window will move back where necessary changes can be done.

When the next button is clicked in step-5, the chart option will displayed. Here the user has to type the chart title, X-axis title, Y-axis title. By clicking in ‘Axis’ option user can specify the axis requirements. By clicking in the ‘Legend’ option the user can specify the legend position in the graph, and so on. The legend is the key for the chart which indicates which colours are used for different series considered in the study. After the finalization of these options click on ‘Next’ button.

This is the fourth step in the Excel chart window where the Excel is asking the user about the ‘Chart location’ i.e., where the user wants to place the chart. The first option is that whether the user is wanted to place the chart in another sheet? The second option is asking for a place in the same sheet where the data is already stored. After deciding the place a click in the Finish button will display the complete chart.

As already stated above, Excel is having a number of options for formatting the font type, font size, color of the charts for better identification, formatting the chart area, formatting the plot area and such other options are also available. The user has to single click on the chart at its required place which needs formatting then has to click on right hand side of the mouse. It will display various formatting options that can be possible to change on the drawn chart. Suppose the user wants to change the appearance of titles. Then the procedure is to click on the chart title, make necessary changes on it. Similarly, if the user wants to change the legend position, then has to click on the legend and with the mouse button down, drag it to a new position.

Most of the time there is the need to clear the background of the chart. To do this, point the crosser in the grey background of the drawn chart. A pointer message flash with title ‘Plot area’, then click the right mouse button. From the displayed shortcut menu, select ‘Format Plot Area’. In the Area box, click on ‘None’. Then click OK. Click the mouse in out of plot area to de-select the chart.

The above steps are derived in the BOX-A4 below with an illustration of India-Africa trade figures for the better understanding of the readers:

Box-A4: Illustration to show procedures of drawing a chart in Excel

Since independence India has had cordial and friendly trade relations with Africa in general. Trade relations expanded considerably since 1947, particularly after the transition into 2nd millennium. India’s trade with Africa since 2005-06 is derived below.

TABLE-TA3: INDIA-AFRICA TRADE

Year

Total Trade (Import + Export) value in US $ millions

2005-06

11,872.09

2006-07

24,990.63

2007-08

34,693.74

2008-09

35,710.57

Source: India 2010, GOI.

A chart has to be drawn using the above derived data.

Step-1: Enter the data in Excel spreadsheet.

FIGURE-FA13: DATA FORMAT IN EXCEL

Step-2: Select the Data range to be drawn. After selection, the selected portion in the spreadsheet will be highlighted with blue colour.

Step-3: When the Chart Wizard button is selected it is asked for chart type which is the first step of the chart wizard. For the above data the ‘Line’ chart type which is the third one in the left hand window has been selected and in right hand ‘chart-sub type’ window the first option i.e., clustered column option has been selected.

Step-4: When the Next button in above step is clicked, it displays the second step with the heading ‘Chart source data’. In this step, each data entry has been checked and to proceed further Next button has been clicked.

Step-5: In this step various chart options have to be finalized. The setting up of ‘Title’ option based on our above example is shown in a figure below:

FIGURE-FA14: CHART OPTION FORMATTING

Step-6: After finalizations of all the options in the above derived the ‘Next’ button will open another one window ‘chart location’ windows. As stated above, here the user has to mention the location where he/she wants the chart to display. Here, the second option i.e., ‘As an object in Sheet1’ is chosen.

FIGURE-FA15: SETTING CHART LOCATION

Step-7: Clicking on the ‘Finish’ button will display the chart as derived below.

FIGURE-FA16: A LOOK AT CHART IN WINDOW

Step-8: In the last step, any editing in the format like change in font type, font size, etc., can be done by double clicking on the respective place in the graph. For example, the chart title in the above chart appears by defaulter in ‘Arial’ but the present format of writing is in ‘Times New Roman’. Repeat point 8 and point 9 derived above to format the titles and clearing the plot area respectively.

FIGURE-FA17: WINDOW SHOWING FORMATING PLOT AREA

CONCLUSION:

Excel as discussed above is a very useful tool particularly for data analysis. It is in general a specific purpose electronic spreadsheet that is developed to use to organize, calculate, and analyze data. Being the user friendly software, this software has been widely used and more popular by the students and researchers. It is having all the basic options so far as the introductory statistical and mathematical analysis is concerned. This software is also popular among the students because of the availability of user friendly chart drawing facility. The greatest advantage of this software is that there is no need of remembering the commands or writing the commands or developing the programmes while using it, rather all most all the tasks can be carried out by a single mouse click.