MOS - Using Microsoft® Office Excel® 2007

 

21 Hours

Course Objectives (Exam 77-602)

After completing this course, you will be able to:

  • understand how Microsoft Office Excel handles numerical information for analysis or tracking purposes
  • identify and work with the screen, Screen Tips, the Ribbon, and the Quick Access toolbar
  • create, edit, save, open, and close workbooks
  • select cells for further action such as formatting, deleting, copying and pasting
  • change the view mode for specific types of editing tasks
  • create and edit simple and more complex formulas
  • apply text and numeric formatting options
  • control the data flow using appropriate page breaks
  • set headers and footers for a worksheet
  • change margins, orientation, or layout for printing a worksheet
  • preview and print worksheets or workbook
  • create, edit, and format simple charts
  • use a variety of advanced functions to find and calculate information or data
  • insert, modify and format clip art images, pictures, and shapes
  • customize charts
  • audit worksheets
  • work with database functions in Excel
  • sort data
  • work with named ranges
  • use AutoFill to copy or fill data
  • find and replace specific types of data
  • consolidate or summarize data
  • group or outline data
  • convert text to columns
  • use AutoFill to set up a series of data
  • find and replace data in a worksheet
  • use Paste Special to create different types of data
  • create groups and subtotals from the data in the worksheet
  • convert text to columnar formats
  • validate data and remove duplicate rows of data
  • use pick lists
  • working with existing templates, or create your own template
  • create, modify or remove a hyperlink
  • save a worksheet or a range of cells in a HTML format
  • publish a worksheet to the Internet
  • work with shared workbooks including adding protection or tracking changes
  • changing workbook properties and using the Document Inspector appropriately
  • set up a what-if scenario with a worksheet
  • use the Goal Seeking and Solver tool
  • work with a single or two variable data table
  • create and modify PivotTables or PivotCharts

Course Prerequisites

This course is designed for students who are familiar with personal computers, using a keyboard and using a mouse. The course assumes that students have completed the Microsoft Windows Level 1 course or have equivalent Microsoft Windows knowledge and experience.
Students who wish to become proficient using the features of Microsoft Office Excel 2007 will benefit from taking this course.

Course Outline

Lesson 1: Introducing Excel

Lesson 2: Manipulating Items

  • Copying and Moving Data
  • Adjusting the Columns and Rows
  • Inserting and Deleting Rows and Columns
  • Managing Worksheets

Lesson 3: Using Formulas

  • Creating and Editing Simple Formulas
  • Using Common Functions with Cell Ranges
  • Using Absolute and Relative Cell References
  • Displaying and Printing Formulas

Lesson 4: Formatting the Worksheet

  • Formatting a Cell
  • Clearing Cell Contents and Formatting
  • Formatting Worksheets

Lesson 5: Preparing to Print

  • Changing Views
  • Previewing Worksheets
  • Adding and Previewing Page Breaks
  • Customizing the Printout
  • Adjusting the Margins
  • Printing the Worksheet

Lesson 6: Working with Charts

  • Creating a Basic Chart
  • Changing Chart Types
  • Working with Pie Charts
  • Changing the Chart Layout
  • Moving and Resizing Charts
  • Printing Charts

Lesson 7: Using Functions

  • What are Functions?
  • Using Math and Trigonometric Functions
  • Using Statistical Functions
  • Working with Financial Functions
  • Using Logical Functions
  • Using Date and Time Functions
  • Using Text Functions
  • Using Information Functions
  • Using Advanced Functions

Lesson 8: Working with Illustrations

  • Drawing Shapes
  • Using SmartArt Graphics
  • Modifying Drawing Objects

Lesson 9: Customized Charts

  • Customizing Charts
  • Using Trendlines
  • Auditing a Worksheet

Lesson 10: Customized Formatting

  • Aligning the Cell Contents
  • Using the Format Painter
  • Hiding/Unhiding Data
  •  Using Cell Styles
  • Looking at Custom Cell Formats
  • Applying Conditional Formatting

Lesson 11: Working with Tables

  • Using Tables
  • Working with Named Ranges

Lesson 12: Working with Databases

  • Working with Databases
  • Filtering Information
  • Using Database Functions
  • Sorting Data

Lesson 13: Using Data Tools

  • Using AutoFill
  • Finding and Replacing Data
  • Using Paste Special
  • Consolidating Data
  • Setting Up Automatic Subtotals
  • Grouping and Ungrouping Data
  • Converting Text to Columns

Lesson 14: Preparing Online Documents

  • Validating Data
  • Using Pick Lists
  • Removing Duplicate Rows
  • Circling Invalid Data
  • Working with Templates
  • Using Hyperlinks
  • Saving Worksheets as HTML

Lesson 15: Collaborating with Others

  • Using Comments
  • Looking at Workgroup Functions
  • Protecting Your Workbook
  • Preparing for Distribution

Lesson 16: Using Analysis Tools

  • What-If Analysis
  • Using the Goal Seeking Tool
  • Using the Solver
  • Using a Data Table
  • Using PivotTables