Microsoft Office

Excel - Advanced

Course Prerequisites

  • Required: Solid understanding of all Excel Introduction and Intermediate topics.

SECTION 1: Getting The Most From Your Data

Lesson 1.1: Outlining And Grouping Your Data

  • Using Automatic Outlining
  • Displaying And Collapsing Levels
  • Grouping Data Manually
  • Creating Subtotals
  • Practice Exercise

Lesson 1.2: Pivot Tables

  • What Is A Pivot Table?
  • Creating A Pivot Table Frame
  • Specifying Data In A Pivot Tables
  • Rearranging Pivot Table Data
  • Practice Exercise

Lesson 1.3: More On Pivot Tables

  • Modifying Pivot Table Calculations
  • Formatting A Pivot Table
  • Refreshing A Pivot Table
  • Charting A Pivot Table
  • Creating A Pivot Table Based On Data From An External Database
  • Practice Exercise

Lesson 1.4: Exploring Scenarios

  • What Is A Scenario?
  • Creating A Scenario
  • Creating A Scenario Summary Report
  • Saving Multiple Scenarios
  • Practice Exercise

Lesson 1.5: What If Analysis

  • Using A One And Two Input Data Table
  • Using Goal Seek
  • Using Solver
  • Practice Exercise

Section 1: Review Questions

back to top

SECTION 2: Excel And The Internet

Lesson 2.1: Excel And Hyperlinks

  • Hyperlinks And The Internet
  • Inserting Hyperlinks
  • Modifying Hyperlinks
  • Using The Web Toolbar
  • Browsing Hyperlinks In Excel
  • Practice Exercise

Lesson 2.2: Excel Workbooks As Web Pages

  • Interactive And Non-Interactive Web Pages
  • 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
  • Practice Exercise

Lesson 2.3: Getting Data From The Internet

  • Importing From An External Data Source
  • Creating A Web Query
  • Saving A Web Query
  • Refreshing A Web Query
  • Practice Exercise

Section 2: Review Questions

back to top

SECTION 3: Advanced Excel Tasks

Lesson 3.1: Creating And Using Shared Workbooks

  • Sharing A Workbook
  • Requesting Reviews
  • Reviewing A Workbook
  • Tracking Changes
  • Merging And Revising A Shared Workbook
  • Practice Exercise

Lesson 3.2: Visual Basic And Macros

  • Adding Code To Your Macro
  • Adding Comments To VBA Code
  • Declaring Variables
  • Prompting For User Input
  • Iteration Over A Range
  • Using If Then Else Statements
  • Practice Exercise

Lesson 3.3: Advanced File Management

  • Identifying File Properties
  • Searching For Files
  • Protecting Personal Information
  • Creating Back Up Files
  • Changing Auto-Recovery Settings
  • Practice Exercise

Lesson 3.4: Excel Security

  • Digital Signatures
  • Macro Security
  • Protecting Your Workbook With A Password
  • Practice Exercise

Lesson 3.5: Custom And Advanced Features In Excel

  • Customizing Excel By Changing Options
  • Using Custom Auto-Fill Lists
  • What Is Text To Speech?
  • Using Detect And Repair
  • Practice Exercise

Section 3: Review Questions

back to top