Preloader spinner

Microsoft Excel Power User: Mastering Complex Functions and Data Models

A laptop displaying the words Microsoft Excel

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 Prerequisites

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

Public Schedule

RRP:  
£225 per delegate
Our price:  
£150 per delegate

Virtual Private Training

Contact us for pricing.

Onsite Training

Contact us for pricing.

Note

All prices exclude VAT at 20%.

VAT registration number: 450 4347 14

You may also like...

Microsoft Visio Intermediate

Enhance your skills with our Microsoft Visio Intermediate course. Learn advanced diagramming techniques and streamline your workflow efficiently.

An icon of a clock
Duration:

1 Day

Microsoft Visio Introduction

If you are interested in learning how to use Microsoft Visio to help you create visually engaging diagrams, then this course is perfect for you.

An icon of a clock
Duration:

1 Day

Copilot for Microsoft 365 for Business Users

Enhance your productivity with the Copilot for Microsoft 365 course for business users. Learn to leverage AI across Word, Excel, PowerPoint, Teams, and Outlook.

An icon of a clock
Duration:

1 day

Enquire about this course

Microsoft Excel Power User: Mastering Complex Functions and Data Models
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

Join our mailing list

Receive details on our new courses and special offers

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.