International Institute for Learning, Inc.
Microsoft® Project Certifications
Microsoft® Excel in Depth
Becoming Proficient in the Details of Excel®
Print Download PDF  
Virtual Classroom / On-Demand Learning
Course No.:
Duration:
Credits:
8858
Four 3-hour sessions
12 PDUs / 1.2 CEUs
 
Prerequisites:

A solid working knowledge of Excel and a desire to become more knowledgeable about its features and benefits

Course Level: Intermediate/ Advanced
 
 

About The Program
This practical course gives participants a solid background in the features of Excel, as well as a deeper understanding of the best ways to use Excel.

This is not a beginner’s course in Excel. Participants will learn some advanced features such as Pivot Tables, Charting, Tables, Sorting, Filtering, Functions and more.

The goal of this course is to give participants a deeper understanding of the intricacies of Excel and a new ability to use it efficiently to produce the solutions they’re seeking.

Who Should Attend
This course is for anyone with a basic working knowledge of Excel who wishes to enhance their understanding of the myriad features of Excel and when to use them.

What You Will Learn
You’ll learn how to:

  • Use functions like IF, AVERAGE, SUMIF, SUMIFS, SUMPRODUCT, INDEX, MATCH, CHOOSE, DATE and many more
  • Create meaningful charts
  • Use Pivot Tables
  • Use Conditional Formatting and Data Validation
  • Take advantage of using Named ranges, do sorting, filtering, tables

Course Overview
Getting Started

  • Introductions
  • Course structure
  • Course goals and objectives

Excel Overview

  • Excel Functions – VLOOKUP, IF, INDEX, MATCH, CHOOSE, SUMIF, SUMIFS, SUMPRODUCT, DATE, so many more
  • Using Named ranges, Data Validation, Sorting, Filtering
  • Learn the Name manager
  • Learn the difference between local and global names
  • Ensure correct access to linked workbooks
  • Ensure data accuracy
  • Learn how to use cascading data validation (for example, once a user chooses “cars”, another cell allows only Ford, Toyota, etc.)
  • Sorting by more than 3 fields
  • Filtering by font, pattern or value
  • See your data based on things like dates in the third quarter, or fields where quantity x price is greater than some fixed value

Conditional Formatting and Data Formatting

  • Make relevant data stand out
  • Isolate highs and lows
  • Make error cells invisible
  • Learn about Data bars and Icon sets

Color Scales

  • Highlight differences between worksheets
  • Learn all the codes in the Format/Number list, like (#,##0.00_);(#,##0.00)
  • Using cell styles
  • Learn all about alignment, protection, borders, colors and more

Pivot Tables

  • How to summarize large amounts of data with a few clicks
  • Learn various ways of presenting the data
  • Pivot table tools
  • How to customize and format a pivot table to suit your needs
  • How to group data
  • Using the various options to present your data

Charts

  • Learn how to create a chart with one keystroke!
  • The 11 types of built-in charts
  • How to customize the charts to make the information tell the right story
  • Using the design, layout and formatting available to charts in Excel
  • Embedded charts versus chart sheets
  • How to modify all the pieces of a chart

Array formulas

  • Learn how to use the most powerful formulas in Excel
  • How to combine many formulas into one compact array-formula
  • Learn the real power of these formulas and why regular formulas can’t do the job
  • Using built-in array formulas, like TREND or TRANSPOSE

Summary

  • What did we learn, and how can we implement this in our work environments?
 
 
©2000-2019 International Institute for Learning, Inc.