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
-
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
-
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
-
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
-
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
-
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
-
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
-
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
-
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 Using Custom and Conditional Formatting in Excel 365 (2024)
Rating 5.0 of 1 users
5.0 (1)
Course Performing data analysis and forecasting in Excel 365
Rating 4.5 of 319 users
4.5 (319)
Course
Rating 4.5 of 2813 users
4.5 (2813)
