Microsoft Excel Advanced

Description

Boost your Microsoft Excel knowledge base with this advanced course, covering key functions like IF, lookup, text, and date functions, along with dynamic arrays. Learn to analyze data using tables, PivotTables, and data analysis tools such as Goal Seek and Solver. Gain expertise in importing/exporting data, working with macros, advanced charting and automating tasks. Perfect for professionals looking to streamline workflows and improve data management and analysis skills. This is a hands-on session includes practice exercises to follow along with the instructor.

Who Should Attend

Professionals looking to streamline workflows and improve data management and analysis skills.

Learning Objectives

Students will gain Advanced Microsoft Excel skills, including:

• Working with Advanced Functions
• Working with Data
• Analyzing Data
• Working with Complex Charts
• Creating and Working with PivotTables
• Enhancing PivotTables
• Working with Macros
• Working with Data Analysis Tools

Course Outline

Working with Advanced Functions
• Functions Overview
• Using the IF Function
• Nesting Functions
• Lookup Functions
• Text Functions
• Date Functions
• Dynamic Arrays
• Running Totals

Working with Data
• Importing Data and Exporting Data
• Converting Text to Columns
• Using Flash Fill
• Connecting to External Source Data
• Working with Hypertext Links

Analyzing Data
• Review Tables – Sorting & Filtering
• Using Automatic Outlining
• Inserting Subtotals
• Creating an Advanced Filter
• Using Database Functions

Working with Complex Charts
• Add a Trendline
• Create a Combo Chart
• Add a Secondary Axis

Creating and Working with PivotTables
• Working with Recommended PivotTables
• Creating a PivotTable using Worksheet Data
• Laying out a PivotTable on a Worksheet
• Modifying PivotTable Fields
• Using a Report Filter
• Refreshing & Formatting a PivotTable

Enhancing PivotTables
• Working with Summary Functions
• Sorting Items in a PivotTable
• Creating a Slicer
• Grouping Data
• Using a Timeline
• Applying Label and Value Filters
• Creating a Calculated Field
• Creating Charts from PivotTables

Working with Macros
• Recording a Macro
• Saving a Macro-Enabled Workbook
• Executing a Macro
• Creating a Macro Button
• Working with Macro Buttons

Working with Data Analysis Tools
• Using Goal Seek
• Using Two-Input Data Tables
• Creating & Working with Scenarios
• Understanding Solver
• Defining a Problem
• Solving a Problem
• Generating a Report of Results

Prerequisites

Excel Intermediate or equivalent skills. Must be able to work with multiple worksheets, use named ranges, and work with functions.

Instructors

Dawn Monroe