Summary of Microsoft Excel 2021/365 Tutorial: 3.5+ Hours of Advanced Excel Training Course

This is an AI generated summary. There may be inaccuracies.
Summarize another video · Purchase summarize.tech Premium

00:00:00 - 01:00:00

The video tutorial covers the new dynamic array functions available in Excel 2021. It goes over how to use the unique function to extract values, the sort function to sort data, and the count function to count the number of unique values in a range. Finally, it demonstrates how to use the extract multiple criterion function to extract all the distinct crayons packs from the data.

00:00:00 This tutorial introduces users to the new dynamic array functions available in Excel 2021, including unique filter sort, lookup, and more. In the course's first exercise, debra Ashby demonstrates how to check prerequisites for the course and how to use the skill level of the course to determine if it is appropriate for the user. The second section of the tutorial covers the import of data from external sources, including transforming it with power query and using macros and vba to automate tedious tasks. Finally, the course concludes with a look at forecasting and importing data from external sources.
00:05:00 In this video, Microsoft Excel 2021/365 Tutorial, the instructor covers the new dynamic arrays and functions in Excel. He explains how these work and how they can be helpful in Excel formulas. He also covers spills and arrays, which are new terms for the audience. Finally, he demonstrates a standard Excel formula and a cse array formula.
00:10:00 This video tutorial shows how to use a cse array to combine multiple calculations in one formula and how spills work with dynamic arrays.
00:15:00 This 1-hour Excel tutorial covers the basics of the new unique dynamic function, how it works, and examples.
00:20:00 In this 1-hour tutorial, Microsoft Excel user will learn how to use the unique function to extract a list of unique values from a range of cells. Additionally, the user will learn how to use the count function to count the number of unique values in a range.
00:25:00 In this tutorial, you will learn how to use the unique function to extract different pieces of information from a table of data. First, you will extract the countries and their populations, then the regions and continents they belong to, and finally the data for each column in that table.
00:30:00 In this tutorial, the author demonstrates how to use the unique and extract multiple pieces of criterion functions in Excel. First, they show how to use the unique function to extract values by row. Next, they show how to use the unique function to extract values by column. Finally, they show how to use the extract multiple criterion function to extract all the distinct crayons packs from the data.
00:35:00 This 1-hour video tutorial teaches how to use the sort function in Excel. The video demonstrates how to sort data in ascending and descending order by column, as well as by row.
00:40:00 This 1-hour long tutorial covers the basics of the Microsoft Excel 2021/365 software. The instructor shows how to sort data by column and row, as well as by multiple columns.
00:45:00 In this video, a Microsoft Excel tutorial, the author shows how to use formulas to find which actors have been in the same movie together more than once. The author first sorts the data in ascending order by actor's first name. Next, he joins the text in each row together using the text join function and sorts the data again. Finally, he selects all of the data and applies a conditional formatting to highlight duplicate values.
00:50:00 In this video, the presenter demonstrates how to use the sort function to sort data in Excel. The first example sorts data in ascending order by column, the second example sorts data in descending order by column, and the last example sorts data in ascending order by value.
00:55:00 In this video, the author demonstrates how to use the sort function and the horizontal sort by function to sort a data set in the correct order.

01:00:00 - 02:00:00

This tutorial provides an overview of some of the new features in Microsoft Excel 2021/365, including the x match and x lookup functions. These functions can be used to lookup values in a data set and return their position in the list. The tutorial also demonstrates how to use the filter function to filter data by date and grade.

01:00:00 In this tutorial, the "Sequence" function is introduced. This function can be used to output a list of sequential numbers. The function can be used in a variety of ways, including to create a date schedule.
01:05:00 This 1-hour long tutorial shows how to unstack records in Microsoft Excel 2021/365. The tutorial explains how to create a table control, specify how many rows and columns to use, and use the index function to find the item you want. The tutorial then shows how to use the sequence command to add new items to the table, and how to apply date formatting.
01:10:00 In this tutorial, the filter function is introduced. The function allows users to filter data based on specific criteria. The first lesson covers the basics of the filter function, including how to create a table and data validation dropdown list. The next lesson focuses on using the filter function to filter data. The third lesson covers how to use the filter function to filter data by date and grade.
01:15:00 In this video, the author demonstrates how to use the filter function to filter for one, two, or more pieces of criteria. First, they show how to filter for students who sat the math exam. Next, they show how to filter for students who achieved a grade of C or higher. Finally, they show how to filter for students who sat a test on a specific date.
01:20:00 This 1-hour Excel tutorial covers how to use the plus operator to filter data based on logic, and the asterix operator to filter data based on grades.
01:25:00 In this video, a table is created and filtered to include only venues with a capacity of greater than or equal to 400 and no dj. The table is then named "students." The filter formula is then created, and when adding a new student to the end of the table, the filter formula updates to include the new student's name.
01:30:00 In this video, the presenter shows how to use the equals (E) and minus (-) operators in Excel to filter a list of venues. The presenter demonstrates how to use the operators to filter a list of venues by capacity (greater than or equal to 400) and by the presence of a DJ.
01:35:00 This video provides an overview of the new Microsoft Excel 2021 features including the rand array function. It explains how to use these features to generate random data.
01:40:00 This 1-hour Excel tutorial teaches how to generate a random jump order for a group of jumpers using rand array.
01:45:00 In this video, a tutorial on Excel 2021/365, the author demonstrates how to use the x lookup function to easily lookup values in a data set. The x lookup function has six arguments, and the last three are optional. The first argument is the lookup value, which can be found in cell H3. The second argument is the lookup array, which can be found in the Apps list control. The last argument is the match type, which can be either an exact match or an index and match.
01:50:00 In this video, the instructor demonstrates how to use the x lookup function to return data from a table. First, they demonstrate how to use the x lookup function without any arguments. Next, they demonstrate how to use the x lookup function with one argument, the lookup value. Finally, they demonstrate how to use the x lookup function with two arguments, the lookup value and the data to be looked up.
01:55:00 This tutorial teaches how to use various features of Microsoft Excel 2021/365, including the x match and x lookup functions. The first example looks up a value in a cell and returns its position in a list. The second example uses a wildcard character to look up a value in a list and returns the result as a row number or position in the list. The last example is x match, which is similar to the match function but allows for more flexibility in how values are matched.

