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