Microsoft Excel 2003

OBJECTIVES

Introduction to Excel. At the end of the course, the participant will have acquired basic knowledge on using this electronic spreadsheet.

CLIENTELE

The public

PREREQUISITES

Windows

DURATION

5 days (30 hours)

COURSE SYLLABUS

Basic notions

Electronic spreadsheet
Windows configuration
Microsoft Excel 2003
Starting Excel
Excel Window
- Title bar
- Menu bar
- Tool bar
- Formula bar
- Worksheet
- Status bar
- Task panes
Office Assistant
Exiting Excel

First steps with Excel

Cell content
- Text
- Value
Entering data
- Changing the pointer orientation
- Cancelling a non-validated entry
- Entering text
- Entering numbers
- Entering a formula
Managing workbooks
- Saving a workbook
- Using the task pane
- Deleting a workbook
Moving the pointer within the workbook
- Scrolling in a worksheet
Selecting cells
Editing data
- Modifying the contents of a cell
- Replacing the entire contents of a cell
- Deleting the contents of a cell
- Deleting the format of a cell without deleting the contents
- Deleting the contents and the format of a cell
Undoing and repeating an operation
Copying and moving
- Copying data
- Copying data using the fill handle
- Moving data
- Moving data using the mouse
Office Clipboard
- Pasting an element from the Office Clipboard
- Deleting the contents of the Office Clipboard
- Automatic completion of text entry
- Selection of a range for text entry
- Entering the same data into several cells at once
- Automatically fill in repeated text in a column
Filling in a series of items
- Filling in a series of numbers
- Filling in a series of months or periods
- Specifying the increment of a series
- Creating a customized series of items

Building a workbook

Model workbook
The structure of a worksheet
Entering a formula
- Figuring out how to create a formula
- To quickly visualize the result of a formula using the button
- Entering a formula directly in a cell
- Entering a formula by pointing to the cells
- Using the sum function
- Using column or row headings (labels)
Locating the cells that a formula refers to
Calculation priorities
Types of cell references
- Relative reference
- Absolute reference
- Mixed reference
Automatically added formulas
Controlling the display of zero (0) values
Displaying formulas instead of result
Copying the results of formulas
Repeating the contents of a cell
- Inserting and deleting rows or columns
- Inserting row or columns
- Deleting rows or columns
Comment
- Inserting a comment
- Modifying a comment
- Deleting a comment
- Displaying all comments

Formatting a workbook

Model workbook
Formatting tools
Character fonts and attributes
- Choosing a default font type and size
- Alignment
  - Left, right or centred alignment
  - Indentation
  - Centring over more than one column
  - Merging cells
  - Justified alignment
  - Displaying text on many lines in a same cell
  - Repeating the contents of a cell (Fill alignment)
  - Cell orientation
  - Vertical alignment
  - Borders
    - Applying a border
    - More border options
    - Modifying a border
    - Fill
    - Row height
      - Modifying row height
      - Standard height
    - Column width
      - Modifying column width
      - Automatically adjusting column width to its longest entry
      - Standard column width

Page layout and printing

Model workbook
Print preview
- Printing area
- Page breaks
  - Horizontal page break
  - Vertical page break
  - Deleting a page break
  - Horizontal and vertical page break
  - Previewing of all the pages on the active worksheet
  - Printing
  - Page layout
    - Paper size and orientation
    - Scaling
    - Margins and centring a worksheet on a page
      - Modifying margins
      - Centring a workbook on the page
      - Header or footer
        
        Creating a header or a footer
        
        Deleting a header or a footer
        
        Customizing a header or a footer
        
        Header or footer position
        
        Worksheet options
        
        Repeating rows or columns on all pages
        
        Other printing options

Statistical and financial functions

Model workbook
Function syntax
- Formula palette
- Statistical functions
  - Total of a set of numbers
  - Number of cells containing numbers
  - Number of cells containing data
  - Maximum value of a range
  - Minimum value of a range
  - Average of values within a range
  - Model workbook
  - Financial functions
  - Periodic payments on a loan
    - Period indicator
    - Cost of a loan
    - Future of a periodic investment
    - Future value of a single deposit

Logical and lookup functions

Model workbook
Logical function if
Comparison and logical functions operators
- Nested conditions
- Logical function and
- Logical function or
- Vlookup function
- Model workbook
  - Lookup table
  - Vertical lookup
  - Conditional lookup

Multi-sheets workbooks

Model workbook
Managing different sheets in a workbook
- Selecting sheets
- Inserting sheets
- Deleting sheets
- Scrolling through sheets
- Copying a sheet
- Moving sheets
- Assigning a descriptive name to a sheet
- Adding a colour to the sheet tabs
- Simultaneously entering data within several sheets
- Consolidating worksheets
  - Consolidating with formulas

Filtering and importing data

Model workbook
Filtering data
- Filtering one record
- Filtering many records
- Data exchange
  - Exchange data between Word and Excel
  - Exchange data between Word and Excel with liaison
  - Importing data from Access

Charts

Model workbook
Charts
- Components of a chart
- Creating a chart
- Modifying a chart
  - Moving a chart
  - Moving elements in a chart
  - Changing the size of a chart
  - Changing the size of the plot area
  - Changing the type of a chart
  - Updating the data in a chart
  - Redefining the data range
  - Chart options
    - Adding titles
    - Modifying a title
    - Gridlines
    - Modifying the legend
    - Locating the legend
    - Adding data labels to a chart
    - Formatting the elements in a chart
    - Modifying the axis scales
    - Creating a customized chart type
    - Inserting a customized chart
    - Deleting a chart

TOP