{"id":3203,"date":"2024-10-30T16:27:44","date_gmt":"2024-10-30T08:27:44","guid":{"rendered":"https:\/\/www.fyndpro.com\/?p=3203"},"modified":"2026-08-27T16:10:49","modified_gmt":"2026-08-27T08:10:49","slug":"32-new-excel-functions-and-examples","status":"publish","type":"post","link":"https:\/\/www.fyndpro.com\/en\/32-new-excel-functions-and-examples\/","title":{"rendered":"32 New Excel Functions: Uses and Examples (Ranked by Practicality)"},"content":{"rendered":"

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.<\/p>\n\n\n\n

No. 1: Pivotby function<\/h2>\n\n\n\n

Function<\/strong>: Pivot data using formulas, supporting multiple columns and tables to pivot sales (column C) by city (column A) and product (column B).<\/p>\n\n\n\n

Example<\/strong>\uff1a<\/p>\n\n\n\n

=pivotby(A1:A10,B1:B10,C1:C10,Sum,3)<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th>Column B<\/th>Column C<\/th><\/tr>
City<\/td>Product<\/td>Sales<\/td><\/tr>
Beijing<\/td>A<\/td>100<\/td><\/tr>
Beijing<\/td>B<\/td>150<\/td><\/tr>
Shanghai<\/td>A<\/td>200<\/td><\/tr>
Shanghai<\/td>B<\/td>250<\/td><\/tr>
Beijing<\/td>A<\/td>300<\/td><\/tr>
Shanghai<\/td>B<\/td>400<\/td><\/tr>
Shanghai<\/td>A<\/td>50<\/td><\/tr>
Beijing<\/td>B<\/td>80<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
City<\/th>Product<\/th>Sales<\/th><\/tr>
Beijing<\/td>A<\/td>400<\/td><\/tr>
Beijing<\/td>B<\/td>230<\/td><\/tr>
Shanghai<\/td>A<\/td>250<\/td><\/tr>
Shanghai<\/td>B<\/td>650<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
No. 2: Groupby function<\/h2>\n\n\n\n
Function<\/strong>: Summarize by category using formulas, supporting multiple columns and tables to aggregate sales (column C) by city (column A) and product (column B).<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=Groupby(A1:B10,C1:C10,Sum,3)<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th>Column B<\/th>Column C<\/th><\/tr>
City<\/td>Product<\/td>Sales<\/td><\/tr>
Beijing<\/td>A<\/td>100<\/td><\/tr>
Beijing<\/td>B<\/td>150<\/td><\/tr>
Shanghai<\/td>A<\/td>200<\/td><\/tr>
Shanghai<\/td>B<\/td>250<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
City<\/th>Product<\/th>Sales<\/th><\/tr>
Beijing<\/td>A<\/td>400<\/td><\/tr>
Beijing<\/td>B<\/td>230<\/td><\/tr>
Shanghai<\/td>A<\/td>250<\/td><\/tr>
Shanghai<\/td>B<\/td>650<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
No. 3: Regexextract function<\/h2>\n\n\n\n
Function<\/strong>: Extract all integers from text using regular expressions.<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=Regexextract(A1,\"\\d+\")<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th><\/tr>
Content<\/td><\/tr>
My phone number is 1234567890<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Extracted integers<\/th><\/tr>
1234567890<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
No. 4: Filter function<\/h2>\n\n\n\n
Function<\/strong>: One-to-many filtering \u2014 filter all rows for the Finance department (column A).<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=Filter(A1:F100,A1:A100=\"\u8ca1\u52d9\")<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th>Column B<\/th>Column C<\/th>Column D<\/th>Column E<\/th>Column F<\/th><\/tr>
Department<\/td>Name<\/td>Position<\/td>Salary<\/td>Age<\/td>Region<\/td><\/tr>
Finance<\/td>Zhang San<\/td>Manager<\/td>8000<\/td>35<\/td>Beijing<\/td><\/tr>
HR<\/td>Li Si<\/td>Specialist<\/td>5000<\/td>28<\/td>Shanghai<\/td><\/tr>
Finance<\/td>Wang Wu<\/td>Assistant<\/td>6000<\/td>30<\/td>Guangzhou<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Department<\/th>Name<\/th>Position<\/th>Salary<\/th>Age<\/th>Region<\/th><\/tr>
Finance<\/td>Zhang San<\/td>Manager<\/td>8000<\/td>35<\/td>Beijing<\/td><\/tr>
Finance<\/td>Wang Wu<\/td>Assistant<\/td>6000<\/td>30<\/td>Guangzhou<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
5th: Vstack function<\/h2>\n\n\n\n
Function<\/strong>Combine multiple sheets: merge sheets for months 1\u201312<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=Vstack('1\u6708:12\u6708'!A1:B100)<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th>Column B<\/th><\/tr>
Month<\/td>Sales<\/td><\/tr>
Jan<\/td>1000<\/td><\/tr>
Feb<\/td>1500<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Month<\/th>Sales<\/th><\/tr>
Jan<\/td>1000<\/td><\/tr>
Feb<\/td>1500<\/td><\/tr>
Mar<\/td>1800<\/td><\/tr>
Apr<\/td>2000<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
6th: Xlookup function<\/h2>\n\n\n\n
Function<\/strong>Multi-criteria lookup, lookup from bottom to top: retrieve Education (column D) by Department (column A) and Name (column B)<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=Xlookup(\"\u8ca1\u52d9\u90e8\"&\"\u5f35\u4e09\",A1:A10&B1:B10,D1:D10)<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th>Column B<\/th>Column D<\/th><\/tr>
Department<\/td>Name<\/td>Education<\/td><\/tr>
Finance Department<\/td>Zhang San<\/td>Master's<\/td><\/tr>
HR Department<\/td>Li Si<\/td>Bachelor's<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Education<\/th><\/tr>
Master's<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
Rank 7: Textjoin function<\/h2>\n\n\n\n
Function<\/strong>: Join multiple values with a delimiter; join the values in A1:A10 with -<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=Textjoin(\"-\",,A1:A10)<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th><\/tr>
Value<\/td><\/tr>
One<\/td><\/tr>
Two<\/td><\/tr>
Three<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Joined result<\/th><\/tr>
One-Two-Three<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
Rank 8: Textsplit function<\/h2>\n\n\n\n
Function<\/strong>: Split a string into multiple values by a delimiter; split 'Zhang San-male-20' in cell A1 into three cells.<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=Textsplit(A1,\"-\")<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th><\/tr>
Content<\/td><\/tr>
Zhang San-male-20<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Name<\/th>Gender<\/th>Age<\/th><\/tr>
Zhang San<\/td>Male<\/td>20<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
Rank 9: Textbefore function<\/h2>\n\n\n\n
Function<\/strong>: Extract the content before a character; extract the province name before '\u7701'.<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=Textbefore(A1,\"\u7701\")<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th><\/tr>
Content<\/td><\/tr>
Guangdong Province<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Province<\/th><\/tr>
Guangdong<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
Rank 10: Textafter function<\/h2>\n\n\n\n
Function<\/strong>: Extract the content after a character; extract the detailed address after '\u5e02'.<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=Textafter(A1,\"\u5e02\")<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th><\/tr>
Content<\/td><\/tr>
Guangzhou City Tianhe District<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Detailed address<\/th><\/tr>
Tianhe District<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
Rank 11: Unique function<\/h2>\n\n\n\n
Function<\/strong>: Extract a list of unique values; extract unique company names from column A.<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=Unique(A:A)<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th><\/tr>
Company Name<\/td><\/tr>
Company A<\/td><\/tr>
Company B<\/td><\/tr>
Company A<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Unique company names<\/th><\/tr>
Company A<\/td><\/tr>
Company B<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
Rank 12: Sort function<\/h2>\n\n\n\n
Function<\/strong>: Sort the table \u2014 sort by the table's 3rd column in descending order<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=SORT(A1:D10,3,-1)<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th>Column B<\/th>Column C<\/th>Column D<\/th><\/tr>
Name<\/td>Type<\/td>Quantity<\/td>Price<\/td><\/tr>
Product 1<\/td>Category A<\/td>10<\/td>20<\/td><\/tr>
Product 2<\/td>Category B<\/td>5<\/td>50<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Column A<\/th>Column B<\/th>Column C<\/th>Column D<\/th><\/tr>
Product 1<\/td>Category A<\/td>10<\/td>20<\/td><\/tr>
Product 2<\/td>Category B<\/td>5<\/td>50<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
No. 13: SortBy function<\/h2>\n\n\n\n
Function<\/strong>: Multi-column sort \u2014 sort the table by columns C and D in ascending order<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=SORTBY(A2:D11,C2:C11,1,D2:D11,1)<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th>Column B<\/th>Column C<\/th>Column D<\/th><\/tr>
Name<\/td>Type<\/td>Quantity<\/td>Price<\/td><\/tr>
Product 1<\/td>Category A<\/td>10<\/td>20<\/td><\/tr>
Product 2<\/td>Category B<\/td>5<\/td>50<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Column A<\/th>Column B<\/th>Column C<\/th>Column D<\/th><\/tr>
Product 2<\/td>Category B<\/td>5<\/td>50<\/td><\/tr>
Product 1<\/td>Category A<\/td>10<\/td>20<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
No. 14: Tocol function<\/h2>\n\n\n\n
Function<\/strong>: Convert multiple-column values into a single column \u2014 convert range A1:F10 into one column<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=Tocol(A1:F10)<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th>Column B<\/th>Column C<\/th>Column D<\/th>Column E<\/th>Column F<\/th><\/tr>
1<\/td>2<\/td>3<\/td>4<\/td>5<\/td>6<\/td><\/tr>
7<\/td>8<\/td>9<\/td>10<\/td>11<\/td>12<\/td><\/tr>
13<\/td>14<\/td>15<\/td>16<\/td>17<\/td>18<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Result<\/th><\/tr>
1<\/td><\/tr>
2<\/td><\/tr>
3<\/td><\/tr>
4<\/td><\/tr>
5<\/td><\/tr>
6<\/td><\/tr>
7<\/td><\/tr>
8<\/td><\/tr>
9<\/td><\/tr>
10<\/td><\/tr>
11<\/td><\/tr>
12<\/td><\/tr>
13<\/td><\/tr>
14<\/td><\/tr>
15<\/td><\/tr>
16<\/td><\/tr>
17<\/td><\/tr>
18<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
No. 15: ToRow function<\/h2>\n\n\n\n
Function<\/strong>: Convert multiple-column values into a single row \u2014 convert range A1:F10 into one row<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=ToRow(A1:F10)<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th>Column B<\/th>Column C<\/th>Column D<\/th>Column E<\/th>Column F<\/th><\/tr>
1<\/td>2<\/td>3<\/td>4<\/td>5<\/td>6<\/td><\/tr>
7<\/td>8<\/td>9<\/td>10<\/td>11<\/td>12<\/td><\/tr>
13<\/td>14<\/td>15<\/td>16<\/td>17<\/td>18<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Result<\/th><\/tr>
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
No. 16: Hstack function<\/h2>\n\n\n\n
Function<\/strong>: Horizontally combine multiple tables \u2014 merge columns A, C, and F into a new table<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=Hstack(A1:A10,C1:C10,F1:F10)<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th>Column C<\/th>Column F<\/th><\/tr>
1<\/td>3<\/td>6<\/td><\/tr>
2<\/td>4<\/td>7<\/td><\/tr>
5<\/td>8<\/td>9<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Result<\/th><\/tr>
1<\/td>3<\/td>6<\/td><\/tr>
2<\/td>4<\/td>7<\/td><\/tr>
5<\/td>8<\/td>9<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
No. 17: ChooseCols function<\/h2>\n\n\n\n
Function<\/strong>: Extract specific columns from a table \u2014 extract columns 1, 2, and 5<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=ChooseCols(A1:G10,1,2,5)<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th>Column B<\/th>Column C<\/th>Column D<\/th>Column E<\/th>Column F<\/th>Column G<\/th><\/tr>
1<\/td>2<\/td>3<\/td>4<\/td>5<\/td>6<\/td>7<\/td><\/tr>
8<\/td>9<\/td>10<\/td>11<\/td>12<\/td>13<\/td>14<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Result<\/th>Column 1<\/th>Column 2<\/th>Column 5<\/th><\/tr>
1<\/td>2<\/td>5<\/td><\/tr>
8<\/td>9<\/td>12<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
No. 18: ChooseRows function<\/h2>\n\n\n\n
Function<\/strong>: Extract specific rows from a table \u2014 extract rows 1, 2, and 5<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=ChooseRows(A1:G10,1,2,5)<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th>Column B<\/th>Column C<\/th>Column D<\/th>Column E<\/th>Column F<\/th>Column G<\/th><\/tr>
1<\/td>2<\/td>3<\/td>4<\/td>5<\/td>6<\/td>7<\/td><\/tr>
8<\/td>9<\/td>10<\/td>11<\/td>12<\/td>13<\/td>14<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Result<\/th>Row 1<\/th>Row 2<\/th>Row 5<\/th><\/tr>
1<\/td>2<\/td>3<\/td>4<\/td><\/tr>
8<\/td>9<\/td>10<\/td>11<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
No. 19: Drop function<\/h2>\n\n\n\n
Function<\/strong>: Remove rows or columns from a table \u2014 remove the table's first row<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=Drop(A1:A100,1)<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th><\/tr>
1<\/td><\/tr>
2<\/td><\/tr>
3<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Result<\/th><\/tr>
2<\/td><\/tr>
3<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
No. 20: Take function<\/h2>\n\n\n\n
Function<\/strong>: Take the first N rows or columns from a table \u2014 take the table's first 10 rows<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=Take(A1:F100,10)<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th>Column B<\/th>Column C<\/th>Column D<\/th>Column E<\/th>Column F<\/th><\/tr>
1<\/td>2<\/td>3<\/td>4<\/td>5<\/td>6<\/td><\/tr>
7<\/td>8<\/td>9<\/td>10<\/td>11<\/td>12<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Result<\/th>Column 1<\/th>Column 2<\/th>Column 3<\/th>Column 4<\/th>Column 5<\/th>Column 6<\/th><\/tr>
1<\/td>2<\/td>3<\/td>4<\/td>5<\/td>6<\/td><\/tr>
7<\/td>8<\/td>9<\/td>10<\/td>11<\/td>12<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
No. 21: Arraytotext function<\/h2>\n\n\n\n
Function<\/strong>: Join characters or numbers with commas \u2014 join the values in A1:A10 with commas<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=Arraytotext(A1:A10)<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th><\/tr>
1<\/td><\/tr>
2<\/td><\/tr>
3<\/td><\/tr>
4<\/td><\/tr>
5<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Result<\/th><\/tr>
1, 2, 3, 4, 5<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
No. 22: Concat function<\/h2>\n\n\n\n
Function<\/strong>: Concatenate characters or numbers \u2014 concatenate the values in A1:A10<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=Concat(A1:A10)<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th><\/tr>
1<\/td><\/tr>
2<\/td><\/tr>
3<\/td><\/tr>
4<\/td><\/tr>
5<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Result<\/th><\/tr>
1 2 3 4 5<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
No. 23: Sequence function<\/h2>\n\n\n\n
Function<\/strong>: Generate a sequence \u2014 generate even numbers from 1~10<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=Sequence(5,,2,2)<\/code><\/code><\/pre>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Result<\/th><\/tr>
2<\/td><\/tr>
4<\/td><\/tr>
6<\/td><\/tr>
8<\/td><\/tr>
10<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
No. 24: Regexreplace function<\/h2>\n\n\n\n
Function<\/strong>: Replace characters using a regular expression \u2014 replace the digits in A1 with 100<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=Regexreplace(A1,\"\\d+\",100)<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th><\/tr>
123<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Result<\/th><\/tr>
100<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
No. 25: Regextest function<\/h2>\n\n\n\n
Function<\/strong>: Use a regular expression to check for inclusion \u2014 determine whether A1 contains digits<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=Regexextract(A1,\"\\\\d+\")<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th><\/tr>
abc123<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Result<\/th><\/tr>
123<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
No. 26: Lambda function<\/h2>\n\n\n\n
Function<\/strong>: Custom function \u2014 define a function that adds two numbers<\/p>\n\n\n\n
Example<\/strong>\uff1a=Lambda(x,y,x+y)<\/code><\/p>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
 <\/p>\n\n\n\n
Result<\/th><\/tr>
3<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
No. 27: Reduce function<\/h2>\n\n\n\n
Function<\/strong>: Iterate over each value in an array and accumulate the result \u2014 sum the positive numbers in A1:A10<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=Reduce(0,A1:A10,Lambda(x,y,IF(y>0,x+y,x)))<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th><\/tr>
1<\/td><\/tr>
-1<\/td><\/tr>
2<\/td><\/tr>
3<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Result<\/th><\/tr>
6<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
No. 28: Scan function<\/h2>\n\n\n\n
Function<\/strong>: Same operation pattern as Reduce but preserves each intermediate result \u2014 sum the positive numbers in A1:A10 and return the running totals<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=Scan(0,A1:A10,Lambda(x,y,IF(y>0,x+y,x)))<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th><\/tr>
1<\/td><\/tr>
-1<\/td><\/tr>
2<\/td><\/tr>
3<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Result<\/th><\/tr>
1<\/td><\/tr>
1<\/td><\/tr>
3<\/td><\/tr>
6<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
No. 29: Map function<\/h2>\n\n\n\n
Function<\/strong>: Process each value in an array and return each result \u2014 replace blank values in A1:A10 with the character '\u96f6'.<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=Map(A1:A10,Lambda(X,IF(X=0,\"\u96f6\",X)))<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th><\/tr>
1<\/td><\/tr>
0<\/td><\/tr>
2<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Result<\/th><\/tr>
1<\/td><\/tr>
Zero<\/td><\/tr>
2<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
No. 30: Let function<\/h2>\n\n\n\n
Function<\/strong>: Define names to simplify formulas \u2014 evaluate a Vlookup result<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=Let(x,Vlookup(D1,A:B,2,0),IF(x>10,\"\u5b8c\u6210\",\"\u672a\u5b8c\u6210\"))<\/code><\/code><\/pre>\n\n\n\n
Column A<\/th>Column B<\/th><\/tr>
1<\/td>15<\/td><\/tr>
2<\/td>5<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Result<\/th><\/tr>
Completed<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
No. 31: Byrow function<\/h2>\n\n\n\n
Function<\/strong>: Perform calculations across each row and return each row's average for the range<\/p>\n\n\n\n
Example<\/strong>\uff1a=Byrow(B2:F5,AVERAGE)<\/code><\/p>\n\n\n\n
Column B<\/th>Column C<\/th>Column D<\/th>Column E<\/th>Column F<\/th><\/tr>
1<\/td>2<\/td>3<\/td>4<\/td>5<\/td><\/tr>
6<\/td>7<\/td>8<\/td>9<\/td>10<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Result<\/th><\/tr>
3<\/td><\/tr>
8<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
No. 32: ByCol function<\/h2>\n\n\n\n
Function<\/strong>: Perform calculations on each column and return multiple results \u2014 return each column's average for the range<\/p>\n\n\n\n
Example<\/strong>\uff1a<\/p>\n\n\n\n
=ByCol(B2:F5,AVERAGE)<\/code><\/code><\/pre>\n\n\n\n
Column B<\/th>Column C<\/th>Column D<\/th>Column E<\/th>Column F<\/th><\/tr>
1<\/td>2<\/td>3<\/td>4<\/td>5<\/td><\/tr>
6<\/td>7<\/td>8<\/td>9<\/td>10<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Result<\/strong>\uff1a<\/p>\n\n\n\n
Result<\/th><\/tr>
3.5<\/td><\/tr>
4.5<\/td><\/tr>
5.5<\/td><\/tr>
6.5<\/td><\/tr>
7.5<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
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.<\/p>\n\n\n\n
<\/div>\n\n\n\n
\n
Microsoft official documentation<\/h2>\n\n\n\n
\n
PIVOTBY<\/a><\/div>\n\n\n\n
GROUPBY<\/a><\/div>\n\n\n\n
REGEXEXTRACT<\/a><\/div>\n\n\n\n
FILTER<\/a><\/div>\n\n\n\n
VSTACK<\/a><\/div>\n\n\n\n
XLOOKUP<\/a><\/div>\n\n\n\n
TEXTJOIN<\/a><\/div>\n\n\n\n
TEXTSPLIT<\/a><\/div>\n\n\n\n
TEXTBEFORE<\/a><\/div>\n\n\n\n
TEXTAFTER<\/a><\/div>\n\n\n\n
UNIQUE<\/a><\/div>\n\n\n\n
SORT<\/a><\/div>\n\n\n\n
SORTBY<\/a><\/div>\n\n\n\n
TOCOL<\/a><\/div>\n\n\n\n
TOROW<\/a><\/div>\n\n\n\n
HSTACK<\/a><\/div>\n\n\n\n
CHOOSECOLS<\/a><\/div>\n\n\n\n
CHOOSEROWS<\/a><\/div>\n\n\n\n
DROP<\/a><\/div>\n\n\n\n
TAKE<\/a><\/div>\n\n\n\n
ARRAYTOTEXT<\/a><\/div>\n\n\n\n
CONCAT<\/a><\/div>\n\n\n\n
SEQUENCE<\/a><\/div>\n\n\n\n
REGEXREPLACE<\/a><\/div>\n\n\n\n
REGEXTTEST<\/a><\/div>\n\n\n\n
LAMBDA<\/a><\/div>\n\n\n\n
REDUCE<\/a><\/div>\n\n\n\n
SCAN<\/a><\/div>\n\n\n\n
MAP<\/a><\/div>\n\n\n\n
LET<\/a><\/div>\n\n\n\n
BYROW<\/a><\/div>\n\n\n\n
BYCOL<\/a><\/div>\n<\/div>\n<\/div>","protected":false},"excerpt":{"rendered":"
\u5728\u6578\u64da\u9a45\u52d5\u7684\u6642\u4ee3\uff0cExcel \u5df2\u6210\u70ba\u6578\u64da\u5206\u6790\u548c\u7ba1\u7406\u7684\u91cd\u8981\u5de5\u5177\u3002\u672c\u6587\u5c07\u6df1\u5165\u4ecb\u7d39 32 \u500b\u5168\u65b0\u7684 Excel \u51fd\u6578 […]<\/p>\n","protected":false},"author":1,"featured_media":3791,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[8,20],"tags":[228,237,236,248,249,296,212,247,232,207,240,230,231,211,210,209,244,243,208,242,245,246,79,216,241,229,239,235,250,234,227,324,238,233,277,280],"class_list":["post-3203","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel","category-onlinesupport","tag-arraytotext","tag-bycol","tag-byrow","tag-choosecols","tag-chooserows","tag-excel-functions","tag-filter","tag-hstack","tag-lambda","tag-let","tag-regexextract","tag-regexreplace","tag-regextest","tag-sequence","tag-sort","tag-sortby","tag-textafter","tag-textbefore","tag-textjoin","tag-textsplit","tag-tocol","tag-torow","tag-unique","tag-vstack","tag-xlookup","tag-concat","tag-groupby","tag-map","tag-drop","tag-scan","tag-take","tag-text-processing","tag-pivotby","tag-reduce","tag-data-analysis","tag-data-filtering"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2026\/08\/new_covers_batch_03_1809_excel.jpg?fit=816%2C1088&ssl=1","_links":{"self":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts\/3203","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/comments?post=3203"}],"version-history":[{"count":3,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts\/3203\/revisions"}],"predecessor-version":[{"id":3755,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts\/3203\/revisions\/3755"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/media\/3791"}],"wp:attachment":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/media?parent=3203"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/categories?post=3203"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/tags?post=3203"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}