Using advanced functions in Excel 365 (2024)

Excel 365 (2024)    |    Intermediate
  • 10 videos | 48m 33s
  • Includes Assessment
  • Earns a Badge
  • Certification CPE
Rating 4.7 of 3 users Rating 4.7 of 3 users (3)
Excel 365 offers a variety of advanced functions for data management and formula creation. In this course, discover how to build formulas to find information in tables, work with data arrays, and obtain date-related information. First, enhance your data management and analysis skills with powerful lookup functions such as VLOOKUP, HLOOKUP, and the newly introduced XLOOKUP. These tools are crucial for cross-referencing data across different lists, identifying and filling in missing information, and precisely inserting relevant data. Next, learn techniques to retrieve, manipulate, and analyze information based on dates. Deal effectively with historical records, current data, or future projections by mastering date-related functions such as NOW, TODAY, WEEKDAY, and WORKDAY in Excel 365. Additionally, learn to summarize complex tables containing multiple arrays, equipping you with the analytical skills to extract meaningful insights from extensive datasets. For instance, use the RANDARRAY function to create random arrays for simulation or sampling purposes, enhancing your modeling and analytical capabilities for various scenarios. Finally, refine your ability to organize and clarify your data with advanced sorting and filtering techniques using formulas, ensuring that you can present your findings clearly and impactfully. This course aligns with the objectives of Exam MO-211: Microsoft Excel Expert (Microsoft 365 Apps). Would you like hands-on practice? Go to the Course Contents pane and download the sample files to follow along with select videos.

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

  • 58s
    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. FREE ACCESS
  • 5m 10s
    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. FREE ACCESS
  • Locked
    3.  Finding a value in a double-entry table in Excel 365
    5m 22s
    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. FREE ACCESS
  • Locked
    4.  Using the MATCH and OFFSET functions in Excel 365
    5m 22s
    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. FREE ACCESS
  • Locked
    5.  Retrieving a date value automatically in Excel 365
    5m 7s
    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 FREE ACCESS
  • Locked
    6.  Retrieving information with dates in Excel 365
    5m 40s
    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. FREE ACCESS
  • Locked
    7.  Calculating periods with formulas in Excel 365
    5m 34s
    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. FREE ACCESS
  • Locked
    8.  Creating automatic reports in Excel 365
    4m 59s
    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. FREE ACCESS
  • Locked
    9.  Creating random arrays in Excel 365
    4m 52s
    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(). FREE ACCESS
  • Locked
    10.  Using the SORTBY and FILTER functions in Excel 365
    5m 28s
    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(). FREE ACCESS

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

Course Managing data in Excel 365 (2024)
Rating 4.0 of 6 users Rating 4.0 of 6 users (6)
Course Exploring essential functions in Excel 365 (2023)
Rating 4.9 of 7 users Rating 4.9 of 7 users (7)
Course Finding and analyzing data with formulas in Excel 365
Rating 4.5 of 449 users Rating 4.5 of 449 users (449)