EXCEL advanced functions

This lesson introduces the most commonly used array and programming formulas, including SEQUENCE, the application of commas and semicolons within curly braces, and their nested use in traditional lookup functions.

This lesson demonstrates how to use array formulas to perform monthly statistics on a table containing dates, accomplishing the task with a single array formula to prevent accidental cell modifications in the data range.

This lesson covers batch filtering based on specified characters using functions like TEXT.FILTER and VSTACK.

This lesson explains how to use COUNTIFS with arrays for batch calculations while introducing the LET function, which allows defining names for cells within formulas for later reference.

This lesson demonstrates how to identify patterns in disorganized worksheets and use the TOCOL function to filter out unnecessary data.

This lesson explains how to use MATCH and OFFSET to locate data within merged cells and format it using WRAPROWS.

This lesson shows how to arrange large datasets into multiple columns for better printing and layout, while also incorporating page-number-based queries.

This lesson compares conventional and array formula methods to identify the first occurrence of data in a row and its corresponding header, while introducing LAMBDA for custom formula creation.

This lesson focuses on combining MAP and LAMBDA, discussing the limitations of MAP, and briefly introducing TEXTSPLIT and hidden line-break characters in cells.

This lesson explains how to extract specified amounts from multiple sheets and display them in a single worksheet, including methods for retrieving sheet names and using INDIRECT.

This lesson demonstrates the use of MAKEARRAY to scan and process each cell within a specified range.

This lesson combines MAKEARRAY and OFFSET to create incremental sequences, deepening understanding of both functions.

This lesson shows how to extract and display max/min values in a specified format using FILTER, SORT, and TAKE.

This lesson explains how to nest two XLOOKUP functions with MATCH and CHOOSECOLS to achieve formatted outputs directly.

This lesson reveals advanced applications of COUNTIF, enabling multi-row conditions and multi-column queries in a single formula.

This lesson demonstrates how to retrieve the latest and second-latest purchase dates (and the days between them) from multiple customer records.

This lesson introduces the SCAN function to standardize merged cells, offering a more flexible alternative to manual methods like "Go To" fills or Power Query.

This lesson covers SORTBY and introduces the combined use of REDUCE and VSTACK.

This lesson presents two methods for reversing string characters, revisiting the REDUCE function.

Using employee attendance as an example, this lesson locates instances of consecutive absences exceeding two days—a method also applicable to tracking continuous sales or failures.

This lesson explains how to group table data by specific columns, with each group occupying a fixed number of rows (blanks included if necessary).

This lesson details how to duplicate each row in a multi-column table a specified number of times.

This lesson demonstrates formula-based multi-column formatting with automatic expansion when source data grows.

Using team leaders and members as an example, this lesson converts multi-row member listings into single-cell merged entries per leader.

An alternative, simpler approach to duplicating rows as in L22.

A more efficient third method for row duplication.

This lesson introduces another row-replication logic, frequently applicable to date processing

This course demonstrates three methods for performing permutation and combination operations on two columns of data. This approach is commonly applied in scenarios such as converting two-dimensional tables into one-dimensional headers, and allocating combinations of materials.

This lesson explains how to consolidate differently positioned fields from multiple tables into one without VBA.

This course primarily focuses on how to use formulas in practice to achieve the functionality of Text to Columns and apply it to structured tables for full automation.

This course covers how to handle scheduling arrangements, where a group of people take turns on duty during the 5-day workdays, with weekends automatically skipped.

This upgraded course not only covers how to handle scheduling arrangements, where a group of people take turns on duty during the 5-day workdays, with weekends automatically skipped, but also add the argument that days each people on duty.