MICROSOFT EXCEL 2019 ADVANCED

COURSE DESCRIPTION

In this course, you will learn the advanced topics of Microsoft Excel such as using complex formulas, modifying worksheets, Analyzing Data using PivotTables, and automating worksheets. Expand your knowledge of the tools of MS Excel 2019.

COURSE OUTLINE

MODULE 1: DATA MANAGEMENT USING NAME MANAGER

• Applying Excel Name Manager to Manage Excel Data
• Using Name Manager with Number Functions
• Using Name Manager with Indirect to Extend formulas in Multiple tables

MODULE 2: PERFORMING CRITERIA-BASED FORMULAS USING LOGICAL FUNCTIONS

• Using IF Conditional Function
• Creating NESTED IF Function
• Applying COUNTIF, SUMIF, AVERAGEIF
• Applying COUNTIFS, SUMIFS, and AVERAGEIFS for multiple criteria-based tables
• Using Formula Auditing Tools

MODULE 3: PERFORMING EXCEL LOOKUP FUNCTIONS

• The VLOOKUP Function
• The HLOOKUP Function
• Using INDEX and MATCH Function

MODULE 4: MANAGE WORKBOOKS AND AUTOMATE REPORT FUNCTIONALITIES

• Flash Fill
• Text to Columns
• Data Validation
• Data Security Options
• Consolidate Data with Excel Subtotal Functions
• Using Advanced Filters

MODULE 5: Introduction to Visual Basic

• Macro Settings
• Macro Recording and Assignments
• Macro Execution
• Macro with Excel Functions and Features

MODULE 6: INTRODUCING EXCEL POWER QUERY

• The Power Excel Workflow, Setting up Power Tools
• The Query Editor Interface
• Options for Loading Data in Excel
• Basic Power Query Table Transformations
• Text, Number, Date Specific Tools
• Conditional Columns in Power Query
• Merging Queries and Appending Queries

MODULE 7: ANALYZING DATA USING PIVOTTABLES, SLICERS AND PIVOTCHARTS

• Describe how to merge the database with PIVOT.
• Add available field names to different data labels of Pivot Area to generate different report scenarios.
• Manipulate and redefine PIVOT data resources.
• Manage and Derive Formulas in the Pivot Area using Value Field settings and Calculated Field feature.
• Re-create PIVOT reports with dashboards using Slicer feature.

Please contact us for schedules and for booking a private class.

Inquire Now