Excel Advanced Functions

array formulas, template lambda functions

What you'll learn

Master advanced Excel formulas.

Become an array formula expert.

Acquire the skills to create complex charts.

Acquire the proficiency to craft dynamic templates.

Requirements

Basic knowledge of Excel

Description

This course primarily focuses on new array formulas or programming formulas, requiring an OFFICE 365 or WPS software environment. such as MAP, BYROW, TOCOL, SEQUENCE, CHOOSECOLS, LAMDBA, LET  etc.The course materials are sourced from mainland China, summarizing and refining real-world spreadsheet problems encountered across various industries—such as logistics, manufacturing, agriculture, real estate, human resources, and more, along with their solutions. While you may not have encountered these issues before, the Chinese approach to solving them may broaden your perspective and provide new insights.This course is not suitable for absolute beginners but is highly recommended for those transitioning from an intermediate to an advanced level. And Some formulas in this course can be directly applied to similar scenarios by adjusting the cell reference ranges, without requiring thorough comprehension.The course videos feature an English interface and audio narration, though the narration is delivered in non-native English. Please refrain from purchasing if this is a concern.The course will be updated periodically, but the price will remain unchanged at its most affordable level in the long term. The course progresses from simple to complex, gradually increasing in difficulty. It will includes 40 recorded lessons and more, each averaging between 8 to 15 minutes in length.

Overview

Section 1: Array Formulas

Lecture 1 Sequence, Curly Braces, and VLOOKUP

Lecture 2 DATE, EOMONTH, and BYROW

Lecture 3 Vstack and Filter

Lecture 4 Hstack and Unique

Lecture 5 Clever Use of IF and TOCOL Functions

Lecture 6 Clever Use of WRAPROWS Function

Lecture 7 Creating Multi-Column and Pagination Effects in Worksheets

Lecture 8 First Month Sales and Corresponding Month

Lecture 9 Adding Serial Numbers to Each Line in Wrapped Cells

Lecture 10 Consolidating Balances from Multiple Worksheets

Lecture 11 Creating a Multiplication Table

Lecture 12 Generating Sequences with Specific Patterns

Lecture 13 Finding Maximum and Minimum Values Across Groups

Lecture 14 Nested Array Queries

Lecture 15 Reverse Lookup

Lecture 16 Querying Most Recent and Second-Most Recent Dates

Lecture 17 Using Formulas to Handle Merged Cells

Lecture 18 Extracting the Last 5 Unique Characters

Lecture 19 Batch Reversing Strings

Lecture 20 Identifying Consecutive Numbers and Their Frequency

Lecture 21 Transforming Table Layouts

Lecture 22 Replicating Each Row a Variable Number of Times (Method 1)

Lecture 23 Implementing Word-Style Multi-Column Layouts in Excel

Lecture 24 Merging Multiple Rows into a Single Row per Group

Lecture 25 Replicating Each Row a Variable Number of Times (Method 2)

Lecture 26 Replicating Each Row a Variable Number of Times (Method 3)

Lecture 27 Replicating Each Row a Variable Number of Times (Method 4)

Lecture 28 Merging Specified Fields from Multiple Tables

Lecture 29 Converting 1D Tables to 2D Tables

Lecture 30 query data

Aiming to advance Excel skills