Excel 2: Advanced Data Analysis

Description

This session covers advanced level Excel topics that are required to manage and
analyze data. This hands-on course is intended for anyone who uses Excel
extensively and who is already comfortable with the skills that were taught in “Excel 1: Core Data Analysis”. Assignments, handouts and examples will be
used throughout the day.

Prerequisites

Participants should take "Excel 1: Core Data Analysis" or have strong Excel skills prior to taking this course.

Time

8 hours

Learning Topics

Database Functions

Use Excel’s Database functions so that Excel can be used instead of Microsoft Access
Learn common uses for the Database functions, some of the most underutilized tools in Excel

Static and Dynamic Naming

Discuss the rationale for using Static Names when building formulas
Learn to create Dynamic Named Ranges, one of the most powerful concepts in Excel which allows users to automatically update graphs, Lookup functions and Pivot Tables

Table Tool

Learn to create a Table (previously known as a List) to manage and analyze data
Use Structured Reference functions to perform powerful calculations on a dataset

Lookup Functions

Use some of Excel’s more advanced Lookup functions, including the INDEX function, and learn helpful applications for their use

Conditional Formatting

Use advanced conditional formatting tools to enhance the caliber of analytical output

Securing Data

Review Excel’s security features, including some lesser-known techniques to protect a spreadsheet
Use Excel’s Data Validation tool to restrict a cell’s contents