Udemy
    •  
    •  
    •  
    •  
    •  
    •  
    •  
    •  
Subscribe
Turn what you know into an opportunity and reach millions around the world.
Learn More
Your cart is empty.
Keep shopping
Excel - Power Functions (2024)
Rating: 5.0 out of 5(2 ratings)
14 students

Excel - Power Functions (2024)

10 Functions Recommended by the Experts
Created byBigger Brains
Last updated 10/2024
English
English

What you'll learn

  • Locate the syntax of an Excel function and explain the basics of function syntax design.
  • Explain why function criteria should produce a True result.
  • Understand and use wildcards in an Excel formula.
  • Create formula with nested functions.
  • Write a formula that combines the EDATE and DATEDIF functions to determine anniversary dates.
  • Write a formula using the EOMONTH function to calculate expiration dates or due dates.
  • Write a formula using the CONVERT function to convert data from one unit of measure to another (e.g., miles to kilometers).
  • Find a value in a large array of data without knowing which row or column to search using the INDEX and MATCH functions.
  • 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.
  • Write a formula that combines OFFSET with COUNT or COUNTA to expand a range rather than revise the formula to fit the new range.
  • Calculate totals and subtotals using SUMPRODUCT and describe how this can be used as an alternative to pivot tables.

Course content

1 section • 8 lectures • 47m total length

  • Syntax, Criteria, and Wildcards5:45

    Explore Excel function syntax and arguments, learn to insert and evaluate functions such as sumproduct and if, and master wildcards—question mark, asterisk, and tilde—for pattern matching.

  • DATEDIF4:33

    Master the DATEDIF function in Excel to calculate date differences in years, months, or days (YM, YD, MD), with caution on start/end date order and its Lotus 1-2-3 carryover.

  • EDATE and EOMONTH7:21

    Explore edate and eomonth to calculate future or past dates from a start date, returning end-of-month results and handling anniversaries, leap years, and today’s date.

  • Knowledge Check
  • CONVERT4:32

    Learn to use the convert function to switch units, from miles to kilometers or feet to meters, using unit codes, metric prefixes, and weight and mass, time, and energy conversions.

  • INDEX and MATCH7:06

    Replace vlookup with index and match to retrieve text and data across ranges, and learn exact and approximate match options with required sorting.

  • INDEX MATCH MATCH5:32

    Use index match match to look up sales by both row and column. Debug and visualize the formula with formula auditing, evaluate formula, and excelformulabeautifier for dashboard-ready results.

  • OFFSET and COUNTA6:17

    Explore calculating rolling averages with offset, counta, and match in Excel. Dynamically locate the latest data and adjust the range for three to six months.

  • SUMPRODUCT6:01

    learn how sumproduct multiplies arrays and then adds the results to compute totals like total value in inventory and total quantity sold.

  • Knowledge Check

Requirements

  • Intermediate experience with Microsoft Excel is recommended.

Description

Learn to Use the 10 Excel Functions Recommended by the Experts

Excel provides over 400 functions to perform a variety of calculations within your data. With this many functions, it’s guaranteed you’re missing out on some powerhouse formulas that can make your day easier. This course explores 10 functions the experts recommend to expedite your data analysis.


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.


  • Explain why function criteria should produce a True result.


  • Understand and use wildcards in an Excel formula.


  • Describe the syntax and write Excel formulas using the following functions:

  1. DATEDIF

  2. EDATE

  3. EOMONTH

  4. CONVERT

  5. INDEX + MATCH

  6. OFFSET+COUNT

Who this course is for:

  • Excel users who want to improve their skills.

Report abuse