7 Underrated Excel Functions You're Probably Not Using (But Should)

Introduction

Most Excel users rely on the same handful of functions — VLOOKUP, SUM, IF, COUNTIF — and never explore what else Excel has to offer. But Microsoft has quietly added a wave of powerful new functions to Excel for Microsoft 365 that can replace complex workarounds, eliminate nested formulas, and reshape how you work with data.

This guide covers 7 underrated Excel functions that most people overlook. Each one solves a real problem, saves time, and works right now in Excel 365. With practical examples and formulas you can copy, you will walk away with new tools to add to your daily workflow.

Why These Functions Matter

Before dynamic arrays arrived in Excel, tasks like splitting text, reshaping data, or extracting specific rows required complex nesting, helper columns, or VBA macros. The functions covered here are part of Excel's modern formula engine. They spill results automatically across multiple cells, update dynamically when your data changes, and reduce formulas that once took five nested functions down to one clean line.

All seven functions require Excel for Microsoft 365 (or Excel 2024 for most of them). They are not available in Excel 2019 or earlier. If you share workbooks with users on older versions, keep this compatibility note in mind.

The 7 Functions Step by Step

1. TEXTSPLIT – Split Text Dynamically Into Columns or Rows

What it does: Splits a text string into multiple cells based on a delimiter. It works like the Text to Columns wizard, but as a dynamic formula.

Syntax:

=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

Example: You have a cell containing "Berlin,Munich,Hamburg,Frankfurt" and you want each city in its own column.

=TEXTSPLIT(A1, ",")

This spills four values across four columns automatically. You can also split into rows by using the third argument:

=TEXTSPLIT(A1, , ",")

Pro Tip: To split by multiple delimiters at once (commas and semicolons), pass them as an array: =TEXTSPLIT(A1, {",",";"}).

Replaces: Nested combinations of LEFT, RIGHT, MID, FIND, and SUBSTITUTE that were painful to build and fragile to maintain.

2. TEXTBEFORE & TEXTAFTER – Extract Text Portions Instantly

What they do: TEXTBEFORE returns everything before a specified delimiter. TEXTAFTER returns everything after it. Both support an instance number so you can target the first, second, or last occurrence.

Syntax:

=TEXTBEFORE(text, delimiter, [instance_num])
=TEXTAFTER(text, delimiter, [instance_num])

Example: Extract the username and domain from an email address in cell A1 ("john.doe@company.com").

=TEXTBEFORE(A1, "@")
→ john.doe

=TEXTAFTER(A1, "@")
→ company.com

Advanced use: Extract a file name from a full path like C:\Users\Docs\Report.xlsx by finding the last backslash:

=TEXTAFTER(A1, "\", -1)
→ Report.xlsx

The -1 tells the function to start from the last occurrence of the delimiter — a feature that previously required complex LEN/SUBSTITUTE nesting.

3. TAKE & DROP – Slice Arrays Without INDEX Workarounds

What they do: TAKE returns a specified number of rows or columns from the beginning or end of an array. DROP removes them. Positive numbers work from the start, negative numbers from the end.

Syntax:

=TAKE(array, [rows], [columns])
=DROP(array, [rows], [columns])

Example: Given a sales table in A1:F20, extract just the first 5 rows:

=TAKE(A1:F20, 5)

Or remove the header row from a range:

=DROP(A1:F20, 1)

Need only the last 3 rows? Use a negative number:

=TAKE(A1:F20, -3)

Pro Tip: Combine TAKE with SORT to get a dynamic "Top N" report: =TAKE(SORT(A2:F20, 5, -1), 5) returns the top 5 rows sorted by column 5 in descending order.

4. TOCOL & TOROW – Flatten Any 2D Array Into a Single List

What they do: TOCOL converts a 2D range into a single column. TOROW converts it into a single row. Both support an optional argument to ignore blanks or errors.

Syntax:

=TOCOL(array, [ignore], [scan_by_column])
=TOROW(array, [ignore], [scan_by_column])

Example: You have quarterly data in a 4×3 grid (B2:D5). Flatten it into a single column:

=TOCOL(B2:D5)

To skip blanks, add the ignore argument:

=TOCOL(B2:D5, 1)

Power combo: Get a sorted list of unique values from a 2D range in one formula:

=SORT(UNIQUE(TOCOL(B2:D5, 1)))

Replaces: Manual copy-paste transposing, helper columns, or VBA macros to reshape data.

5. WRAPROWS & WRAPCOLS – Reshape a List Into a Grid

What they do: WRAPROWS takes a one-dimensional list and wraps it into a 2D array with a specified number of items per row. WRAPCOLS does the same but fills column by column.

Syntax:

=WRAPROWS(vector, wrap_count, [pad_with])
=WRAPCOLS(vector, wrap_count, [pad_with])

Example: You have a list of 12 months in A1:A12. Display them as a 3×4 grid (4 items per row):

=WRAPROWS(A1:A12, 4)

Result:

Jan  Feb  Mar  Apr
May  Jun  Jul  Aug
Sep  Oct  Nov  Dec

Good to Know: If your list does not divide evenly, WRAPROWS pads with #N/A by default. Use the third argument to set a custom pad value like an empty string: =WRAPROWS(A1:A10, 4, "").

6. CHOOSEROWS & CHOOSECOLS – Pick Exactly the Rows or Columns You Need

What they do: CHOOSEROWS returns specific rows from an array by their index numbers. CHOOSECOLS does the same for columns. You can reorder them in any sequence.

Syntax:

=CHOOSEROWS(array, row_num1, [row_num2], ...)
=CHOOSECOLS(array, col_num1, [col_num2], ...)

Example: From a sales report in A1:E10, extract only the Region (column 1), Revenue (column 4), and Profit (column 5):

=CHOOSECOLS(A1:E10, 1, 4, 5)

Or pick rows 1, 3, and 5 to build a custom summary:

=CHOOSEROWS(A1:E10, 1, 3, 5)

Pro Tip: Use negative numbers to count from the end. =CHOOSEROWS(A1:E10, -1) returns the last row. This is perfect for dynamic reports where the data length changes.

7. GROUPBY – Aggregate Data With a Single Formula

What it does: GROUPBY groups rows by one or more columns and aggregates values using a function like SUM, AVERAGE, COUNT, or MAX — all in a single, dynamic formula. Think of it as a lightweight Pivot Table that lives in a cell and updates automatically.

Availability note: GROUPBY became generally available in the Current Channel for Microsoft 365 users in September 2024. It is also available in Excel 2024.

Syntax:

=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array])

