microsoft partner logo color
8245  Reviews star_rate star_rate star_rate star_rate star_half

Excel Formulas and Techniques for Excel Power Users

Skills Gained Review relative and absolute referencing Review using names Create aggregated and nested aggregate functions Create the IFERROR function Create Lookups Create text functions...

Read More
Course Code OFC-116
Duration 1 day
Available Formats Classroom

Skills Gained

  • Review relative and absolute referencing
  • Review using names
  • Create aggregated and nested aggregate functions
  • Create the IFERROR function
  • Create Lookups
  • Create text functions

Prerequisites

All attendees must have prior knowledge of basic Excel formulas.

Course Details

Training Materials

All Excel training attendees receive comprehensive courseware.

Software Requirements

  • Excel 2016, 2019, 365, or later
  • Related lab files that Accelebrate will provide

Outline

  • The Foundation of Formulas
    • Exploring and reviewing relative referencing
    • Exploring and reviewing absolute referencing
    • Creating a name for an absolute reference
  • Aggregate Functions
    • Overview of using the IF, SUMIF, SUMIFS, COUNTIF functions
    • Nested IF statements vs the IFS function
    • Incorporating arrays
  • The IFERROR  Function
    • Purpose of the IFERROR  functions
    • Understanding which errors IFERROR evaluates
    • Creating the IFERROR function
  • Lookups
    • Purpose of Lookups
    • Creating V and H Lookups
    • Creating X Lookup
    • Creating X Lookups (Excel 2019, 365, or later)
    • Using arrays in Lookups
    • Incorporating COLUMN and ROW functions in Lookups
  • Text Functions
    • Purpose of CONCATENATE
    • New techniques to perform CONCATENATE functions
    • Overview of LEFT
    • Overview of RIGHT
    • Overview of MID
  • Conclusion and Bonus Demo (in Excel 365)