NJ NY ACRAO Conference November 1, 2002

1
NJ NY ACRAO ConferenceNovember 1, 2002

• Beginning Excel XP for College Administrators
• Session III 220 330 PM

2
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.

3
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

4
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

5
Sample - Worksheet Basics
6
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.

7
V. Opening a Excel WorksheetActivating 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 () and
  
  
• 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

8
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
  upon the values in adjacent cells.

9
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

10
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

11
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 arent
  embedded in a worksheet.
  
• Many File management functions

12
X. Functions in Formulas

• Function predefined formula for making a
  specific kind of calculation
  
• Easier to write formulas
• See the difference between
• C4C5C6C7C8C9C10C11
• SUM(C4C11)
• 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
  theres more than one argument, separated by
  commas.

13
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

14
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

15
Sample Spreadsheet Function - VLOOKUP
16
XI Formatting Worksheet Cells

• Formatting Basics
• Worksheets 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

17
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

18
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)

19
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

20
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 requiredOrders.txt

21
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.
  

22
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 516-299-2589
  or email me at chester.barkan_at_liu.edu