Example: You have a transaction table with Category in column B and Amount in column D. Summarize total spending by category:

=GROUPBY(B2:B100, D2:D100, SUM)

This returns a clean two-column summary with each unique category and its total. A grand total row appears at the bottom by default.

Want the average instead? Simply swap the function:

=GROUPBY(B2:B100, D2:D100, AVERAGE)

Common Mistake: The row_fields and values ranges must have the same number of rows. Mismatched ranges cause a #VALUE! error. Always double-check your row counts.

Pro Tip: Unlike Pivot Tables, GROUPBY results update instantly without needing a manual refresh. For quick ad hoc summaries, it is faster and lighter than building a full Pivot Table.

Practical Example – Combining Multiple Functions

Here is a real-world scenario where these functions work together. Suppose you receive a CSV export where product names and prices are combined in a single column as "Product|Price" format. You need a clean summary grouped by product.

Step 1: Split the combined text into separate columns using TEXTSPLIT:

=TEXTSPLIT(A2, "|")

Step 2: Extract just the product names using TEXTBEFORE:

=TEXTBEFORE(A2, "|")

Step 3: Extract the price values using TEXTAFTER and convert to numbers:

=VALUE(TEXTAFTER(A2, "|"))

Step 4: Once you have clean columns, use GROUPBY to summarize totals by product.

This workflow replaces what used to take helper columns, Text to Columns, and a Pivot Table — now handled by three or four formulas.

Bonus Tips and Common Mistakes

1. Spill errors (#SPILL!): All these functions use dynamic arrays. If any cell in the spill range already contains data, you get a #SPILL! error. Make sure the output area is clear.

2. Compatibility: These functions are not backward compatible. If you send a workbook to someone using Excel 2019 or earlier, the formulas will show #NAME? errors. Consider providing a static copy for older versions.

3. TOCOL + UNIQUE is your best friend: Whenever you need a unique list from a multi-column range, flatten it first with TOCOL, then wrap it in UNIQUE and SORT. This three-function combo is incredibly powerful.

4. Negative numbers are underused: TAKE, DROP, CHOOSEROWS, and TEXTAFTER all support negative arguments to count from the end. This is extremely useful for dynamic data where the number of rows changes.

Downloadable Practice File

To follow along with every example in this article, download the practice workbook below. It includes seven dedicated sheets — one for each function — with sample data, formula placeholders in blue, and helpful notes.

What is inside the workbook:

• Overview sheet with a summary of all 7 functions

• TEXTSPLIT – name splitting and CSV parsing examples

• TEXTBEFORE & TEXTAFTER – email and file path extraction

• TAKE & DROP – array slicing with employee sales data

• TOCOL & TOROW – flattening a product grid

• WRAPROWS & WRAPCOLS – reshaping a month list into a calendar grid

• CHOOSEROWS & CHOOSECOLS – picking specific rows and columns from a report

• GROUPBY – transaction data with SUM, COUNT, and AVERAGE aggregations

  
  

Note: The formulas require Excel for Microsoft 365 or Excel 2024. They will show #NAME? errors in older versions.

Final Thoughts

Excel keeps evolving, and the functions covered here represent some of the most practical improvements in recent years. Each one eliminates a common pain point — whether that is splitting text, slicing arrays, reshaping data, or creating instant summaries. The best part is that they all work together, so once you learn one, the others click into place quickly.

Start by picking one function that solves a problem you face regularly. Try TEXTSPLIT the next time you receive messy data, or use GROUPBY instead of building a Pivot Table for a quick summary. Small changes in your formula toolkit can save significant time over weeks and months.

Want more Excel tips and practical tutorials? Subscribe to Excelized and never miss a new guide.