Prerequisite

This course presumes that the user has a strong knowledge of Windows concepts as well as an intermediate knowledge of Excel concepts (such as working formulas and functions and tables).

Details

Click on a date to request further information or to register.

Course Outline

There is no disputing that Microsoft Excel is a powerful tool.  This tool allows users to create spreadsheets that can be formatted to allow for sharing of critical data. Users can make informed decisions by formatting and analyzing data. From basic data entry to creating charts and pivot tables for rich data visualization, students will derive a greater appreciation for the value of the data.

Section 1: Advanced Topics

• Lesson 1.1: Setting Excel Options
  • Changing Your User Name
  • Changing your Default Font
  • Changing Your Default File Format
  • Setting Formula Options
  • Changing the Appearance of Your Excel Interface
• Lesson 1.2: Excel and Windows
  • Searching for Documents
  • Using Office Diagnostics
  • Checking for Updates
  • Starting Excel Automatically when Windows Boots
• Lesson 1.3: Using the Excel Viewer
  • What is the Excel Viewer?
  • Downloading the Excel Viewer
  • Using Excel Viewer

Section 2: Getting the Most From Your Data

• Lesson 2.1: Getting the Most from Your Data
  • Using Automatic Outlining
  • Displaying and Collapsing Levels
  • Grouping Data Manually
  • Creating Subtotals
• Lesson 2.2: PivotTables
  • What is a PivotTable?
  • Creating a PivotTable
  • Creating a PivotTable Frame
  • Specifying PivotTable Data
  • Rearranging PivotTable Data
• Lesson 2.3: More About PivotTables
  • Modifying PivotTable Calculations
  • Formatting a PivotTable
  • Refreshing a PivotTable
  • Charting a PivotTable
  • Creating a PivotTable Based on External Data
• Lesson 2.4: Exploring Scenarios
  • What is a Scenario?
  • Creating a Scenario
  • Creating a Scenario Summary Report
  • Saving Multiple Scenarios
• Lesson 2.5: What If Analysis
  • Using a One and Two Input Data Table
  • Using Goal Seek
  • Using Solver

Section 3: Excel and the Internet

• Lesson 3.1: Excel and Hyperlinks
  • What is a Hyperlink?
  • Inserting Hyperlinks
  • Modifying Hyperlinks
  • Using Hyperlinks in Excel
• Lesson 3.2: Saving Workbooks as Web Pages
  • HTM and MHTML Files
  • Saving a Workbook as a Web Page
  • Saving a Worksheet as a Web Page
  • Saving a Chart as a Web Page
  • Saving a Range as a Web Page
• Lesson 3.3: Getting Data from the Internet
  • Importing from an External Data Source
  • Creating a Web Query
  • Saving a Web Query
  • Refreshing a Web Query
• Lesson 3.4: Excel and Office Servers
  • Publishing to a Document Server
  • Saving to a Document Management Server
  • Using Excel with Excel Services

Section 4: Advanced Excel Tasks

• Lesson 4.1: Creating and Using Shared Workbooks
  • Sharing a Workbook
  • Opening and Editing a Shared Workbook
  • Tracking Changes
  • Resolving Conflicts in a Shared Workbook
• Lesson 4.2: Using Custom AutoFill Lists
  • What is an AutoFill List?
  • Creating a Custom AutoFill List
  • Modifying a Custom AutoFill List
  • Using a Custom AutoFill List
• Lesson 4.3: Using the VLOOKUP Function
  • Using VLOOKUP to Find Data
  • How to Find an Exact Match with VLOOKUP
  • Finding the Closest Match with VLOOKUP
• Lesson 4.4: Linking, Consolidating, and Combining Workbooks
  • Linking Workbooks
  • Consolidating Workbooks
  • Combining Worksheets