In the data-driven era, Excel has become an essential tool for data analysis and management. This article provides an in-depth introduction to 32 new Excel functions with practical examples. These new functions cover data reshaping, filtering, text processing, and custom calculations, helping users improve efficiency and accuracy in data handling. Whether you are a professional analyst or a regular user, these new Excel functions will help you work more flexibly with data and make data analysis simpler and more efficient.
No. 1: Pivotby function
Function: Pivot data using formulas, supporting multiple columns and tables to pivot sales (column C) by city (column A) and product (column B).
Example：
=pivotby(A1:A10,B1:B10,C1:C10,Sum,3)| Column A | Column B | Column C |
|---|---|---|
| City | Product | Sales |
| Beijing | A | 100 |
| Beijing | B | 150 |
| Shanghai | A | 200 |
| Shanghai | B | 250 |
| Beijing | A | 300 |
| Shanghai | B | 400 |
| Shanghai | A | 50 |
| Beijing | B | 80 |
Result：
| City | Product | Sales |
|---|---|---|
| Beijing | A | 400 |
| Beijing | B | 230 |
| Shanghai | A | 250 |
| Shanghai | B | 650 |
No. 2: Groupby function
Function: Summarize by category using formulas, supporting multiple columns and tables to aggregate sales (column C) by city (column A) and product (column B).
Example：
=Groupby(A1:B10,C1:C10,Sum,3)| Column A | Column B | Column C |
|---|---|---|
| City | Product | Sales |
| Beijing | A | 100 |
| Beijing | B | 150 |
| Shanghai | A | 200 |
| Shanghai | B | 250 |
Result：
| City | Product | Sales |
|---|---|---|
| Beijing | A | 400 |
| Beijing | B | 230 |
| Shanghai | A | 250 |
| Shanghai | B | 650 |
No. 3: Regexextract function
Function: Extract all integers from text using regular expressions.
Example：
=Regexextract(A1,"\d+")| Column A |
|---|
| Content |
| My phone number is 1234567890 |
Result：
| Extracted integers |
|---|
| 1234567890 |
No. 4: Filter function
Function: One-to-many filtering — filter all rows for the Finance department (column A).
Example：
=Filter(A1:F100,A1:A100="財務")| Column A | Column B | Column C | Column D | Column E | Column F |
|---|---|---|---|---|---|
| Department | Name | Position | Salary | Age | Region |
| Finance | Zhang San | Manager | 8000 | 35 | Beijing |
| HR | Li Si | Specialist | 5000 | 28 | Shanghai |
| Finance | Wang Wu | Assistant | 6000 | 30 | Guangzhou |
Result：
| Department | Name | Position | Salary | Age | Region |
|---|---|---|---|---|---|
| Finance | Zhang San | Manager | 8000 | 35 | Beijing |
| Finance | Wang Wu | Assistant | 6000 | 30 | Guangzhou |
5th: Vstack function
FunctionCombine multiple sheets: merge sheets for months 1–12
Example：
=Vstack('1月:12月'!A1:B100)| Column A | Column B |
|---|---|
| Month | Sales |
| Jan | 1000 |
| Feb | 1500 |
Result：
| Month | Sales |
|---|---|
| Jan | 1000 |
| Feb | 1500 |
| Mar | 1800 |
| Apr | 2000 |
6th: Xlookup function
FunctionMulti-criteria lookup, lookup from bottom to top: retrieve Education (column D) by Department (column A) and Name (column B)
Example：
=Xlookup("財務部"&"張三",A1:A10&B1:B10,D1:D10)| Column A | Column B | Column D |
|---|---|---|
| Department | Name | Education |
| Finance Department | Zhang San | Master's |
| HR Department | Li Si | Bachelor's |
Result：
| Education |
|---|
| Master's |
Rank 7: Textjoin function
Function: Join multiple values with a delimiter; join the values in A1:A10 with -
Example：
=Textjoin("-",,A1:A10)| Column A |
|---|
| Value |
| One |
| Two |
| Three |
Result：
| Joined result |
|---|
| One-Two-Three |
Rank 8: Textsplit function
Function: Split a string into multiple values by a delimiter; split 'Zhang San-male-20' in cell A1 into three cells.
Example：
=Textsplit(A1,"-")| Column A |
|---|
| Content |
| Zhang San-male-20 |
Result：
| Name | Gender | Age |
|---|---|---|
| Zhang San | Male | 20 |
Rank 9: Textbefore function
Function: Extract the content before a character; extract the province name before '省'.
Example：
=Textbefore(A1,"省")| Column A |
|---|
| Content |
| Guangdong Province |
Result：
| Province |
|---|
| Guangdong |
Rank 10: Textafter function
Function: Extract the content after a character; extract the detailed address after '市'.
Example：
=Textafter(A1,"市")| Column A |
|---|
| Content |
| Guangzhou City Tianhe District |
Result：
| Detailed address |
|---|
| Tianhe District |
Rank 11: Unique function
Function: Extract a list of unique values; extract unique company names from column A.
Example：
=Unique(A:A)| Column A |
|---|
| Company Name |
| Company A |
| Company B |
| Company A |
Result：
| Unique company names |
|---|
| Company A |
| Company B |
Rank 12: Sort function
Function: Sort the table — sort by the table's 3rd column in descending order
Example：
=SORT(A1:D10,3,-1)| Column A | Column B | Column C | Column D |
|---|---|---|---|
| Name | Type | Quantity | Price |
| Product 1 | Category A | 10 | 20 |
| Product 2 | Category B | 5 | 50 |
Result：
| Column A | Column B | Column C | Column D |
|---|---|---|---|
| Product 1 | Category A | 10 | 20 |
| Product 2 | Category B | 5 | 50 |
No. 13: SortBy function
Function: Multi-column sort — sort the table by columns C and D in ascending order
Example：
=SORTBY(A2:D11,C2:C11,1,D2:D11,1)| Column A | Column B | Column C | Column D |
|---|---|---|---|
| Name | Type | Quantity | Price |
| Product 1 | Category A | 10 | 20 |
| Product 2 | Category B | 5 | 50 |
Result：
| Column A | Column B | Column C | Column D |
|---|---|---|---|
| Product 2 | Category B | 5 | 50 |
| Product 1 | Category A | 10 | 20 |
No. 14: Tocol function
Function: Convert multiple-column values into a single column — convert range A1:F10 into one column
Example：
=Tocol(A1:F10)| Column A | Column B | Column C | Column D | Column E | Column F |
|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 6 |
| 7 | 8 | 9 | 10 | 11 | 12 |
| 13 | 14 | 15 | 16 | 17 | 18 |
Result：
| Result |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
No. 15: ToRow function
Function: Convert multiple-column values into a single row — convert range A1:F10 into one row
Example：
=ToRow(A1:F10)| Column A | Column B | Column C | Column D | Column E | Column F |
|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 6 |
| 7 | 8 | 9 | 10 | 11 | 12 |
| 13 | 14 | 15 | 16 | 17 | 18 |
Result：
| Result |
|---|
| 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18 |
No. 16: Hstack function
Function: Horizontally combine multiple tables — merge columns A, C, and F into a new table
Example：
=Hstack(A1:A10,C1:C10,F1:F10)| Column A | Column C | Column F |
|---|---|---|
| 1 | 3 | 6 |
| 2 | 4 | 7 |
| 5 | 8 | 9 |
Result：
| Result | ||
|---|---|---|
| 1 | 3 | 6 |
| 2 | 4 | 7 |
| 5 | 8 | 9 |
No. 17: ChooseCols function
Function: Extract specific columns from a table — extract columns 1, 2, and 5
Example：
=ChooseCols(A1:G10,1,2,5)| Column A | Column B | Column C | Column D | Column E | Column F | Column G |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| 8 | 9 | 10 | 11 | 12 | 13 | 14 |
Result：
| Result | Column 1 | Column 2 | Column 5 |
|---|---|---|---|
| 1 | 2 | 5 | |
| 8 | 9 | 12 |
No. 18: ChooseRows function
Function: Extract specific rows from a table — extract rows 1, 2, and 5
Example：
=ChooseRows(A1:G10,1,2,5)| Column A | Column B | Column C | Column D | Column E | Column F | Column G |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| 8 | 9 | 10 | 11 | 12 | 13 | 14 |
Result：
| Result | Row 1 | Row 2 | Row 5 |
|---|---|---|---|
| 1 | 2 | 3 | 4 |
| 8 | 9 | 10 | 11 |
No. 19: Drop function
Function: Remove rows or columns from a table — remove the table's first row
Example：
=Drop(A1:A100,1)| Column A |
|---|
| 1 |
| 2 |
| 3 |
Result：
| Result |
|---|
| 2 |
| 3 |
No. 20: Take function
Function: Take the first N rows or columns from a table — take the table's first 10 rows
Example：
=Take(A1:F100,10)| Column A | Column B | Column C | Column D | Column E | Column F |
|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 6 |
| 7 | 8 | 9 | 10 | 11 | 12 |
Result：
| Result | Column 1 | Column 2 | Column 3 | Column 4 | Column 5 | Column 6 |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 6 | |
| 7 | 8 | 9 | 10 | 11 | 12 |
No. 21: Arraytotext function
Function: Join characters or numbers with commas — join the values in A1:A10 with commas
Example：
=Arraytotext(A1:A10)| Column A |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
Result：
| Result |
|---|
| 1, 2, 3, 4, 5 |
No. 22: Concat function
Function: Concatenate characters or numbers — concatenate the values in A1:A10
Example：
=Concat(A1:A10)| Column A |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
Result：
| Result |
|---|
| 1 2 3 4 5 |
No. 23: Sequence function
Function: Generate a sequence — generate even numbers from 1~10
Example：
=Sequence(5,,2,2)Result：
| Result |
|---|
| 2 |
| 4 |
| 6 |
| 8 |
| 10 |
No. 24: Regexreplace function
Function: Replace characters using a regular expression — replace the digits in A1 with 100
Example：
=Regexreplace(A1,"\d+",100)| Column A |
|---|
| 123 |
Result：
| Result |
|---|
| 100 |
No. 25: Regextest function
Function: Use a regular expression to check for inclusion — determine whether A1 contains digits
Example：
=Regexextract(A1,"\\d+")| Column A |
|---|
| abc123 |
Result：
| Result |
|---|
| 123 |
No. 26: Lambda function
Function: Custom function — define a function that adds two numbers
Example：=Lambda(x,y,x+y)
Result：
| Result |
|---|
| 3 |
No. 27: Reduce function
Function: Iterate over each value in an array and accumulate the result — sum the positive numbers in A1:A10
Example：
=Reduce(0,A1:A10,Lambda(x,y,IF(y>0,x+y,x)))| Column A |
|---|
| 1 |
| -1 |
| 2 |
| 3 |
Result：
| Result |
|---|
| 6 |
No. 28: Scan function
Function: Same operation pattern as Reduce but preserves each intermediate result — sum the positive numbers in A1:A10 and return the running totals
Example：
=Scan(0,A1:A10,Lambda(x,y,IF(y>0,x+y,x)))| Column A |
|---|
| 1 |
| -1 |
| 2 |
| 3 |
Result：
| Result |
|---|
| 1 |
| 1 |
| 3 |
| 6 |
No. 29: Map function
Function: Process each value in an array and return each result — replace blank values in A1:A10 with the character '零'.
Example：
=Map(A1:A10,Lambda(X,IF(X=0,"零",X)))| Column A |
|---|
| 1 |
| 0 |
| 2 |
Result：
| Result |
|---|
| 1 |
| Zero |
| 2 |
No. 30: Let function
Function: Define names to simplify formulas — evaluate a Vlookup result
Example：
=Let(x,Vlookup(D1,A:B,2,0),IF(x>10,"完成","未完成"))| Column A | Column B |
|---|---|
| 1 | 15 |
| 2 | 5 |
Result：
| Result |
|---|
| Completed |
No. 31: Byrow function
Function: Perform calculations across each row and return each row's average for the range
Example：=Byrow(B2:F5,AVERAGE)
| Column B | Column C | Column D | Column E | Column F |
|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 |
| 6 | 7 | 8 | 9 | 10 |
Result：
| Result |
|---|
| 3 |
| 8 |
No. 32: ByCol function
Function: Perform calculations on each column and return multiple results — return each column's average for the range
Example：
=ByCol(B2:F5,AVERAGE)| Column B | Column C | Column D | Column E | Column F |
|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 |
| 6 | 7 | 8 | 9 | 10 |
Result：
| Result |
|---|
| 3.5 |
| 4.5 |
| 5.5 |
| 6.5 |
| 7.5 |
This article summarizes the functionality and usage examples of 32 Excel functions. From basic data-restructuring functions like Tocol and ToRow to more advanced custom functions such as Lambda and Reduce, these functions demonstrate Excel's powerful data-processing capabilities. By using them, users can manipulate data more flexibly to meet diverse analysis needs. As Excel continues to evolve, mastering these functions will help users go further in data analysis and fully leverage Excel's potential.
