Microsoft Office

Excel - Intermediate

Course Prerequisites

  • Required: Understanding of Excel Introduction topics.

SECTION 1: Working with Functions and Formulas

Lesson 1.1: Using Formulas in Excel

  • Understanding Relative and Absolute Cell References
  • The Basic Mathematical Operators
  • Using Formulas with Multiple Cell References
  • The Formula Auditing Bar
  • Fixing Formula Errors
  • Displaying and Printing Formulas
  • Practice Exercise

Lesson 1.2: Exploring Excel Functions

  • What are Functions?
  • Finding the right function
  • Some Useful and Simple functions
  • Practice Exercise

Lesson 1.3: Using Functions in Excel

  • Inserting Functions
  • Using Functions and AutoFill to Perform Difficult Calculations
  • Using the IF Function
  • Working with Nested Functions
  • Practice Exercise

Lesson 1.4: Working with Names and Ranges

  • What Are Range Names?
  • Defining and using range names
  • Selecting Nonadjacent Ranges
  • What is Auto Calculate?
  • Practice Exercise

Lesson 1.5: Working with Array Formulas

  • What are Array Formulas
  • Using Basic Array Formulas
  • Using Functions with Array Formulas
  • Using the IF function in Array Formulas
  • Practice Exercise

Section 1: Review Questions

back to top

SECTION 2: Managing Lists

Lesson 2.1: Working with Lists

  • What is a list?
  • Creating lists
  • Modifying Lists
  • What is the Total row?
  • Practice Exercise

Lesson 2.2: Working with Records and Fields

  • What are Records and fields?
  • Adding fields by inserting Columns
  • Adding Records by Inserting Rows
  • Adding Records with a Data Form
  • Finding and Deleting Records
  • Practice Exercise

Lesson 2.3: Working with Lists and Filters

  • Sorting a List
  • What is an AutoFilter?
  • Custom AutoFilters
  • Using an Advanced Filter
  • Copying Filtered Records
  • Practice Exercise

Lesson 2.4: Using Excel as a Database

  • Filtering with Wild Card Characters
  • Validating Your Data
  • What are Database functions?
  • What are Lookup functions?
  • Practice Exercise

Section 2: Review Questions

back to top

SECTION 3: Working With Macros and Controls

Lesson 3.1: Creating Macros in Excel

  • What is a Macro?
  • Creating a Macro
  • Playing a Macro
  • Assigning a Shortcut Key to Macro
  • Macros, Buttons, and toolbars
  • Practice Exercise

Lesson 3.2: Excel Form Controls

  • What is a Form Control?
  • The Form Toolbar
  • Adding a control to a Worksheet
  • Assigning a macro to a control
  • Using form controls
  • Practice Exercise

Section 3: Review Questions

SECTION 4: Workbook Management

Lesson 4.1: Using Excel in Word

  • Inserting Excel Data in Word
  • Modifying Excel Data after Insertion
  • Linking Excel Data in Word
  • Linking an Excel Chart in word
  • Practice Exercise

Lesson 4.2: Using Excel with Other Programs and Files

  • Using Outlook to Send Excel Data
  • Opening an Excel File in a Different Format
  • Importing Data from a Text file
  • Importing Data from a Database
  • Practice Exercise

Section 4: Review Questions

back to top