Excel Power Functions

10 Functions Recommended by the Experts

Teacher : Barbara Evers Learner : Christina Tankersley

Learn to Use the 10 Excel Functions Recommended by the Experts
Ask any Excel expert to name their favorite Excel functions, and you’ll receive a variety of suggestions. Some you’ll recognize, others you won’t. This course examines 10 of the functions more commonly listed by the experts and removes the mystery of how and why to use them.

With over 450 functions available in Excel, odds are you aren’t getting the full power of Excel in your workbooks. This course steps you through functions that can increase your productivity and simplify your spreadsheets.

Do you work with dates? Learn how to generate anniversary dates, renewal dates, and end-of-month dates.
Do you need to locate values within a large array of data? Explore options that allow you to locate data anywhere within the database without the VLOOKUP limitation of looking in the leftmost column.

Do you create rolling averages? Learn which functions will help you calculate them.
Once you explore these functions, your spreadsheets will never be the same.

Objectives – You will be able to:

• Locate the syntax of an Excel function and explain the basics of function syntax design.
• Describe how Excel functions use True/False evaluations based on criteria.
• Write an Excel function that uses wildcards.
• Describe the syntax and write an Excel function using the following functions:
• DATEDIF
• EDATE
• EOMONTH
• CONVERT
• INDEX + MATCH
• OFFSET
• COUNTA
• SUMPRODUCT
• Create nested functions using multiple Excel functions in one cell calculation.
• Determine anniversary dates using EDATE, DATEDIF, and EOMONTH.
• Convert units using the CONVERT function (e.g., miles to kilometers).
• Find a value in a large array of data using the combination of INDEX and MATCH.
• Debug or trace through an Excel formula using the “Evaluate Formula” feature or the Excel Formula Beautifier online tool.
• Create a rolling average using the OFFSET function or OFFSET and MATCH.
• Calculate totals and subtotals using SUMPRODUCT and describe how this can be used as an alternative to pivot tables.