MSACRAO Conference 2001 November 26
Download
Report
Transcript MSACRAO Conference 2001 November 26
MSACROA Conference 2006
November 27-30, 2006
Introduction to Microsoft Excel for
College Admissions and Registration
Officers
Presenter: Chester Barkan
Registrar, Long Island University,
C.W. Post Campus
Workshop Agenda
I. Review the applications of Excel in the
fields of Admissions and Registration
Sample Applications
II. Introduction to Excel – Basic Spreadsheet
Skills
Structure of the Spreadsheet
Migrating Around the Spreadsheet
Setting up the Basic Spreadsheet
Labels, Values and Formulas and Functions
III. Basic Editing Skills
Workshop Agenda
IV. Using Functions in Formulas
Using the Function Wizard
Understanding the Order of Operations in the
calculation of formulas
V. Formatting Worksheet Cells
VI. Creating Charts
Determining the type of chart to use
Creating and Formatting the Chart
Separate Sheet or Embedded Chart
Workshop Agenda
VII. Working with Graphical Objects
VIII. Advanced Features
Naming Cell Ranges
3-D Cell Referencing
Consolidation
Macros
Scenario Manager
Goal Seeker
XIX. Printing
I. Applications of Spreadsheet
Analysis in College Admissions
and Registration
Enrollment Trends Analysis
Admissions Reporting
Assist in the Processing of State
and Federal Reports – IPEDS
Enrollment Projection Reports
Comparison Data – Looking at
several semesters or years to
determine trends
Charting Progress Reports
Participants may contribute other
examples of spreadsheet
II. Introduction to
Excel-Basic
Spreadsheet
Skills
Excel is most commonly used to create
worksheets
Collection of information laid out in columns and
rows
Two Kinds of Inputs
Value – piece of information that does not change. Text,
numbers, dates and times are examples
Formula – collection of values, operators, and predefined
functions. Excel evaluates them to produce a result.
Recalculation and its Benefit will be explain
Most often used to organize and calculate numerical
and financial information
II. Introduction…
Workbooks and Worksheets or Spreadsheets
Documents created using Excel are referred to was
Workbooks (2 types)
Blank – Created from Scratch
Template – which contains values, formulas, formatting,
custom toolbars
Cells, Cells and More Cells
Worksheet Information is entered into cells. The
intersection of the column and row
Each Cell has a unique address referred by the
column letter and row number
The cell must be active in order to enter labels,
numeric data or formulas.
II. Introduction…
Values and Formulas – Entered into the active
cell
Formula Bar – Active when you are entering data
into a cell
Anything you type or click on may be included in the active
cell
Many excel options and menu commands are unavailable
Values – Constant – They don’t change unless you
change them
Formula Basics – Excel makes calculations based
on formulas you enter into cells
III. Basic Editing Features
Excel offers a number of features and
techniques that you can use to modify your
worksheet
We will review several basic editing techniques
Proper editing is very important for the overall
appearance of the Worksheet
Cell References and Formulas
Two Primary Types of Cell References
Relative Cell reference is the address of a cell relative to
the cell the reference is in
Absolute cell references – exact location of a cell ($)
Mixed Cell References – offer a 3rd option – either the
column or row reference is absolute while the other
reference remains relative
III. Editing Worksheets
Excel workbook files consist of multiple sheets
Workbook files appear in document windows
Workbooks files can be saved on disk and
reopened for editing and printing.
In Excel there two kinds of sheets
Worksheet is for entering information and
performing calculations
A chart sheet is for creating charts that aren’t
embedded in a worksheet
Use the multiple sheet capabilities of workbook files
to keep sheets for the same project together
IV. Functions and
Formulas
A function is a predefined formula for
making a specific kind of calculation
Anatomy of a Function
The function name determines what the
function does
The argument determines what values or cell
references the function should use in its
calculation
IV. Formulas and
Functions…
Excel offers several ways to enter a function:
1. Typing
2. Typing and Clicking
3. Using the Formula Palette
Always remember to begin a formula with the =
sign. If you fail to include the equal sign, it will
paste the formula as a label (text)
Function Palette or Wizard – is a wonderful
tool for learning the different functions that are
there and the setup.
Functions – Most Common and
the Not So Common
SUM Function – adds up
numbers. Also remember
the AutoSum Feature is
available
PRODUCT Function –
multiples the arguments
ROUND function –
rounds a number to a
number of decimal
places
AVERAGE Function –
calculates the average or
means of its argument
MODE Functions – most
common value
MIN & MAX Functions
– Minimum and
Maximum value of its
arguments
COUNT Function – how
many numbers are
referenced by its
arguments
STDEV or STDEVP
Function –calculates the
standard deviation based
on random sampling of
the entire population
IV. Functions
Continued….
IF Function –
evaluates a condition
and returns one of
two different values
depending on
whether the
condition is met
(true) or not met
(false)
LOOKUP & Reference
Functions
VLOOKUP and
HLOOKUP Functions
return information based
on data stored in a
lookup table
IS Function – returns
information about other
cells. You can see this
applied in one of the
same spreadsheets that
will be on you disk.
IV. Functions….
Date and Time
Functions
Excel treats dates
and times as serial
numbers. A date is
the number of days
since January 1,
1900
Excel is Year 2000ready.
DATE Function –
returns the serial
number for a date
Syntax: DATE(year,month,day)
Calculating the number of
days between two dates
NOW & TODAY Functions –
return a serial number for the
current date and time (NOW)
or current date (TODAY)
Syntax: DATE() and NOW()
DAY,WEEKDAY,MONTH and
YEAR Functions – return the
day of the month, day of the
week, the month number, or
the year number for a serial
number
V. Formatting
Worksheets
Formatting a Worksheet is like putting on its
makeup….
Worksheet’s content may be perfect – but applying
formatting increases its impact and makes an
impression on the people who see it.
Excel offers a wide range of formatting options
You can apply formatting to cells using a variety of
techniques: with toolbar buttons, shortcut keys,
menu commands, or the conditional formatting and
auto format features
Formatting Options
Number Formatting
– Many predefined
formatting options
Alignment – excel
offers a wide variety
of options to set the
way characters are
positioned within a
cell
Text Alignment
Horizontal
Vertical
Indent
Orientation – angle at
which text appears
within the cell
Text Controls – options
include wrap text, shrink
to fit, merge cells
All Done Through the
Format Menu
Formatting Options
Borders – Excel
offers a number of
different border
styles that you can
apply to separate
cells or selection of
cells.
Formatting Toolbar
or Format Cells
menu
Fill Colors and Patterns
Excel lets you add color
to cells either with or
without patterns
Format Cells Dialog Box
Style – lets you combine
formats into named
styles that you can apply
to any cell in the
workbook
Conditional Formatting
This feature enables you to set up special
formatting that is automatically applied
by Excel only when cell contents meet
certain criteria
Format > Conditional Formatting
Use the menu and text boxes to set up the
criteria for applying formats
You can add several formatting conditions
Formatting - Finale
Column Width and Row
Height – Columns and
Rows can be adjusted to
accommodate characters
Different Methods of
adjusting Columns and
Rows
Dragging
Format Menu
AutoFit
AutoFormat Featurequick way to dress up
tabular data in
worksheets
Cell Comments-(aka
Cell Notes)
Insert Comment
Adding Cell
Comments
View and Editing
Removing Formats –
Edit Menu’s Clear
Submenu
VI. Creating Charts
A chart is a graphical representation of data.
Can be embedded in a worksheet
Can be a chart sheet
There are many different types of charts, both 2D and 3-D. Each type of chart has at least one
variation, you can customize any chart.
No Limit to the number of ways you can present
data graphically with Excel
The Chart Wizard walks you step-by-step
through the creation of a chart
Worksheets and Chart
Links
When you create a chart based on worksheet data, you establish a
link between the two objects. – If the content of the worksheet
changes, that is reflected on the chart sheet
The next few slides will clarify several parts (features) of the chart
giving you a detailed understanding of the many parts of the chart
including:
Data Series and Points
Chart Elements
Chart Type
Axes
Gridlines
Data Labels
Data Table
Chart Features
Data Series and Points
A data series is a group of
related data in a chart
Corresponds to the values
in a linked range of cells
in a single column or row
Each cell within a data
series is called a data
point – individually
plotted on a chart
You can change data
series included in a chart
Each chart is made up of
multiple elements – each
of which can be selected,
modified, formatted.
Chart Type (Chart >
Chart Type)
Chart Options
Titles
Axes
Gridlines
Legend
Data Labels
Data Tables
VI Working With
Graphic Objects
Other than formatting, adding graphic objects
is another way to enhance the appearance of
the worksheet
Excel enables you to add graphic objects to
your worksheet and chart sheets using three
methods
1. Use tools on Excel Drawing Toolbar
2. Copy graphics created in other programs and
paste then into excel documents
3. Commands under Excel’s Insert function
Objects
Drawing Objects – Use
the drawing Toolbar to
add lines,arrows,
shapes, and text books to
your worksheet and
charts
Objects must be selected
before they can be
modified – Select by
Clicking on them
Grouping Objects –
Objects can be grouped
in order to keep them
together. You can then
select, relocate, edit,
resize, all objects that
are within one group
Text Box –A text box is
like a little Word
Processing document
within the spreadsheet
Located on the text box
button
Other Graphics
As we said you can
add graphics created
with other programs
to your excel sheets
Enables you to
include more
complex graphics.
Insert Clip Art
Insert Files
Insert Pictures
VII. Advanced Features
Excel has many advanced features that
you use to tap Excel’s real power
Name
3-D Cell Reference
Consolidations
Custom View
Macros
Scenario Manager and Goal Seek
A. Names
Difficulty with using cell
references – Sometimes
difficult to remember
References may change
if cells are inserted or
deleted
Excel lets you assign
easy to remember names
to individual cells.
Defining and Naming
a Range
Select the cells you
want to name and
select Insert > Name.
Define
Enter a range name
and press enter
B. 3-D References
3-D References let
you write formulas
that reference cells
in other worksheets
The links are live –
when a cells content
changes the results of
formulas in cell that
reference it change.
Excel offers several
ways to write
formulas with 3-D
References
Use Cell Names
\Type them IN
Click them on
Use the paste special
command
C. Consolidations
Consolidate command
lets you combine data
from multiple sources.
Excel lets you
A. Consolidate based on
the arrangement of data
B.Consolidate based on
identifying labels or
categories
With either method,
Excel can create
links to the source
information so the
consolidation
changes
automatically when
linked data changes
D. Custom View
Excel feature – lets you create multiple
views of a workbook file
A view includes the window size and
position, the active cell, the zoom
percentage, hidden columns and rows and
print settings
Once set up, you can select it from a dialog
box
E. Macro
A macro is a series of commands that Excel can
perform automatically
Macros are built with Microsoft Visual Basic
programming language
Macro Recorder will record the keystrokes, menu
choices and dialog box settings as you make them
To begin: Select Tools Macro Macro :
Record New Macro
Excel Macro Virus Protection Feature
Displays a warning message – Three Options
Enable Macro / Disable Macro / More Info
F.Data Tables,
Scenarios, Goal Seek
and
Solver
By know you are aware of the many features
available to you in Excel – the most powerful of
those features involve manipulating data to
determine “What If” situations. In this section
we will:
Create one and two variable data tables
Goal Seek – to constrain values in a what-if analysis
TREND Function to predict future sales
Multiple Scenarios
Solver to determine specified results
Forecasting with WhatIf Analysis
One Variable Data Tables
Data tables are useful when you need to set up a range of
values that you want to substitute for one or more values in a
calculation
A DATA TABLE – is a range of cells that should how changing
certain values in your formulas affect the results of the
formula.
Data Tables provide short cuts for calculating multiple versions in one
operation and a way to view and compare the results of all the different
variations together
One Variable or Two Variable Data Tables
Formulas used in a one-variable data table must refer to an input cell,
which is the cell which a list of input values from a data table is
substituted.
The formula uses the FV financial function to calculate the future value of
an investment, based on a constant contribution and interest rate for the
entire term.
Forecasting with What-If
Analysis
Two Variable Data Tables
The only difference between a one and two variable data table is
that it uses one formula with two lists of input values.
Goal Seek
Another interesting tool for what-if analysis. Excel varies the
values in one specific cell until a formula that is dependent on
that cell returns the results you want
Trend Function
TREND function will calculate a value based on a linear series,
such as revenue for a given time period.
Forecasting with WhatIf Analysis
Scenario Manager
A scenario is a set of values that Excel saves and can substitute
automatically in your worksheet.
Scenarios can be used to either forecast the outcome of a worksheet
model or to restore a worksheet to existing values.
Solver
Use: To see how constraining certain values in a workbook will affect
other values.
Solver lets you determine the maximum or minimum value of one cell
by changing other cells.
You find an optimal value in one cell called the target cell, on the
worksheet. Solver adjusts the values in the changing cells you specify
called the adjustable cells, to produce the results you specify from the
target cell formula. You can apply constraints which restrict the
values Solver can use in the model
XIV. Printing
Excel enables you to print, all the worksheet,
part of it, multiple sheets or the entire workbook
all at once.
Printing is basically a three-step process:
1. Use the Page Setup dialog box to set up your
report for printing.
2. Use the print preview feature to take look at your
report
3. Print Command to send the desired number of
pages the the printer
Questions and Answers
If you have any questions, you can email
me at [email protected]
Outline on my website at
http://myweb.cwpost.liu.edu/cbarkan