STA01A1 Practical 1, University of Johannesburg 1
PRACTICAL 1
Getting Started with Microsoft EXCEL
Microsoft Office Excel is used to store data and crunch numbers in all types of businesses. The
spreadsheet program's power lies in Excel functions and Excel formulae, which allow all sorts of
data analysis, from simple sums to standard deviations.
Basic operations
Microsoft Excel has the basic features of all spreadsheets, using a grid of cells arranged in
numbered rows and letter-named columns to organize data manipulations like arithmetic
operations. It has a battery of supplied functions to answer statistical, engineering and financial
needs. In addition, it can display data as line graphs, histograms and charts, and with a very
limited three-dimensional graphical display. It allows sectioning of data to view its dependencies
on various factors for different perspectives (using pivot tables for example), etc.
Parts of the Excel 2010 Screen
ROW
COLUMN
Active Cell
The active cell is recognized by its black outline. Data is always entered into the active cell.
Different cells can be made active by clicking on them with the mouse or by using the arrow keys
on the keyboard.
STA01A1 Practical 1, University of Johannesburg 2
File Tab
The File tab options are mostly related to file management such as opening new or existing
worksheet files, saving, printing, and a new feature - saving and sending Excel files in PDF format.
Formula Bar
Located above the worksheet, this area displays the contents of the active cell. It can also be
used for entering or editing data and formulas.
Name Box
Located next to the formula bar, the Name Box displays the cell reference or the name of the
active cell.
Column Letters
Columns run vertically on a worksheet and each one is identified by a letter in the column header.
Row Numbers
Rows run horizontally in a worksheet and are identified by a number in the row header. Together
a column letter and a row number create a cell reference. Each cell in the worksheet can be
identified by this combination of letters and numbers such as A1, F456, or AA34.
Sheet Tabs
By default there are three worksheets in an Excel file. The tab at the bottom of a worksheet tells
you the name of the worksheet - such as Sheet1, Sheet2 etc. Switching between worksheets
can be done by clicking on the tab of the sheet you wish to access. Renaming a
worksheet or changing the tab color can make it easier to keep track of data in large spreadsheet
files.
Quick Access Toolbar
This customizable toolbar allows you to add frequently used commands. Click on the down arrow
at the end of the toolbar to display the toolbar's options.
Ribbon
The Ribbon is the strip of buttons and icons located above the work area. The Ribbon is organized
into a series of tabs - such as File, Home, and Formulas. Each tab contains a number of related
features and options.
STA01A1 Practical 1, University of Johannesburg 3
1. CALCULATING FORMULAE
In EXCEL you can enter a formula into any cell. The formula refers to data that is entered into
other cells. This data is used to do a calculation. All formulae in EXCEL begin with an equal sign
(“=”).
Refer to the file “Prac 1 Data, Sheet 1”.
There are two ways to create a formula:
1. Type the following into cell C2: “=A2+B2” or “=sum(A2:B2)” and press <Enter>.
2. An alternative way of calculating a formula is as follows:
a) Click on cell C2.
b) Click on [𝑓𝑥] in the toolbar.
c) From the Function category list, select All.
d) From the Function name list, select Sum and click OK. A formula window will appear
where you will be prompted to input values or cell references.
e) Click on the icon on the right-hand side of each input line. The dialog box will
disappear. Select cell A2 and B2 by highlighting them. Click on the icon on the right-
hand side of the input line again to return to the dialog box and click OK.
3. To repeat this for row 3 through 10, you do not have to enter the formula into every cell.
EXCEL can automatically change the cell references. Once the formula has been entered
into the first cell, move the mouse pointer to the bottom right corner of the cell. It will
become a cross hair. Left-click and hold down the mouse button, then drag the pointer
down to cell C10 OR simply double click the mouse button to copy the formula to the
other cells. EXCEL will calculate all the values.
NB: Sometimes you want to drag a formula, but you do not want either the row
reference (i.e. 2 or 3) or the cell reference (i.e. A or B) to change. This can be done by
adding a $ symbol in front of the corresponding cell reference.
Exercise 1
Refer to the file “Prac 1 Data, Sheet 1”.
Complete the table by entering the appropriate formula in each cell and calculate the sum of each
column.
To define a name for a data range:
Highlight all the values in the data range. Right-click anywhere in the data range and select
Define Name. Give your dataset a name and select OK.
Exercise 2
Refer to the file “Prac 1 Data, Sheet 2”.
1. Compute the median of the data using the formula “=median(data)”.
2. Compute the mean (average) of the data.
3. Compute the variance and standard deviation of the data using the VAR.S function.
STA01A1 Practical 1, University of Johannesburg 4
NOTE: There are two functions in EXCEL for calculating the variance: The function VAR.S
calculates the sample variance, whereas the function VAR.P calculates the population
variance (i.e. it divides by N, the population size, not by n - 1 as in the sample variance
formula).
4. Now Select Data, Data Analysis, Descriptive Statistics and click OK. Highlight all the
data as Input Range. Make sure to tick the Summary Statistics box. Click on Output
Range and type in an output location. The summary statistics will appear in the selected
location in your spreadsheet.
2. CONSTRUCTING HISTOGRAMS
Refer to Sheet 3 from the file “Prac 1 Data”, which gives the top 40 stocks on the over-the-counter
market, ranked by percentage of outstanding shares traded on a particular day.
To create a histogram
1. Excel refers to the upper bound of a class interval as the bin, meaning that Excel uses a
method of right inclusion (contrary to what our text book uses!)
2. To create the bin range for the data in Sheet 2, enter the values (3.5, 4.5, 5.5, …, 12.5)
into column B, labelling them as “Bin Range”.
3. Select Data, Data Analysis, Histogram and click OK. The histogram dialog box will
appear.
4. Highlight the appropriate Input Range and Bin Range for the data. Make sure to click the
“Labels” and “Chart Output” check boxes.
5. Pick a convenient cell location for the output and click OK.
6. Edit the gap width of the histogram by double-clicking on a bar and then changing the width
to 0%.
3. CONSTRUCTING BOXPLOTS
Refer to Sheet 3 from the file “Prac 1 Data”, which gives the top 40 stocks on the over-the-counter
market, ranked by percentage of outstanding shares traded on a particular day.
To create a boxplot
1. Highlight the variable “Stocks”.
2. Select Insert, Recommended Charts, All Charts, Box & Whisker and click OK.
References:
http://www.dummies.com/how-to/content/excel-formulas-and-functions-for-dummies-cheat-
she.html
http://en.wikipedia.org/wiki/Microsoft_Excel
http://spreadsheets.about.com/od/excel101/ss/2010-06-26-Parts-Of-The-Excel-2010-
Screen.htm
---oOo---
