Excel Advanced

Who should attend

This class is for users familiar with primary Excel features but who need more powerful features like lookups, error-handling, macros and scenario analysis.

Team Pass by Learnit

Excel Advanced

From $0
Two, 2-hour Modules (9am - 11:30am PT)

After you’ve mastered formatting tables of data, creating formulas between cells, using built-in functions, and creating charts, there is a standard set of more powerful features you’ll eventually need. Whether you’re looking at someone else’s spreadsheet and wondering “how did they do that?” or simply lacking enough firepower to get your own spreadsheets to do what you want, this class will probably solve that problem.

Database Features. You may be thinking “I use spreadsheets, not databases!” — but there are a few indispensable Excel features that mirror the most useful of features from the world of databases: lookups and aggregate functions. A “lookup” in Excel is when your formula looks up values in a secondary worksheet (just like a database!) and puts them in the primary worksheet: for example, looking up in worksheet B the inventory amount adjacent to a cell containing a particular SKU, and putting it in worksheet A, adjacent to the cell containing that same SKU. Likewise, the SUMIF(), COUNTIF(), and similar Excel functions are counterparts of “aggregate data” calculations in databases – useful, for example, if you wanted to sum up the order values shipped to a particular zip code.

Errors. You’ve probably encountered your fair share of errors that Excel has vociferously pointed out with: “#N/A” and “#NUM!” It turns out there’s an easy way to deal with these errors, either by intercepting them and providing a backup value, or by auditing and fixing them once and for all. We’ll show you the two surefire methods of handling these errors.

Finding the Right Value(s). Sometimes you need to measure variations in a single cell value, like product price, to optimize the value of another cell, like profits. Other times, you need to optimize multipleinterconnectedcells, like price, volume, and inventory – to find the optimal value for profits. These two what-if scenarios are handled by the “Goal Seek” and “Solver” functions in Excel. Get ready to impress your boss.

Macros. These are recorded steps that can be memorized for later playback. For example, if you find yourself splitting text into columns along a delimiter and then conditionally formatting certain rows, you might want to record those steps and then instantly reapply them to a new table. These steps are saved in Excel's internal programming language, VBA (Visual Basic for Applications). Once you've recorded your steps, you can dive into the behind-the-scenes VBA code that activates those steps and customize them even further.

Pro Techniques. On your way to becoming an Excel pro, you’ll need a toolkit of useful standard tips, tricks, and techniques. Among these, you’ll probably find that functions such as LEFT(), MID(), and Excel’s Text-To-Columns and Consolidating Data features are the ones you’ll use the most often. We’ll show you why those are so popular and how to use them.

Course ID
EXCELApYfL
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 5: Advanced Functions

Learning Outcomes:

  1. Use Database Functions: XLOOKUP(), SUMIF(), and AVERAGEIF()
  2. Fix errors using auditing, or handle inevitable errors with backup values
  3. Apply data cleanup and isolation/consolidation techniques.

Topics:

  • Creating and Applying Names In A Worksheet
  • VLOOKUP Function
  • XLOOKUP Function
  • ROUND function
  • Using the Formula Auditing Tools
  • Using the IF Function and Nesting Functions
  • Using the IFERROR Function
  • Summary Based Functions: SUMIF(S), COUNTIF(S), AVERAGEIF(S)
  • Text Functions: LEFT, RIGHT & MID
  • CONCAT function and Text-To-Columns Feature

Module 6: Using the "What If" Analysis Toolpack & Implementing Macros

Learning Outcomes:

  1. Find optimal values for a single cell or a set of inter-related cells. 
  2. Create and run basic macros to automate repetitive tasks.
  3. Add macros to the Excel ribbon

Topics:

  • Using the Data Consolidation Tool 
  • Using the Goal Seek Tool
  • Using the Solver Tool
  • Using the Scenario Manager
  • Recording and Running Macros
  • Understanding the Macro/VBA Developer Interface
  • Editing Macros with the Visual Basic Editor
  • Adding Buttons to Run Macros From the Ribbon

Skills covered

No items found.

Excel Advanced

Reviews

“Josh was an amazing instructor! He did an awesome job. The course was very well paced, informative and he was overall super helpful!”

Susan M.
Susan M.

“It was one of the most engaging courses I've taken. Faz was really engaging and made the class a lot of fun. Thanks!”

Cindy B.
Cindy B.

“Good pace and engaging instructor, very knowledgeable.”

Samantha P.
Samantha P.

“David made the course and material easy to follow and had many helpful shortcuts and tips.”

Shanna B.
Shanna B.

"A lot of good stuff to learn!"

Mariana I.
Mariana I.