02:00:00 - 03:00:00

This tutorial covers how to use the median and mode functions in Excel 2021/365. The mode function finds the value that occurs most frequently, while the median function finds the middle value in a data set. The tutorial also shows how to use the large function to find the second, third, fourth, and fifth largest values in a given range.

02:00:00 The video discuss three different ways to use the Excel x-match function: to find an exact match, to find the next larger item, or to find the next smaller item. It also provides an example of how to use x-match with an index.
02:05:00 In this video, the instructor demonstrates how to use the unique function, filter functions, and sort functions to create data entries in a spreadsheet.
02:10:00 This tutorial covers advanced features of Microsoft Excel, including how to create data validation dropdowns and how to sort and filter results.
02:15:00 This 1-hour Excel training course covers advanced data validation and lookup functions. The course begins by creating a data validation drop-down list of unique values for sales agents. Next, the course covers how to lookup revenue and profit for each sales agent using an x lookup formula. The course concludes by teaching how to use a rand array function to add dummy data to columns B and C.
02:20:00 In this tutorial, the user shows how to create a data validation dropdown list using ranges and the transpose function. Additionally, the user demonstrates how to use the index and match functions to find the row number for a two-way lookup.
02:25:00 This 1-hour Excel training video covers the use of the x lookup and choose functions. The x lookup function is used to look up values in a range, while the choose function is used to select a value from a list. The video demonstrates the use of these functions to lookup values for a company, employees, and departments.
02:30:00 This tutorial explains how to use the choose function to select a range of cells and generate a random number between two specified values.
02:35:00 In this tutorial, the author demonstrates how to use the switch function to lookup a value in a different column based on a certain condition. The disadvantage to using this function is that if the value is deleted or changed, it will cause errors in the column being looked up.
02:40:00 In this tutorial, you'll learn how to use the different Excel functions, including switch and choose. You'll also practice two-way x lookups and how to use the rand between function.
02:45:00 In this lesson, Microsoft Excel 2021/365 user learn how to use the median and mode functions. Median and mode are statistical functions which allow calculation of the middle value or the most common value in a data set.
02:50:00 This tutorial covers the use of the Excel 2021/365 functions mode, mode multi, and mode single. Mode multi finds the value that occurs most frequently, mode single works the same as the old mode function, and large and small find the second, third, fourth, and fifth largest and smallest values in a range.
02:55:00 This tutorial shows how to use the Excel large function and if function to find the second, third, fourth, and fifth largest sales values in a given range of cells.

03:00:00 - 03:40:00

This video tutorial provides an overview of how to use Microsoft Excel to calculate different data values. It covers how to use the rank, average, and aggregate functions, as well as how to round numbers to different decimal places.

03:00:00 In this tutorial, the instructor shows how to rank data in a list, using the rank function in Excel. First, they explain the function's three arguments: number reference, order, and a fourth optional argument, rank. They then show how to rank the data in cell b4 against the data in column b, using the rank function.
03:05:00 In this tutorial, the author explains how to use Excel's rank and average functions. Rank assigns a number to a list of items, while average calculates the average value of a set of numbers.
03:10:00 In this video, a Microsoft Excel tutorial, the instructor shows how to use the count blank function to count the number of customers who have not left their email addresses.
03:15:00 In this tutorial, the author demonstrates how to use various Excel formulas to calculate different data values. First, the author calculates the average, median, and mode values for a table of data. Next, the author finds the largest, smallest, and first three largest values in a table and returns the corresponding product. Finally, the author uses the rank function to rank the data values of a students and sorts the table accordingly.
03:20:00 In this tutorial, the author demonstrates how to use the rank function to rank data in ascending and descending order, as well as how to sort a table by rank. They also discuss how to round values to an even number of pennies.
03:25:00 This 1-hour Excel tutorial covers how to round numbers to different decimal places. The tutorial covers the round up and round down functions, as well as the roundup and floor functions.
03:30:00 This video provides a tutorial on the different Excel functions available, including the enround and aggregate functions. The video shows how to use these functions to calculate aggregates, such as sum, average, count, and min/max.
03:35:00 In this video, Microsoft Excel trainer Brad Johnson explains how to use the aggregate function to calculate different values, including totals, averages, and bonuses.
03:40:00 In this tutorial, Simon Says it provides an introduction to Microsoft Excel 2021/365, including how to enter data and use specialized rounding to calculate a new salary. The video then covers the second part of the exercise, which involves calculating the bonus for each employee. Finally, the video demonstrates how to use the aggregate function to complete the total and average for all of the prices in the data sheet.