Introduction
Microsoft Excel is a popular spreadsheet program from Microsoft. MS Excel is an automated version of the
paper-based spreadsheet that makes it easier to manipulate, process, and view
the data. Excel allows you to create electronic spreadsheets with the
ability to insert mathematical formulas to perform automatic calculations. Excel implements a graphical user interface and the
ability for the user to navigate the application via pointing and clicking
your mouse. Excel is a very versatile
application that you can use to balance smaller tasks, like your
checkbook, while simultaneously providing a robust infrastructure to manage a
small business. Task
Using MS Excel, integrate a spreadsheet for your
licensure/content area. The spreadsheet can be one that students would create
from scratch, or from a template you provide, or one you create to use as an
instructional or assessment instrument. This spreadsheet should include a
completed example, or a student template with completed example, or a student
job aid with completed example. The spreadsheet should utilize the
capabilities of Excel in manipulating or visually representing numbers. Resources
For assistance in learning how to use MS Excel please visit the website
below: http://www.fgcu.edu/support/office2000/excel/ Note: The items listed below under "Features selected for use"
are only possible incorporations of capabilities. By no means do you have to
include every one into the document. Only those you feel are appropriate for
your integration and that you select for use will be evaluated and graded.
You will not be penalized for omission of features. Evaluation
|
Conclusion
MS Excel is a powerful software application that allows you to create electronic
spreadsheets with the ability to insert mathematical formulas to perform
automatic calculations as well as manipulate data. Students successfully
completing this assignment will demonstrate growth and ability in technology and
showcase skills providing the foundation for the creating instructional
materials using word-processing, database, spreadsheet, presentation, and web
development software [CPBS 4.1, 4.2, 6.1, 6.2, 6.3, 7.1, 7.2, 7.3, 7.5, 8.5]
[ISTE I.A, I.B, II.A, II.B, IV.B, V.A ,V.C, V.D]
I.
What is a Spreadsheet?
a. A
spreadsheet is the computer equivalent of a paper ledger sheet. It consists of
a grid made from columns and rows. It is an environment that can make number
addition, subtraction, and multiplication more efficient.
II.
What are some of the components of a spreadsheet?
a. Column
is defined as the vertical space that is going up and down the window. Letters
are used to designate each COLUMN'S location.
i.
b. Rows,
is defined as the horizontal space that is going across the window. Numbers
are used to designate each ROW'S location.
i.
c. Cell,
is defined as the space where a specified row and column intersect. Each CELL
is assigned a name according to its COLUMN letter and ROW number.
i.
III.
In a spreadsheet there are three types of data
a. Label, text with no numerical value
b. Constants, just a number -- constant
value
c. Formulas, a mathematical equation used
to calculate
i.
Examples:
|
Data types |
Examples |
descriptions |
|
LABEL |
Name, Wage, Days |
anything that is
just text |
|
CONSTANT |
5 or 3.75 or -7.4 |
any number |
|
FORMULA |
=5+3 or = 8*5+3 |
math equation |
*ALL formulas MUST begin with an equal sign (=).
IV.
Labels typically do not have a value associated with
them. Labels are used to identify what you are talking about.
a. Examples
are student names, class assignments, attendance, days etc.
b. Note:
labels are used to help identify what you are talking about within
your spreadsheet. They are NOT for
the computer but rather for US so we can clarify and identify what we are
doing.
V.
Constants are entries with specific fixed values. For
example, if someone asks you how old you are, you would answer with a specific
answer. Sure, other people would have a different answer, but it is a fixed
value for each person.
a. In
this example: the constants are:
·
$12,000
·
9.6 %
·
60
b. As
you can see from this example that there may be different types of numbers.
Sometimes constants are referring to dollars, sometimes referring to
percentages, and other times referring to a number of items (in this case 60
months). These amounts are typed into the computer and are used to establish
the total.
c. Note:
We use constants to enter FIXED number
data.
VI.
Formulas are entries that have an equation that
calculates the value to display. When using formulas, we DO NOT type in the
numbers we are looking for; we type in the equation. This equation will be
updated upon the change or entry of any data that is referenced in the
equation.
a. When
using formulas in excel you should type the reference of the constants (not the
constant), the cell where the data is stored, Not the actual data.
b. Below are some example formulas for review
and practice.
i.
Addition, =SUM(A2:A09)
ii.
Subtraction, =B3-C3
iii.
Multiplication, =B4*C4
iv.
Average, =AVERAGE(A3:A9)
c. Note: Formulas are mathematical
equations. They are used to calculate a value to be displayed. There is a list
of the functions available within Excel that can be located on the menu
bar by selecting INSERT
and moving down to Function.
*Formulas or Functions MUST BEGIN with an equal sign (=).
VII.
When you are entering formulas
into a spreadsheet you want to make as many references as possible to existing
data. If we can reference that information we don't have to type it
in again. AND more importantly if that OTHER information changes, we DO-NOT
have to change the equations.
If you work for 23 hours and
make $5.36 an hour, how much do you make? We can set up this situation using
·
three labels
·
two constants
·
one equation
Let's look at this equation in B4:
= B1 * B2
= 23 * 5.36
Both of these equations will
produce the same answers, but one is much more useful than the other.
DO YOU KNOW which is BEST and
WHY?
It is BEST if we can Reference as much data as possible as opposed to typing data into
equations.
In our last example, things were pretty straightforward.
We had number of hours worked multiplied by wage per hour and we got our total
pay. Once you have a working spreadsheet you can save your work and use it at a
later time. If we referenced the actual cells (instead of typing the data into
the equation) we could update the entire spreadsheet by just typing in the NEW
Hours worked. And -- you're done!
Let’s look at a new spreadsheet:
- hours have been changed
to 34
- wage is the same
- total pay would now be
= 34 * 5.36
§
but would still be =
B1 * B2
If we had typed in (=23 * 5.36 )
the first time and just changed the hours worked, our equation in B4 would
still be (=23 * 5.36 )
INSTEAD we typed in references to the data that we wanted
to use in the equation.
We typed in (=B1*B2). These are the locations of the data that we want to use
in our equation.
It is BEST if we can Reference as much data as possible as opposed to typing data into
equations.
For these following examples
let’s consider the following data:
|
|
|
Operation |
Symbol |
Constant |
Referenced |
Answer |
|
Multiplication |
* |
= 5 * 6 |
= A1 * B3 |
30 |
|
Division |
/ |
= 8 / 4 |
= A3 / B2 |
2 |
|
Addition |
+ |
= 4 + 7 |
= B2 + A2 |
11 |
|
Subtraction |
- |
= 8 - 3 |
= A3 - B1 |
5 |
Probably the most popular function in any spreadsheet is
the SUM function. The Sum function takes all of the values in each of the
specified cells and totals their values. The syntax is:
- =SUM(first value,
second value, etc)
In the first and second spots
you can enter any of the following (constant, cell, range of cells).
- Blank cells will
return a value of zero to be added to the total.
- Text cells can not be
added to a number and will produce an error.
|
Lets use the table here for
the discussion that follows: We will look at several different specific examples
that show how the typical function can be used! Notice that in A4 there is a
TEXT entry. This has NO numeric value and can not be included in a total. |
|
|
Example |
Cells to ADD |
Answer |
|
=sum (A1:A3) |
A1, A2, A3 |
150 |
|
=sum (A1:A3, 100) |
A1, A2, A3 and 100 |
250 |
|
=sum (A1+A4) |
A1, A4 |
#VALUE! |
|
=sum (A1:A2, A5) |
A1, A2, A5 |
75 |
There are many functions built into many spreadsheets.
One of the first ones that we are going to discuss is the Average
function. The average function finds the average of the specified data. (Simplifies
adding all of the indicated cells together and dividing by the total number of
cells.) The syntax is as follows.
- =Average (first value,
second value, etc.)
Text fields and blank entries
are not included in the calculations of the Average Function.
|
Let’s use the table here for
the discussion that follows: |
|
|
Example |
Cells to average |
Answer |
|
=average (A1:A4) |
A1, A2, A3, A4 |
62.5 |
|
=average (A1:A4, 300) |
A1, A2, A3, A4 and 300 |
110 |
|
=average (A1:A5) |
A1, A2, A3, A4, A5 |
62.5 |
|
=average (A1:A2, A4) |
A1, A2, A4 |
58.33 |
In Excel there is a help tool for functions called the
Function Wizard.
There are two ways to get to the function wizard. If you
look at the Standard Toolbar, the function 
wizard icon looks like the icon below.
The other way to get to the function wizard is to go to
the Menu select INSERT, then select FUNCTION.
Either way you get there, at this point Excel will list
all of the functions available. Upon choosing the function, Excel will prompt
you for the information it needs to complete the function. Mini descriptions
are available for each of the cells. It is often necessary for you to
understand the functions in order to be able to figure out these descriptions.
Yeah, I know it would have been nice to know this
earlier, but it is important for you to understand how the functions work
before you start using the Function Wizard. It is faster to type the basic
function in from the keyboard as opposed to going through the steps of this
tool.
Well, that is all of the
functions we are going to cover.
A question that everyone (who
has ever worked on a spreadsheet) has asked at one time or another is,
"Where did all my numbers go?" or same question, "Where did all
of those ####### come from and why are they in my spreadsheet?"
The problem is the number trying to be displayed in a
particular cell does not have enough width to display properly. To clear up the
problem we just need to make the column wider. You can do this many ways.
Here are two ways to change the column width:
a. Select
the column (or columns) with the problem by
clicking on their labels (letters). Then you choose the MENU FORMAT. Go down to
COLUMN and over to WIDTH and type in a new number for the column width.
b. Move
the arrow to the right side of the column
label and click and drag the mouse to the right (to make wider) or left (to
make smaller). Let up on the mouse button when the column is wide enough.
*Notice the cursor changes to a
vertical line with arrows pointing left and right.
In many spreadsheets you can also change the vertical
height of a row by moving the lower edge of the row title (number).
Sometimes we (all) make mistakes or things change. If you
have a spreadsheet designed and you forgot to include some important
information, you can insert a column into an existing spreadsheet. What you
must do is click on the column label (letter) and choose in Columns from
the Insert menu. This will insert a column immediately left of
the selected column.
As you can see from this example there was a blank column
inserted into the spreadsheet. You might wonder if this will affect your
referenced formulas. Yes, the referenced cells are changed to their new
locations. For example:
Cell C4 was =C3+B4
and now is =D3+B4
Likewise, we can also insert rows. With the row label
(number) selected you must choose the Row from the Insert menu.
Again this will insert a row before the row you have selected.
The formulas will be updated to
their corresponding locations.
C3 was = C2+B3
NOW C4=C2+B4
Numbers can usually be represented quicker and to a
larger audience in a picture format. Excel has a chart program built into its
main program. The Chart Wizard will step you through questions that will
(basically) draw the chart from the data that you have selected. There
are many types of charts. The two most widely used are the bar chart and the
pie chart.
The BAR Chart is usually
used to display a change (growth or decline) over a time period. You can
quickly compare the numbers of two different bar charts to each other.
The PIE Chart is usually
used to look at what makes up a whole. If you had a pie chart of
where you spent your money you could look at the percentages of dollars spent
on food (or any other category).
You can add legends, titles, and
change many of the display variables.