Transcript NJ NY ACRAO Conference - Long Island University

NJ NY ACRAO Conference
November 1, 2002
Beginning Excel XP for
College Administrators
Session III 2:20 – 3:30 PM
1
I. Overview – Microsoft Excel
2000/XP
• Using Microsoft Excel in College
Administration
• The participants will be introduced to the basic concepts of spreadsheet
•
•
•
•
analysis. The many uses of spreadsheets in college administration will
be discussed. Topics will include basic spreadsheet setup, formatting a
spreadsheet, formulas and functions, charting, enhancing the
appearance of your spreadsheet.
Materials are available on the web after the conference.
Electronic Spreadsheet - help you perform numeric calculations rapidly
and accurately.
Excel provides powerful features that enable you to analyze and
present data in meaningful charts formats
Objectives of this session include:
– Introduction to the basic features of the Excel Worksheet
– Familiarize with the environment as you create, edit, modify, format
and save spreadsheets.
2
II. Objectives of the Session
• The following Excel features will be presented
in this first session:
– Create, edit, preview, print, save and close and Excel
Workbook
– Edit, copy and move cell entries, and work with ranges
– Copy formulas with relative and absolute cell references
– Formats, values and labels, and apply color, background,
patterns and border
– Insert and delete rows and columns
– Design, create and modify charts
– Display formulas and display and print formula content
– Build formulas using the Paste Formula Functions
3
III. Spreadsheets - Defined
• Microsoft Excel is an electronic spreadsheet program that runs
on Windows-based computer systems – Workbooks – refers to
the electronic spreadsheets created using Excel.
• When you open Excel, the Worksheet Window appears – it
contains the necessary tools and features that enable you to
work effectively with Excel.
• The elements of the Worksheet Window include: Worksheet
Window
• Cell Pointer
• Title Bar
• Pointer
• Name Box
• Formula Bar
• Toolbars
• Sheet Tabs
• Status Bar
4
Sample - Worksheet Basics
5
IV. Worksheet Basics
• Worksheet is a collection
•
of information laid out in
columns and rows
Intersection of a column
and row creates a cell –
Each cell has a unique
cell address (cell
reference)– that is
derived from the column
and row number.
For Example Column A, Row 15 is A15
• There are type types of
input that can be placed
in a cell:
• Label – piece of information that
•
•
identifies – May be referred to as
text values – (prefer labels so as
not to confuse with numerical
values
Values – numerical data that will
be involved in a calculation (not
necessary by for the most part) –
Values may be formatted in a
variety of ways to improve the
appear of the spreadsheet.
Formulas – Collection of values,
cell references, operators and
predefined functions that when
evaluated by excel produce a
result.
6
V. Opening a Excel Worksheet
Activating and Selecting Cells
• Create a blank workbook file
• Create a workbook file based on a template
• Activating and Selecting Cells ( )
See Notes
– To enter information into a cell – the cell must first be active
– A cell is active when there is a dark or colored border called the cell
pointer around it
– Extent of a Spreadsheet (<END>)<Down arrow> and <right
arrow>
– To enter labels, values or formulas – you select the cell and begin
typing.
– A Range of Cells can be selected – A range is a rectangular
selection of cells defined by the top left and bottom right cell
reference.
– Selection of Multiple Ranges; Columns and Rows
– Deselecting a Range
7
VI. Entering Labels, Values and
Formulas
• Select and Activate the cell where you want to
•
•
place your information
As you type the information appears in the cell
and the formula bar at the top of the
spreadsheet
Values – The term refer should only refer to
numeric information that will be used in
spreadsheet calculations. Labels – should be
reserved to those words that describe numeric
information on the worksheets. Formulas – Cell
references , operators and functions that act
8
upon the values in adjacent cells.
VII. Formula Basics
• Points to Remember in working with formulae:
– If a formula uses cell references to refer to other cells
and the contents of one or more of those cells
changes, the results of the formula change too.
– All formulas begin with an equals (=) sign. This
indicates to Excel that a formula is being entered
– Formulas can contain any combination of values,
references, operators and functions
– Formulas are not case sensitive. Excel however will
convert cell references and functions to uppercase.
– Order of Operations is Important with regard to how
Excel will execute a Formula
9
VIII. Editing Worksheets
• Most of what I will explain here will be in
the form of a hands-on demonstration:
– Editing Cell Contents
–
–
–
–
–
–
•
•
•
•
Editing while you entering data
Editing a completed entry
Clearing the Contents of a Cell
F2 Function Key for Edit Mode
Inserting and Deleting Cells
Copying Cells
Copy and Paste
Fill Handle and Fill Command
Fill Series and AutoFill
Copying Formulas – Cell References
10
XIX. Working With Files
• Excel Worksheets files can include up to 255
•
•
individual sheets, which are like pages in the
workbook.
The default setting is 3 worksheets per
workbook but you can add worksheets
Two Kinds of sheets:
– Worksheet – is for entering information and
performing calculations. Charts can be embedded in a
worksheet
– Charts Sheets – creating charts that aren’t embedded
in a worksheet.
• Many File management functions
11
X. Functions in Formulas
• Function – predefined formula for making a
specific kind of calculation
– Easier to write formulas
– See the difference between
• =C4+C5+C6+C7+C8+C9+C10+C11
• =SUM(C4:C11)
– Anatomy of a function
• Function name determines what the function does
• Argument – determine what values or cell reference the
function should use in its calculation
• Arguments are enclosed in parentheses, and if there’s more
than one argument, separated by commas.
12
Entering Functions
• Excel offers several ways to enter a
•
function:
– Typing
– Typing and clicking
– Using the Formula Palette (fx)
Variety of Functions may be very
beneficial to the work that you are
responsible for.
– Mathematics & Trigonometric
Functions
• SUM Function and the
AutoSum Button
• PRODUCT Function
• ROUND Function – rounds a
number to the number of
decimal places you specify
ROUND(number,num_digits)
number would also be the
output of another function
Mathematical/Trignometric Functions
Format
Function
=SUM(AA1:AA51)
SUM
PRODUCT =PRODUCT(AA1:AA11)
=ROUND(B50,2)
ROUND
=EVEN(2.45)
EVEN
=ODD(3.78)
ODD
=INT(25.898351)
INT
=ABS(-12)
ABS
=SQRT(25)
SQRT
=PI()
Pi
=RAND()
RAND
RANDIANS =RADIANS(90)
DEGREES =DEGREES(45)
=SIN(12)
SIN
=COS(12)
COS
=TAN(24)
TAN
13
Functions - Samples
• Statistical Functions
•
•
•
•
•
– AVERAGE, MEDIAN,
MODE, MIN & MAX,
COUNT, STDEV AND
STDEVP
Financial Functions
– SLN, DB, SYD, DDB, PMT,
– PV, FV, IRR
Logical Functions
– IF
Lookup and Reference
Functions
– VLOOKUP and HLOOKUP
Date & Time Functions
– DATE, NOW, TODAT
Text Functions
Statistical Function
AVERAGE-Average mean of range of
arguments
=AVERAGE(E2:E5)
MEDIAN - "halfway" point of a range of
numbers
=MEDIAN(E2:E5)
MODE - Most Common Value - If there is
no repeated number #NUM! Error
=MODE(E2:E5)
MIN - Returns the minimum value
=MIN(E2:E5)
MAX - Returns the maximum value
=MAX(E2:E5)
COUNT - counts hopw many numbers are
referenced for an argument
=COUNT(E2:E5)
STDEV - statistical measurement of how
much values vary from the average or
mean for the group
=STDEV(E2:E5)
14
Sample Spreadsheet Function VLOOKUP
Logical Functions
IF Function
IF(logical_test,value_if_true,value_if_false)
Example - Passing
Example - Failing
65
45
=IF(B4>=65,"Pass","Fail")
=IF(B5>=65,"Pass","Fail")
Lookup Functions
Student
Joe Student
Jane Pupil
Lotta Grades
Final Average
50
89.2
78
=VLOOKUP(B9,$B$14:$C$18,2)
=VLOOKUP(B10,$B$14:$C$18,2)
=VLOOKUP(B11,$B$14:$C$18,2)
Average
=50
60
70
80
90
Mark
F
D
C
B
A
15
XI Formatting Worksheet Cells
• Formatting Basics
– Worksheet’s Formatting – Increases its impact to
make an impression on the people who see it
– Excel Offers a wide range of formatting options
• Number formatting
– Number, Currency, Accounting, Date, Time, Percentage,
Fraction, Scientific, text, special, custom
• Alignment
– Text alignment (Horizontal, Vertical, Indent); Orientation;
Text Controls; indent cell contents; merge and center
• Font formatting
– Font Styles, Size, Underline, Color and Effect
• Borders
• Patterns
• Columns and row formatting
16
XII. Creating Charts
• File Required: Selections.xls
• A Chart is a graphical representation of data.
–
–
–
–
May be embedded in a worksheet or saved as a separate sheet
Large variety of charts both 2 and 3 dimensional in perspective
Most charts can be customized to meet your needs
Include Charts with worksheets whenever you want to emphasize
worksheet results
– Charts can communicate information like trends and comparative
results better than numbers alone
• Worksheet and Chart links – charts are bound to the
worksheet data. If you change data in the worksheet you will
see an immediate change in the chart appearance
• Data Series and Points
– Data Series is a group of related data in a chart
– Data Series normally correspond to the values in the linked range
of cells
– When plotted on chart-each data series is assigned its own color
– Each cell within a data series is a data point
17
Excel Charts
• Chart Elements
– Each chart is made up
of multiple elements
– Any element in a chart
may be selected and
changed or altered
– Review the parts of
the chart.(See next
page)
90
80
70
60
50
40
30
20
10
0
East
West
North
1st 2nd 3rd 4th
Qtr Qtr Qtr Qtr
18
Elements of a Chart
• Chart Type
• Chart Options – refer to the inclusion and basic
formatting of chart elements including
–
–
–
–
Titles
Axes
Gridlines
Data Tables
• Formatting Chart Elements
– Includes: font, number, alignment, patterns,
placement, scale, data labels, shape, series order,
options
– Other Options – Moving Chart Elements; Exploding
Pie Charts
19
Creating PivotTable Reports
• In this section, we will expanding our knowledge
by:
–
–
–
–
–
Importing a delimited table into Excel
Setting up the data for a PivotTable
Creating a Simple PivotTable
Grouping Data
Organizing, Modifying and Creating a PivotChart
Report
• Files required:Orders.txt
20
PivotTables – Basic Concepts
• Transaction – event, such as the sale of a product or
•
payment against an account. The details of a transaction
are often entered into computer databases.
PivotTables – enable us to look at the data in a different
way
– Interactive table that you can use to quickly summarize large
amounts of data. With the ability to rotate rows and columns
you can see different summaries of the data for the specified
area iof interest.
• Pivot Charts are bound to the PivotTables. Modifying one
will modify the other.
21
Review & Summation
• Many books available to continue your
learning experience.
• Best Suggestion – Go to your School
Bookstores and pick up the textbooks they
use for classes – They provide a variety of
information and great sample exercises.
• Call me if you have any questions – 516299-2589 or email me at
[email protected]
22