Log On/Register  

855.838.5028

Excel 2016: Power User

Duration: 1 Day
Module Price: $130

Putting data to work in new and creative ways is a huge part of what makes Excel great. In this course, users will learn to take new and familiar tools and use them to create dynamic, easy-to-use workbooks. Users will learn to put Data Mining tools like VLOOKUP and INDEX/MATCH to work when processing information. Strategies for building complex charts and pivot tables will be discussed along with a crash course on building Array functions/formulas and macros. This course is the natural next step for those who feel like “There has to be a better way!”.

Module 1 

Data Mining Techniques 

  • VLOOKUP 
  • Troubleshooting Vlookup 
    • MATCH for column lookup 
    • INDEX & MATCH 
    • VLOOKUP with IFERROR for secondary lookup table 
  • IF, YEAR, MONTH, TEXT, MID 

 

Advanced Pivot Table Techniques 

  • Outside Formula Building (Get Pivot Data) 
  • Calculated Items & Fields 
  • Grouping (Dates & Manual) 
  • Show Values as (Alternate Data Views) 

 

Module 2 

Arrays 

  • Useful Array Functions 
  • Index & Match for multiple lookup values 

 

Advanced Conditional Formatting 

  • Highlight Entire Row 
  • Based on Logical Test 
  • Banded Rows 

 

Advanced Charting 

  • Recommended Charts 
  • New Charts: Having the correct data for each chart type 
  • Combo Charts 
  • Rigging up existing charts to display what you want 
  • Using Radio Buttons and Form Controls 

Macros 

  • Report Generators 
  • Creating and Using Buttons 
  • VBA Essential Code Blocks 
    • If Then statements 
    • Do Until loops

Putting data to work in new and creative ways is a huge part of what makes Excel great. In this course, users will learn to take new and familiar tools and use them to create dynamic, easy-to-use workbooks. Users will learn to put Data Mining tools like VLOOKUP and INDEX/MATCH to work when processing information. Strategies for building complex charts and pivot tables will be discussed along with a crash course on building Array functions/formulas and macros. This course is the natural next step for those who feel like “There has to be a better way!”.

Module 1 

Data Mining Techniques 

  • VLOOKUP 
  • Troubleshooting Vlookup 
    • MATCH for column lookup 
    • INDEX & MATCH 
    • VLOOKUP with IFERROR for secondary lookup table 
  • IF, YEAR, MONTH, TEXT, MID 

 

Advanced Pivot Table Techniques 

  • Outside Formula Building (Get Pivot Data) 
  • Calculated Items & Fields 
  • Grouping (Dates & Manual) 
  • Show Values as (Alternate Data Views) 

 

Module 2 

Arrays 

  • Useful Array Functions 
  • Index & Match for multiple lookup values 

 

Advanced Conditional Formatting 

  • Highlight Entire Row 
  • Based on Logical Test 
  • Banded Rows 

 

Advanced Charting 

  • Recommended Charts 
  • New Charts: Having the correct data for each chart type 
  • Combo Charts 
  • Rigging up existing charts to display what you want 
  • Using Radio Buttons and Form Controls 

Macros 

  • Report Generators 
  • Creating and Using Buttons 
  • VBA Essential Code Blocks 
    • If Then statements 
    • Do Until loops
Learn More
Please type the letters below so we know you are not a robot (upper or lower case):