Excel Power User

Who should attend

This class is for users who are already proficient in formulas, functions, cell references, charts, and basic pivot tables.

Team Pass by Learnit

Excel Power User

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

Even if you're already a "power user" with a few pivot tables under your belt and a good grasp of charts and other standard Excel topics, there are a few next-level features and techniques that truly distinguish the Excel pros. With a little guidance from one of our amazing trainers, these features won't be hard for you to pick up and will significantly enhance your spreadsheets:

Lookups. In a database like MS Access you can look up data by joining tables along shared fields, like a customer ID in both the Customers and Orders table. This feature also exists in Excel: you can look up values in one worksheet and put them in another worksheet based on a related field: for example, look up the sales figure adjacent to a cell containing "January" in worksheet B, and put it into worksheet A adjacent to the cell containing "January." We'll show you how to do this and troubleshoot common problems.

Pivot Table Enhancements. Need to customize a pivot table to add extra info? Excel provides a number of features to add calculated values, and data groupings into otherwise vanilla pivot tables. To help users change the way your pivot table displays, you can add timelines and "slicers" to select only certain types of data or specific date ranges.

Charts and Advanced Formatting. The choice of chart type can make a huge difference in how easy it is to glean insights from your data. We'll show you how to choose the right charts based on your data. Have two types of data in the same chart (e.g., temperature and rainfall) with different units? You'll learn how to add secondary axes using "combo charts" and get them to display exactly as you want. Along the way you'll master some advanced conditional formatting rules.

Macros and VBA. Macros are recorded steps that can be memorized for later playback. For example, if you find yourself formatting all your tables the same way (frozen header rows, banded rows and thin gray border), you might want to record those formatting steps and then be able to reapply them to any new table. These steps are actually 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.

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: Advanced Lookups and Pivot Tables

Learning Outcomes:

1. Look up values between worksheets based on matching criteria and resolve common errors.

2. Apply logical functions to have Excel make decisions about what values to return.

3. Enhance your pivot tables with a suite of advanced features.

Topics:

Advanced Lookup and Related Useful Functions

  • XLOOKUP and VLOOKUP Functions
  • Troubleshooting with IFERROR for Secondary Lookup Table
  • Two-way Lookup with INDEX and MATCH
  • Using IF Logic to Choose Return Values
  • Core Date and String Functions: MONTH, TEXT, MID

Advanced Pivot Table Techniques

  • Report Page Filters
  • Building Outside Formulas
  • Calculated Items and Fields
  • Grouping: by Date and Manually
  • Alternate Data Views with "Show Values As" Menu
  • Timelines and Slicers

Module 2: Arrays, Advanced Charts and Macros

Learning Outcomes:

1. Create array functions for complex searches and calculations.

2. Set up, edit, and manage Excel charts

3. Create and modify a macro library for repetitive tasks in Excel

Topics:

Arrays

  • Creating Array Formulas for Single Value Output
  • Array Formulas for Multiple Value Output
  • Useful Array Functions

Advanced Conditional Formatting

  • Highlighting an Entire Row
  • Highlighting Based on Logical Tests
  • Implementing Banded Rows

Advanced Charting

  • Recommended Charts
  • Choosing Appropriate Charts for Different Data Types
  • Combo Charts
  • Wrangling Existing Charts to Display What You Want

Macros

  • Recording Macros
  • Saving Macros to your Personal Macro Workbook
  • Accessing and Editing Your Macros
  • Using the "Step Into" Feature with the VBA Editor to Debug
  • Saving a Workbook Containing VBA Script

Skills covered

No items found.

Excel Power User

Reviews

"It was fun and practical."

Jason D.
Jason D.

"This was a great course. I wish I had taken it years ago. Thank you!"

Rochelle M.
Rochelle M.