# Using advanced functions in Excel 365 (2024)

Excel 365 (2024)    |    Intermediate
• 10 videos | 48m 33s
• Includes Assessment
• CPE
Rating 4.7 of 3 users (3)

## WHAT YOU WILL LEARN

• Discover the key concepts covered in this using advanced functions in excel 365 (2024) course
Use the vlookup and hlookup functions to cross-reference data lists and check for missing values or inserting data
Find a value in a double-entry table with formulas
Use reference positions to look-up for values with formulas
Retrieve information relating to dates in the past, present, and future
• Extract date values and perform calculations using dates
Calculate periods of time with date functions
Analyze complex tables with multiple arrays to obtain a summarized result
Create random arrays of values
Sort and filter data using formulas

## IN THIS COURSE

• This video outlines the key content covered in this Using advanced functions in Excel 365 (2024) course, including cross-referencing data with formulas, retrieving information with dates, and using the SORTBY and FILTER functions in Excel 365.
• In Excel, you can use the VLOOKUP and HLOOKUP formulas to cross-reference the contents of two lists. These functions are useful to check for missing values in your lists. If a value is found, it is returned, otherwise it returns an error. In this video, explore how to cross-reference data with formulas in Excel 365. This video aligns with the MO-211 Microsoft Excel Expert Exam objective: Look up data by using the XLOOKUP(), VLOOKUP(), HLOOKUP(), MATCH(), and INDEX() functions.
• 3.  Finding a value in a double-entry table in Excel 365
With Excel 365, the XLOOKUP, XMATCH and INDEX functions help you to easily find values located on a separate worksheet. In this video, discover how to extract values from a double-entry table. This video aligns with the MO-211 Microsoft Excel Expert Exam objective: Look up data by using the XLOOKUP(), VLOOKUP(), HLOOKUP(), MATCH(), and INDEX() functions.
• 4.  Using the MATCH and OFFSET functions in Excel 365
In Excel 365, you may sometimes need to find a cell value by using its positional coordinates in the table. You can do this with the MATCH and OFFSET functions. In this video, you will explore how to use reference positions to look-up for values in Excel 365. This video aligns with the MO-211 Microsoft Excel Expert Exam objective: Look up data by using the XLOOKUP(), VLOOKUP(), HLOOKUP(), MATCH(), and INDEX() functions.
• 5.  Retrieving a date value automatically in Excel 365
Some formulas in Excel 365 can be used to automatically find a date according to specified criteria. For example, find out the last date of the previous month, the current day and time, and the equivalent date from a previous or future month. This video aligns with the MO-211 Microsoft Excel Expert Exam objective: Reference date and time by using the NOW() and TODAY() functions
• 6.  Retrieving information with dates in Excel 365
Excel 365 features date functions that can be used to return precise information relating to a particular month, day, or year. There are also functions for calculating workdays elapsed between dates. In this video, discover how to retrieve information with dates. This video aligns with the MO-211 Microsoft Excel Expert Exam objective: Calculate dates by using the WEEKDAY() and WORKDAY() functions.
• 7.  Calculating periods with formulas in Excel 365
Excel 365 provide additional date functions to calculate periods elapsed between two dates. For example, use the NETWORKDAYS.INTL function to calculate the quantity of whole days, including a custom list of holidays. To display the quantity of days, months and years between two dates with a custom text message, use the DATEDIF function.
• 8.  Creating automatic reports in Excel 365
The SUMPRODUCT formula can be used to chain together calculations using multiple data ranges and produce a summary of a data table. This is very useful if you need to use data that has already been added to a workbook and you don't want to break up complex data tables.
• 9.  Creating random arrays in Excel 365
Learn to generate random number sequences in Excel 365 with ease. In this video, learn how to use the RANDARRAY function to create arrays of random values, perfect for simulations, sampling, and data analysis tasks. This video aligns with the MO-211 Microsoft Excel Expert Exam objective: Generate numeric data by using RANDARRAY().
• 10.  Using the SORTBY and FILTER functions in Excel 365
Discover how to quickly filter and sort data in Excel 365 using the FILTER and SORTBY functions. FILTER lets you narrow down data to what's relevant, while SORTBY organizes it with ease, without altering your data. This video aligns with the MO-211 Microsoft Excel Expert Exam objectives: Sort data by using SORTBY() and Filter data by using FILTER().

## EARN A DIGITAL BADGE WHEN YOU COMPLETE THIS COURSE

Skillsoft is providing you the opportunity to earn a digital badge upon successful completion on some of our courses, which can be shared on any social network or business platform.

Digital badges are yours to keep, forever.

## YOU MIGHT ALSO LIKE

Rating 3.0 of 2 users (2)
Rating 4.5 of 2813 users (2813)
Rating 4.6 of 416 users (416)