Introduction to excel: basic operations

The aim of this helpsheet is to familiarise you with the basic operations of Microsoft Excel.

In general statements in italics are commands from a menu or in a button, which you need to click using the mouse. Statements in bold indicate things you need to type on the screen.

1.    Opening Excel

To open Excel click Start/Programs/Microsoft Excel

2.    Navigating around the Excel Window

After opening the program you should see the Excel window. Note the following:

 

Title Bar – very top of the main window.

 

Main Menu – just below the title bar. There are several main menu items (File, Edit, View, Insert, Format, Tools, Data, Window, Help) and each main menu item has a corresponding drop-down menu.

Toolbars – Various symbols that allow one-touch access to various Excel commands.

Work Area – The Excel spreadsheet or worksheet. Columns are lettered and rows are numbered. The cells or worksheet space are where the text or numeric entries are recorded. Each new file contains three sheets (bottom of the screen), clicking on another sheet opens another (blank) worksheet. More sheets can be added if required (in total you can have up to 255 worksheets!). All the worksheets make up the workbook, and the workbook is the name given to Excel documents (files).

3.    Data Entry

Before you can start to analyse data in Excel you must enter the data or open a previously saved file. To create a new workbook click File/New/Workbook.

 

Manual Entry from the Keyboard

 

Suppose you wanted to test the hypothesis that firm performance was determined by wage expenditure. In Table 1 you are given wage expenditure and performance data for a cross-section of 20 football clubs (Sources: Rothmans Football Yearbook, Deloitte and Touche’s Survey of Football Finance).

 

Table 1: Wage Expenditure and Performance

of Premier League Football Clubs (Season 1997/98)

 

Club Code	Points	Wages (£m)
1	78	15.28
2	57	10.07
3	35	26.13
4	58	14.34
5	40	6.16
6	63	14.87
7	52	8.40
8	33	5.34
9	55	5.58
10	40	10.93
11	59	12.31
12	53	8.91
13	65	15.03
14	77	22.55
15	44	17.49
16	44	7.57
17	48	4.78
18	44	12.06
19	56	8.30
20	44	6.02

 

To enter the above data in an Excel worksheet, activate the cell (e.g. A1) using the mouse. Now type club, press the Return key to move down to the next row (A2) and type 1 followed by the Return key. Repeat for the remaining 19 values (alternatively, use the autofill option). Once you have entered the data for the first column use the cursor keys or mouse to activate the cell B1, type Points and press return. Now enter the numerical data as it appears in the above table. Follow the same procedure for the third column – column C - Wages.

If, on the other hand, the data already existed in electronic form - either in another package such as Microsoft Word, database, spreadsheet in another statistical program or a document on an Internet site – it is possible to import the data directly into an Excel worksheet.

For example, suppose the data in Table 1 was available in a text file (a text file is a file which contains only text and the columns of data are usually separated by either a space, comma or tab). The file name is Premier.txt.

To import the file into an Excel workbook double-click on Premier.txt. You will now be confronted with the Text Import Wizard. There are three steps.

 

Step 2: Column breaks have been placed between the various columns, if necessary you can add (clicking a blank space), remove (double-click a line) or move the breaks (click and drag). Once completed click Next.

 

Stage 3: Define the column format. The default format (General) will work in most cases. Click Finish, you should find that the data has been successfully imported.

 

At this stage it is a good idea to save the workbook. Click File/Save As and type Premier in the file name box, then click OK. You can either save the file onto floppy disk (drive A) or on your user directory drive (H drive).

 

4. Creating Scatterplots

As a precursor to undertaking regression analysis an initial visual inspection of the data can be useful in determining the shape of the relationship between the variables (i.e. linear or non-linear) and in determining the presence of outliers and influential (excluded) variables. Charts can either be inserted as an embedded chart object (i.e. a chart in the current worksheet) or as a chart sheet (i.e. a separate sheet). In this section I will demonstrate how to create a scatterplot in Excel, again using the workbook Premier.xls.

Firstly open the file Premier.xls and highlight the columns Wages and Totpoints.

 

Step 1: Click XY (Scatter) from the chart type list and then click Next.

 

Step 2: A scatter plot of the two variables should be presented. However, the variables are the “wrong way around” in the sense that the dependent variable, here Points, should be located on the y-axis (vertical axis) and Wages – the explanatory variable – on the x-axis (horizontal axis). To change the position click Series and replace the letter “B” in the X Values box with the letter “C”, likewise replace “C” with “B” in the Y Values box. Now click Next.

 

Step 3: Now you can add titles to the axes and the chart, add/remove gridlines and add/remove a legend and include data labels. Click Next.

 

