Microsoft Excel Power User: Mastering Complex Functions and Data Models

Duration: 1 Day

‍

Course Aims

This course is aimed at attendees who want to expand their knowledge into some of the more complex functions, build reports using form controls, work with Power Pivots and use built-in automation offered by Microsoft Excel.

Course Pre-requisites

Attendees who wish to attend this Microsoft Excel course should have attended our Excel Advanced course or be competent with the following advanced functions: IF, VLOOKUP, HLOOKUP, SUMIF and COUNTIF and have created and edited Pivot Tables.

Course Objectives

On completion of this course, delegates will be able to:

• Use Statistical, Lookup, Date, and text functions
• Working with Form Controls
• Carryout "What-If Analysis" using Scenario Manager and Goal Seek
• Get and load data from CSV/Text file
• Use advanced features of Pivot Tables
• Working with Power Pivots

‍

Course Content

‍

Introduction and Objectives

‍

Using Statistical, Lookup, Text, and Date Functions

• Using COUNTIFS, SUMIFS, AVERAGEIFS, MAXIFS, MINIFS, LARGE, SMALL, IFS and RANK
• Use INDEX, MATCH, and CHOOSE
• TEXT, TEXTJOIN, FIND, & SUBSTITUTE
• Calculate dates using, NETWORKDAYS, EMONTH, EDATE, WEEKDAY and WORKDAY
• XLOOKUP & FILTER (functions for those using Excel 2021 / Microsoft 365)

‍

Form Controls

• How to insert form controls like a Combo box, checkbox, spin button and list box to build reports
• How to use form controls with Formulas

‍

What If Analysis Tools

• Obtaining the desired result to a formula using Goal Seek and Scenario Manager
• Creating a scenario summary report

‍

Get and Transform Data

• Import Text/CSV

‍

‍Advanced features of Pivot Tables

• Apply Conditional Formatting to a Pivot table
• Creating Formulas in a Pivot Table
• Show report filter pages
• Adding Slicers and Timeline across multiple Tables
• Creating Pivot Tables using multiple worksheets

‍

‍Power Pivots

• What is Power Pivot?
• How to add Power Pivot to the ribbon tabs
• How to add Excel data to the data model
• Create a Pivot table using the related tables

‍

 Hints and Tips

‍

Download PDF Outline