Excel Power User

Who should attend

This live online Excel Power User course is for advanced Excel users, financial analysts, accounting professionals, data analysts, and anyone interested in taking Excel to the next level!

Team Pass by Learnit

Excel Power User

From $0
Two, 2-hour Modules (9am - 11:30am PT)
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!”
Course ID
EXCELPfNnO
Available times
June 17 & June 18, 2021
9:00am-11:30am
June 17 & June 18, 2021
9:00am-11:30am
June 17 & June 18, 2021
9:00am-11:30am
June 17 & June 18, 2021
9:00am-11:30am
June 17, 2021
9:00am-11:30am
June 17, 2021
9:00am-11:30am
June 17, 2021
9:00am-11:30am
June 17, 2021
9:00am-11:30am
June 17, 2021
9:00am-11:30am

Course Outline

Module 1: Excels Advanced Database Functions & Advanced PivotTable Techniques

Learning Outcomes

1. Gain a deep understanding of advanced database functions and common mistakes and errors to lookout for.

2. Use Index match instead of VLOOKUP for value lookups.

3. Understand some of Excels PivotTables most used features

Advanced Database Functions

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

Advanced Pivot Table Techniques

  • PivotTable Report Page Filters
  • Building Outside Formulas
  • Calculated Items & Fields?
  • Grouping (Dates & Manual)?
  • Show Values as (Alternate Data Views)
  • Timelines and Slicers

Module 2: Arrays, Advanced Charting & Macro editing

Learning Outcomes:

1. Learn about excels array functions for complex searches and calculations.

2. Setup, edit and manage excel charts

3. Learn how to Create, updated and maintain a macro library for repetitive tasks in Excel

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??

Macros?

  • Record a Macro
  • Saving Macros to your Personal Macro workbook
  • Accessing and editing your macros
  • Using the Step into with VB editor to debug and edit
  • Saving a workbook with a VB script.

Skills covered

No items found.