Step 4: Choose whether to insert the chart as a chart sheet (i.e. “new sheet”) or as an embedded chart (“as object”). Click Finish.

If the chart is embedded you can change its location by cutting and pasting, or by clicking a blank spot within the chart moving the chart to a different location. You can change its size by moving the mouse pointer to the bottom right hand side of the chart dragging the pointer down until the desired size is achieved.

Finally, if you want to edit any individual component of the chart (e.g. plot symbols, axes scale etc) move the mouse pointer and right-click the component you want to change. This brings up a drop down menu from where the necessary alterations can be made.

5. Data Description

Another useful preliminary tool is to calculate various summary measures or descriptive statistics. Typically these statistics can be classified into those that measure centre (mean, median and mode), those that measure spread (variance, standard deviation and range) and those that measure shape (skewness and kurtosis).

As an example, to calculate the mean number of points for the file Premier.xls go to cell B22 and click on the function button in the toolbar (f_x). Make sure the range of observations used to calculate the mean is correct (here the range should be B2:B21) before clicking on AVERAGE. The mean (average) number of points is now recorded in cell B22. In the formula bar this is written as AVERAGE(B2:B21). The complete list of descriptive functions is as follows:

 

Measures of Centre

Mean MEAN(data range)

Median MEDIAN(data range)

Mode MODE(data range)

 

Measures of Spread

Variance VAR(data range)

Standard deviation STDEV(data range)

Maximum value* MAX(data range)

Minimum value* MIN(data range)

 

Measures of Shape

Skewness SKEW(data range)

Kurtosis KURT(data range)

All of these summary measures can be calculated simultaneously (and inputted as a new worksheet). Click Tools/Data Analysis, locate the Descriptive statistics option, click OK. Enter cell range for Totpoints (B1:B21) in the range box. Click label as first row contains names of the variables entered. Type Totpoints in the text box to the right of the New Worksheet Ply in the Output Options section. Do likewise for Wages.

We can also look at the variables together. For example, we may want to determine how correlated the two variables are. The scatterplot we constructed in Section 5 suggests that a strong positive relationship exists but how strong is the relationship?

To calculate the (Pearson) correlation locate an empty cell below the data and the various summary measures calculated above. Click on the function button on the toolbar and locate the function CORREL (or PEARSON). Enter the data range for the two arrays B2:B21 and C2:C21 and then click OK.

6. Bivariate (Simple) Linear Regression

When we plotted the two variables in our scatterplot the values did not fall exactly in a straight line. In regression analysis we wish to find the line that best estimates the relationship between y (the dependent or response variable) and x (the explanatory or independent variable). The plotted line is called the fitted regression and the equation of the line is known as the regression equation.

Return to the scatterplot you constructed in Section 4. To add a regression line right-click any of the data points in the graph and click Add Trendline from the menu. Make sure the Linear regression type is highlighted in the Type tab option. You can also display the regression equation and R-squared value on the chart by going into the Options Tab and clicking on Display equation on chart and Display R-squared value on chart.

 

Constructing a Table of Regression Statistics

A table of regression statistics is much more useful, particularly for hypothesis testing and other diagnostic purposes. To construct a table of regression statistics we must first of all make sure that the Analysis ToolPak is installed. If it is not, then:

Click Tools/Add-Ins and then click Analysis ToolPak checkbox from the Add-Ins list box and then click OK. We are now ready to construct our regression statistics.

Click Tools/Data Analysis, locate the Regression option, click OK. Enter cell range for Y (B1:B21) and X (C1:C21) in respective range box. Click label as first row contains names of the variables entered. Type regression statistics in the text box to the right of the New Worksheet Ply in the Output Options section. You can also request to observe the residuals from the regression.

7. Data Transformations

Sometimes you may want to transform one variable in another form. For example, if the relationship between two variables is exponential then undertaking a logarithmic transformation of the variables will make the relationship linear (see Lecture 6) or reduce mis-specification problems (Lecture 7) and heteroskedasticity (Lecture 8).

To transform Wages into (natural) logarithmic form:

In the first cell of a new column (D) write the variable name LnWages. Next highlight the second cell of the column and click on the function button in the toolbar. Using the function category Math and Trig locate the function name LN, click OK. The number you want converting is the Wages value which is contained in row 2 (i.e. C2). A quick way of repeating this for all the other values is to use the autofill function. Move the mouse pointer to the bottom right hand side of the cell so that the pointer changes from a white plus sign to a black plus sign. Click and hold down the mouse button and drag the fill handle down to cell D21.

Exponential transformation (e^x): EXP

Variable raised to a particular power: POWER