Microsoft Excel Power User: Mastering Complex Functions and Data Models
Course Schedule
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
Public Schedule
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...
Enhance your skills with our Microsoft Visio Intermediate course. Learn advanced diagramming techniques and streamline your workflow efficiently.
1 Day
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.
1 Day
Enhance your productivity with the Copilot for Microsoft 365 course for business users. Learn to leverage AI across Word, Excel, PowerPoint, Teams, and Outlook.
1 day