How to Master Excel’s ‘Power’ Functions(VRTIME219)

Presented by: Mike Thomas
Duration: 240 Minutes

Download Conference Material(s):

Overview

Take Your Business Intelligence, Data Analysis & Reporting to the Next Level

Microsoft Excel is a useful tool when it comes to compiling and analyzing large quantities of data. And there is so much more to Excel than simple spreadsheets and reports.

If you’ve ever wanted to break through to some of Excel’s more sophisticated data analysis tools, this is the training course for you. Join Mike Thomas for a comprehensive set of training sessions in which you’ll learn to use the full scope of Power Pivot (a free add-in that puts the “power” into Pivot Tables), Power Query, Lookup functions, and more.

Thomas will teach you to create datasets as large as you like, import clean data quickly and efficiently, combine data from multiple (including external) sources, compare list items, perform powerful calculations, and construct Lookup functions to improve the accuracy and integrity of your workbooks.  (Hint: No more data entry errors—and no more hours spent cleaning data!)

You’ll walk away with a greater understanding of what Excel has to offer—and the confidence to begin using these Power functions in your daily work.

In three 60-to-90-minute sessions, this training program will teach you how to:

  • Import data into Excel from text files, webpages, and databases
  • Import data into Power Pivot—and why you should
  • Use the Data Model to create/manage relationships
  • Create powerful calculated fields in a Pivot Table
  • Use the VLOOKUP and HLOOKUP functions
  • Use MATCH and COUNTIF to compare two lists
 
Session Details
Session-1: Power Pivot: Take Pivot Tables to the Next Level
Duration: 90 minutes

Topics covered:

  • The benefits of using the Data Model
  • Creating Pivot Tables from multiple Excel-based lists without using VLOOKUP
  • Creating Pivot Tables from large datasets (without worrying about file size or performance)
  • Creating Pivot Tables from related data sources (including external sources)
  • Creating powerful calculated fields in a Pivot Table
Session-2: Power Query: How to Import & Clean Data Quickly
Duration: 90 minutes

Topics covered:

  • Importing data into Excel from text files, webpages, and databases
  • How to avoid the “million-rows” limitation by connecting to external sources
  • Using Query Editor to create calculated columns in Pivot Tables
  • Using Query Editor to clean and transform data.
Session-3: Master Lookup Functions
Duration: 60 minutes

Topics covered:

  • How to construct and use the VLOOKUP function
  • TRUE and FALSE: the 4th parameter demystified
  • Tables: the key to future-proofing VLOOKUP
  • Using MATCH and COUNTIF functions to compare two lists
  • INDEX-MATCH or VLOOKUP
  • How to construct and use the HLOOKUP function
  • The LOOKUP Function
  • How to avoid #NA
Who Will Benefit

This webinar is designed for users of Excel (2010 and above for Windows) who have at least intermediate knowledge of the application. You should be familiar with the basics of using formulas and creating Pivot Tables.

Note: Power Pivot and Power Query are not available for all versions of Excel. If you are unsure whether this training is relevant for your Excel version, please check with your IT department.

Register Now
 
 
About Our Speaker(s)
Mike Thomas

Mike has worked in the IT training business since 1989 where his expertise and experience covers designing, delivering training courses, writing training materials and recording and editing video-based tutorials. Although he is a subject matter expert in a range of Microsoft technologies and has worked extensively with Mac OSX and Mac-related software since 2006; however, his passion is for all-things-Excel and in 2012 he founded the website theexceltrainer.co.uk

Mike is a Fellow of The Learning and Performance Institute and has worked with and for a large number of global and UK-based companies and organizations across a diverse range of sectors. In addition to training, he designs and develops Microsoft Office-based solutions that automate key business tasks and processes.

More Info