# Excel for Power Users

Canonical URL: <https://training-nyc.com/courses/excel-for-power-users>

## Overview

Learn to solve challenging problems in Excel by applying and nesting various functions. You'll get an introduction to VBA, Excel's programming language, to automate workflow and repetitive tasks. Through various exercises and projects, you will reinforce and build upon knowledge gained in intermediate-level courses. 

### Who Should Attend This Course?

This course is perfect for those who feel comfortable with topics covered in our [Advanced Excel for Business](/courses/excel-for-business-level-iii-advanced) course and want to elevate their skills and tackle difficult exercises in Excel. As opposed to standard courses where the desired function or formula is known beforehand, in this courses attendees (with instruction and guidance from an Excel expert) will have to devise the proper set of functions needed to solve the exercise. 

**Course Objective:** The goal of this course is not to simply provide attendees with complicated formulas for future reference, but to teach users how Excel's functions can be used to solve complex problems. 

### Related Courses

Want to become a Power User but don't meet the prerequisites? Our [Excel for Business Bootcamp](/courses/excel-for-business-series-bundle) is a 3-day program that will prepare you for the course. 

Looking to learn VLOOKUP and Pivot Tables? Master these functions and tools in [Intermediate Excel for Business](/courses/excel-for-business-level-ii-intermediate). 

Do you work in investment banking or a financial firm and have a strong foundation in Excel? Our [Advanced Excel for Financial Modeling course](/courses/excel-for-finance) will show you the tricks and techniques you need to become a top financial analyst.

## Prerequisites

Attendees must have Excel proficiency equivalent to our [Advanced Excel](/courses/advanced-excel) course, including nested functions, INDEX-MATCH, and recording macros.

## Curriculum

### Advanced Summation Techniques

#### Running Total

#### Dynamic Running Total with SUM and OFFSET

#### SUM and INDEX

#### Sum Through N Items with SUM and OFFSET

### Advanced Lookup Techniques

#### VLOOKUP with Two Conditions

#### VLOOKUP with Multiple Rates

#### INDEX-MATCH with Cell Anchoring

#### VLOOKUP and INDIRECT to Pull from Two Tables

#### Partial Match VLOOKUP

### Advanced Text Functions

#### Review UPPER, LEN, FIND, SUBSTITUTE, RIGHT/LEFT, MID Functions

#### Capitalize the First Letter with LEFT, UPPER, LEN, MID

#### Find the Name in an Email Address

#### Find the First Word in String of Text

### Advanced Conditional Formatting

#### Review: Highlight the Entire Row with Formulas and Cell Locking

#### Highlighting Duplicate Cells in Two Ranges with and and COUNTIF

#### Dynamically Highlight Bottom N Values with SMALL

#### Highlight Every Other Row in a Table

### Other Advanced Techniques and Functions

#### Create Submenus with INDIRECT and OFFSET

#### CHOOSE Function to Select Scenarios

#### Dynamic Named Ranges

#### Dynamic Charting with Waterfall Chart

#### SUMIFS with Advanced Conditions

### Arrays

#### Single-cell Array Formulas

#### Advantages and Disadvantages of Arrays

#### TRANSPOSE Function

#### MAX and MIN Functions with Arrays

#### INDEX-MATCH with Arrays to Retrieve the Entire Row

### Statistics

#### Solver

#### Data Analysis ToolPak

### Creating Macros Through Fundamental VBA

#### Finding the Name of the Current Worksheet

#### Finding the Last Row Used

#### Finding the Last Column Used

#### VBA to Create a Clustered Column Chart

#### Creating a Pivot Table Using Our Captured Last Row and Column

## Pricing

**Tuition:** $299