Advanced Excel

4200 Montrose, Suite 590  Houston, TX 77006

(713) 522-9656 Phone  (713) 522-9661 Fax

Advanced Excel

What You Will Learn

Participants in the program learn how to use the advanced features of Microsoft Excel. The learning includes how to sort and manage data in lists, filter and query data, use outlines on worksheets, lookup and database functions, and PivotTables. The class exercises will involve use of data tables and scenarios to make projections about data and use Goal Seek, Solver, and the Analysis ToolPak to analyze data. Students will also learn how to audit worksheets, protect worksheets and workbooks, create and use macros and templates, and import export information and documents. Other topics include use of workgroup features, working with custom views and Report Manager, and creating and modifying data maps.

What Others Have Learned

  1. How to analyze worksheets using the go to command, Goal Seek, and scenario manager
  2. How to create and use PivotTables and use them to display and chart data
  3. How to use data tables, the What-If function, and scenarios to make projections about data
  4. How to use the Goal Seek, Solver, and the Analysis ToolPak to analyze data
  5. How to use look up and database functions
  6. How to filter and query data
  7. How to make forecasts using data in Excel
  8. How to work with macros and templates
  9. How to import and export information from other programs
  10. How to use Excel in a workgroup

Who Should Attend

Advanced users who are using Excel to perform large amounts of data analysis, preparing, modifying and analyzing budgets, financial statements, and other reports containing complex numeric formulas. It will help Excel users that are managing and summarizing data lists.

WJ Murray & Associates

Advanced Excel

Course outline

Day one8:00AM to 12:00 Noon

  1. Introduction

Overview of advanced applications for Excel

Business examples of advanced Excel application

The functions of the tables, macros, and Goal Seek, Solver, and the Analysis ToolPak

  1. Working with data lists

Sorting data

Working with data forms

Using data validation

  1. Filtering data

Using AutoFilter

Creating a custom AutoFilter

Setting up criteria ranges

  1. Using database and look up functions

Looking up values in a table

Working with database functions

-Counting the occurrence of a value

-Finding the average of a specific field

  1. Working with PivotTables

Creating PivotTables

Editing PivotTables

Modifying PivotTables

Lunch12:00 to 1:00 PM
  1. Making projections about data1:00 to 5:00 PM

Solving What If problems

Creating and managing scenarios

  1. Analyzing data

Working with the Goal Seek function

Working with Solver

Using the Analysis ToolPak

  1. Working with macros

Running, recording, and assigning macros

Editing, renaming, and deleting macros

Selecting and formatting cells in macros

Creating macros to analyze and present data

Creating macros to automate common tasks

  1. Creating custom tool bars and menus

Day 28:00 AM to 12:00 Noon

  1. Importing and exporting information
  1. Pasting information from other programs
  2. Inserting objects
  3. Using Excel in a workgroup

Sending a document to other users

Working with cell comments

Sharing workbooks

  1. Using data maps

Creating a map

Changing the format of a map

Changing the data displayed in the map

Enhancing maps

Adjourn12:00 Noon

WJ Murray & Associates