# An Introduction to Spreadsheets Using Microsoft Excel

Document Sample

```					Excel Grade Book Assignment (for MS 2007)

Introduction to Spreadsheets Using Microsoft Excel 2007

Overview

This Project is designed as a beginner’s introduction to using spreadsheets and to some of the
features of Microsoft Excel. We will cover topics such as the spreadsheet layout, entering and
editing data, calculating using operators and functions, and graphing. We will explore
a formatting and as a calculating tool.

Introduction

Spreadsheets are commonly used to do automatic calculations of rows and columns of numerical
data, much like the accounting ―spreadsheets‖ used by the financial community. But spreadsheets
can also be used to arrange various types of data for neatly formatted display.

It’s time to learn how useful spreadsheet programs like Microsoft Excel can be in your classroom.
This program can help you perform calculations and create charts and graphs based on your
data. Teachers can use Excel to calculate student averages and automatically assign letter
grades, to collect and analyze information, including student records, lesson notes, school activity
budgets, professional organization information, and data. Students may use Excel for scientific
data, weather journals, financial reports, nutritional diaries, and legislative voting records.

By the end of this activity, you will have learned:

How to input/format/organize data into a spreadsheet

How Excel can perform basic calculations and functions (averages, sums, and
more)

Microsoft Excel is one of many spreadsheet applications currently available. Most of these
applications have adapted the syntax and functions of VisiCalc, the original spreadsheet program.
By learning Excel you will also be learning to use the basic procedures at the heart of any
spreadsheet program. Pay particular attention to the underlined terms. They are part of the
vocabulary for using spreadsheets in general or Excel in particular.

Page 1 of 15
Excel Grade Book Assignment (for MS 2007)

Screen Layout

When you start working in Microsoft Excel, you begin using a workbook that contains screens
called worksheets. They are identified as Sheet1, Sheet2, and so on. Here is the initial screen
you see when you open Excel:

Formula Bar

Scroll Bars

Worksheet or
Chart Name

Sample Excel Window

The worksheet is divided into a grid consisting of columns, which are named by letters at the top
in the column heading, and rows, which are named by numbers at their left end in the row
heading. Column letters go from A to Z. If more than 26 columns are needed, they are named AA,
AB, … AZ, BA, BB, etc.
At the top of the window are the Menus. The Menus contain the Tools, which contain buttons for
view of the worksheet, are at the right and bottom of the window. Below the worksheet are
Worksheet or Chart Names. Use them to switch between worksheets, several of which can be
incorporated into a workbook.

Page 2 of 15
Excel Grade Book Assignment (for MS 2007)

Formula Bar
Name Box                                                                         Columns

Rows

Cell

Highlighted or Selected                             Fill Series Control

Exploring a Worksheet

A cell takes its name from the column and row it intersects. For example, the cell lying at the
intersection of the second column and second row (as highlighted on the sample Excel window
above) is named B2. The cell selector (or cell pointer) is the black border outlining the current
active cell. This cell address of the active cell is also shown in the Name Box located in the
Formula bar area at the left, just above the worksheet.
Because a cell can contain much more information than you see just looking at the cell, when a
cell is clicked or selected, the contents are shown in the in the Formula Bar on the right side, just
above the worksheet. When you click on a cell, the edges of the cell will be highlighted to show
which cell is currently active.
There are a number of ways to move around in a worksheet. Moving from one cell to another in
Excel is quick and easy. The ways to move from cell to cell include clicking a cell or using the
Go To command (Edit>>Go To), the scroll bars, the arrow keys, or the HOME, END, PAGE UP,
and PAGE DOWN keys. You can also use the ARROW, TAB and SHIFT-TAB, or ENTER keys
to move to a cell adjacent to the current active cell.

Page 3 of 15
Excel Grade Book Assignment (for MS 2007)

EXERCISE

1. Open the Microsoft Excel program from the START menu by selecting PROGRAMS and then
MICROSOFT EXCEL from the submenus. A window similar to the one shown above will appear.

2. Before moving ahead, quickly select FILE and then SAVE AS from the submenus. Save your file as
Grades.xls. Now continue with this Exercise.

3. Note the Excel window’s features as listed above.

4. Move the active cell to new locations by using the methods above. Note how various keys move the
active cell and how the address changes in the name box.

5. Switch to Sheet 2 and then back to Sheet 1 by clicking on their respective page tabs.

6. Click the HELP button       at the right end of the toolbar. Then click any button, menu, or other
feature of the window to get help on its function. This is an excellent way to discover the capabilities

7. Explore various buttons and menus using the HELP button.

Entering Data

To enter either numerical or text data into a cell, make the cell active and start typing. Whatever
you type appears in the formula bar                                                  just above the
worksheet.

Data is entered into the cell whenever you change the active cell by using one of the methods
above, click in the active cell, or click the check mark to the left of the formula bar. If you
change your mind and wish to cancel the current entry, click the        to the left of the formula bar
(or press the ESC key). You can also enter data directly into a cell by double-clicking the cell.

Spreadsheets use three types of data: numbers, labels, and formulas.

Numbers can stand alone or be used in calculations. They can be formatted to represent regular
numbers, currency, dates and times, percentages, etc.

Labels are usually chunks of text that identify values, thus their name. But labels can also serve
to position text on the screen (or a later printout) for formatted display.

Formulas are used to perform calculations or manipulate values from other cells and display the
results in the cell that contains the formula. Usually it is the result of the calculation that is
displayed in the cell, not the formula itself.

Page 4 of 15
Excel Grade Book Assignment (for MS 2007)

EXERCISE

1. Copy the contents of the sample Excel window above into your worksheet. Include all labels,
the exam and test scores, and the final grade.

2. If the column is not wide enough to hold all the data you enter, position the mouse pointer in the
column heading at the dividing point between the column whose width you wish to change and
the next. A double-headed arrow will appear that looks like this:    Hold down the left
mouse button and drag the column boundary to the desired position so there is room for all the
data within the column. Row heights can be changed in a similar manner. (Right-clicking a
column or row boundary displays a context-sensitive menu, which makes available several
other options.)

3. Format cells (centered, bold, italic, font size or type, etc.) any way you like by using the format
menu or format toolbar. To format a whole column or row at a time, select it by clicking in the
column or row header. Once the whole column or row is selected, you can apply formatting to
all of its cells at one time. You can select the whole worksheet by clicking the SELECT ALL
button, which lies at the intersection of the column and row headers at the upper left corner of
the worksheet.

4. Although we have not used the spreadsheet for its primary purpose—calculation—we have
arranged the data into a useful format for subsequent viewing. In the next set of exercises we
will perform some calculations.

Page 5 of 15
Excel Grade Book Assignment (for MS 2007)

Calculating

Spreadsheet calculations are performed using formulas. The simplest calculations perform arithmetic
operations on numbers embedded within the formula. An example of this type of calculation is 34 + 23 -
45. If these numbers are entered into a cell, the cell will display the result of this calculation or 12. (The
numbers must be preceded by an equal sign (=34+23-45) or the entry will be interpreted as a label

When entering formulas one must be careful to consider the order of operations***. All operations are not
of equal importance. Exponentiation (calculating a power of a number using the ^ operator) is performed
first. Next, multiplication (*) and division (/) operations are performed left to right. Last come the addition
(+) and subtraction (-) operations.

The order of operations can be modified by using parentheses. For example, =6+4/2 yields an answer of
8 (the 4/2 division operation is performed first), but =(6+4)/2 yields 5 (the addition is performed first
because it lies within parentheses).

***Remember Please Excuse My Dear Aunt Sally from high school math? It works in Excel too!

In the next exercise, we will substitute a formula for the final grade to avoid having to calculate the grade
before entering it.

Page 6 of 15
Excel Grade Book Assignment (for MS 2007)

EXERCISE

1. For each final grade, average the exam and quiz scores in a formula so that the final grade will
be automatically calculated. The first formula will be:

=(95+85+84)/3

An 88 will show in the cell after you click the check mark.

2. When entering the grade formula for the third student, a number like 93.666667 may show as
the result. To round the number so that only one digit appears after the decimal point, make the
cell active. Click the arrow next Number in the Home Menu. Set the Decimal Places to 1.

Or use the            buttons that are in the Number toolbar to decrease or increase the number
of decimals to the right of the decimal point.

3. To format several cells at once, select them by dragging over the cells to highlight them. Then
choose the format you want from the Home Menu or the right-click to view the formatting menu.
Use this method to format the Final Score Column for all students so all the grades will show
with one digit after the decimal point.

Page 7 of 15
Excel Grade Book Assignment (for MS 2007)

Re-entering the data in the formula to calculate final grades is tedious and can lead to errors. One of the
primary features of a spreadsheet is its ability to take data currently in cells and incorporate them into
formulas. In the next exercise, we will substitute cell addresses for the numbers entered above.

EXERCISE

1. Instead of the formula =(86+80+49)/3 previously entered as the final grade for the first student,
substitute the cell addresses of the original values:

=(B7+C7+D7)/3

Now you have used the original values in the formula without having to copy the contents of the
cell. Another advantage of using the cell name in a formula is that you can change the contents of
the cell and the results of the formula will change to reflect the new value.

2. Instead of entering a similar formula for the remaining two students, you can copy the formula
already entered. Select the cell you just edited. Then drag the square in the corner down. The
formula will automatically copy.

3. Click on the final score cell for the fourth student and then the fifth student. In the formula bar, note
that when the cell formulas were copied, from student to student, the row numbers were adjusted.

Fourth Student =(B7+C7+D7)/3

Fifth Student =(B8+C8+D8)/3

Page 8 of 15
Excel Grade Book Assignment (for MS 2007)

Several functions are available to make common calculations easier. One of these is the AVERAGE
function. In the next exercise we will use this function and a shorthand for a range of cells to further
simplify our formulas.

EXERCISE

1. For the second student’s final score substitute:

=AVERAGE(B5,C5,D5)

(note the commas between cell names) for the previously entered:

=(B5+C5+D5)/3

to compute the same values.

2. Now use the shorthand:

=AVERAGE(B5:D5)

to indicate the series of cells from B5 through D5.

3. If you wish, copy the contents of this cell to the final grade cells for the other two students as in the
previous exercise.

Sorting

After the gradesheet is finished, you can choose to view it in different ways. You might want to
sort the rows by student names or by scores or by letter grades. The next exercise will
demonstrate how to do this.

EXERCISE
1. Select all cells in the gradebook proper by dragging the mouse pointer from the first name to the
last letter grade. The main part of the gradebook should be highlighted.

2. For a simple sort, go to the Data Menu click one of the SORT buttons.             This will sort all
the columns by the data in the first column, in this case the student names. You can sort the data
in either ascending or descending name order by clicking the appropriate button.

3. For more sorting options, select the word SORT. Then select the pull down menu next to Name
and choose Final Score. Then click OK and the gradebook is now sorted by the final grade.

Page 9 of 15
Excel Grade Book Assignment (for MS 2007)

Summary Data: Statistics & Graphing

Summarizing data in Excel is quite easy. Functions exist for almost any statistical calculations
you wish to perform. In the next exercise, we’ll average the final scores to get a general picture of
how the student’s did on each of the tests and on the final score.

EXERCISE
1. Go to the Formulas Menu

2. To calculate the average for the Exam 1, go to cell B11.

3. Choose Recently Used and select AVERAGE.

4. When the box pops up click OK and the formula will average the column for Exam 1.

5. Copy the formula in cell B11 into cells C11 through E11 as you did above to see averages for
all test and final scores.

Page 10 of 15
Excel Grade Book Assignment (for MS 2007)

EXERCISE
1. Highlight the range of cells from A3 to B9. This should include the Name and Exam 1
headings, the list of names in the first column and all the exam scores.

2. Go to the Insert Menu and select the Columns graph. Choose the first chart type under 2D
Column and the chart will appear.

3. Click on the chart and drag it to an open area of the worksheet.

4. Enlarge the chart by dragging on the right side.

Page 11 of 15
Excel Grade Book Assignment (for MS 2007)

Earlier, when we copied a formula containing cell address to another cell, addresses in the copied
cells were changed to become relative to the cell to which they were copied. Sometimes we want
formulas to refer to a constant cell address and not shift when the cell is copied. Addresses that
always refer to the same cell, even after being copied are known as absolute addresses. An
example of an absolute cell address is \$C\$3. The dollar sign before both the column and row
components of the address above make it absolute. Cells can also have mixed addresses. If
either dollar sign is omitted, the component without the dollar sign is still relative and will shift as
the original formula is copied. \$C3 and C\$3 are mixed addresses.

We’ll use mixed addresses in the next exercise to add a weighting component to the final grade
calculation. In our adjusted scoring scheme, the first exam will count double and the final exam
triple. We will insert a new row to hold the weighting amounts. Of course we can no longer simply
average the three scores to obtain the final score, so that formula will have to be amended.

EXERCISE
1. In Row 2, enter then a 2 above the EXAM 1 label, a 1 above QUIZ, and a 3 above FINAL EXAM. If
you wish, you can label the row weightings.

2. We’ll put the sum of all the weightings above the FINAL SCORE label. There are several ways to do
this. We could write the formula:

=(B2+C2+D2)

or use the SUM function:

=sum(B2,C2,D2)

Or, we can use the AUTOSUM button in the Formulas Menu.

3. To change the final score formula to incorporate the weightings, each score will be multiplied by its
weighting and the sum divided by the sum of the weightings. Since the weighting numbers are in a
constant row, we must use a mixed address for the weighting address. Enter the formula below into
the final score cell for the first student:

=(B\$2*B4+C\$2*C4+D\$2*D4)/E\$2

4. Copy the first student’s final score cell to the other final score cells. Note that the weighting
components to the formula correctly refer to the address of the weighting cells.

Page 12 of 15
Excel Grade Book Assignment (for MS 2007)

Page 13 of 15
Excel Grade Book Assignment (for MS 2007)

EXERCISE

1. Copy the grading scale table (see cells B13 – C 17 below) into cells with cell B13 in the upper
left-hand corner.

2. Select cell G4 and follow these steps to enter the formula:
a)   Go to the Formulas Menu and click the   sign at the left.
b)   Type VLOOKUP in the top box and click GO.
c)   Then choose VLOOKUP from the list and click OK.
d)   Type E4 into the top box.
e)   Type B13:C17 into the second box.
f)   Type 2 into the third box.
g)   Type True in to the fourth box.
h)   Click OK

3. Follow the directions to create a formula like the one below for the rest of the students.

=VLOOKUP(E4,B9:C13,2,TRUE)

The first argument, E4, is the value that is being looked up. The second, B13:C17, is the
(vertical) table that contains the lookup values. (Note the mixed addresses and the colon that
indicates a range of cells that extend across both columns and rows.) The third argument tells
the lookup function to look in the second column for the value to be placed in the current cell.
The last (optional) argument, TRUE, says that the lookup values are in ascending order.

Page 14 of 15
Excel Grade Book Assignment (for MS 2007)

Lookup Tables

The weightings we added to the gradebook have changed the final scores and some of the final
including Excel, have lookup functions that will accomplish the task for us. Before we use the
lookup function, a table must be constructed to establish the relationship between numerical and
letter grades. The VLOOKUP function, which we will be using, then refers to the table, as seen
below.

If the final score is equal to or greater than the first number in the first column (0) and less than
the second number in the first column (60), the first letter grade in the second column (F) is
selected. However, if the score is equal to or greater than the second number in column 1, the
lookup function sequences down one row and continues the process until it reaches the end. For
example, if the score being looked up is 75, the lookup function will continue down the table until
it determines that 75 is equal to or greater than 70 and less than 80. The letter grade C is then
selected as the value.

(For the HLOOKUP function the table would be arranged horizontally instead of vertically.)

YOU ARE NOW DONE!!!!

Save your Spreadsheet as P5_EXCEL_LASTNAME and submit it in to me by email