iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Excel’s LET, LAMBDA, TEXTSPLIT, TAKE, DROP, VSTACK, and CHOOSECOLS functions can make common spreadsheet jobs easier: clarifying calculations, reusing logic, splitting text, and shaping dynamic-array results. They are not equally unfamiliar to every user, and they are not available in every Excel edition. The examples below are illustrative; check the compatibility notes before sharing a workbook.
Make calculations clearer and reusable
LET: name intermediate calculations
LET assigns names to values or calculations inside a formula. This can make a long formula easier to read, and repeated expressions can be calculated once instead of being written repeatedly.
For example, if A2 contains a subtotal and B2 contains a tax rate, use:
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →=LET(subtotal,A2,taxRate,B2,subtotal*(1+taxRate))
The names subtotal and taxRate make the calculation’s parts visible. Microsoft documents LET for Microsoft 365, Excel 2024, and Excel 2021; confirm availability in the specific product and release channel you use. Microsoft’s LET documentation describes naming intermediate calculations and avoiding repeated calculation of the same expression.
#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
LAMBDA: give a repeated calculation a name
LAMBDA lets you define a reusable custom function in a workbook without VBA, macros, or JavaScript. It is useful when the same calculation appears in many places and deserves a recognizable name.
For a simple markup calculation, a LAMBDA could be:
=LAMBDA(price,rate,price*(1+rate))(A2,10%)
This immediately applies a 10% markup to the value in A2. To reuse it by name, define the LAMBDA in Name Manager, give it an appropriate name such as ADD_MARKUP, and then call it with arguments such as =ADD_MARKUP(A2,10%). An uncalled LAMBDA entered directly in a cell can return #CALC!; an incorrect number of arguments can also cause an error. Microsoft documents up to 253 parameters. LAMBDA is documented for Microsoft 365, Excel 2024, and Excel 2021. See Microsoft’s LAMBDA documentation.
Split text from one cell into columns
TEXTSPLIT: separate text by a delimiter
TEXTSPLIT divides a text string into a spilled array using column and/or row delimiters. For example, if A2 contains Lee, Morgan, this formula splits the name at the comma:
=TEXTSPLIT(A2,",")
The results spill into adjacent cells, leaving the original value intact. You can also split a comma-separated list of tags with the same pattern. TEXTSPLIT is similar to Text to Columns, but expressed as a formula; it is the inverse of TEXTJOIN. Its optional arguments support handling consecutive delimiters, matching case, and padding uneven results. Microsoft documents TEXTSPLIT for Microsoft 365 and Excel 2024, so check the exact installation before relying on it. Details and syntax are in Microsoft’s TEXTSPLIT documentation.
Keep or remove rows and columns at an array’s edge
TAKE: return the first or last part
TAKE returns a specified number of contiguous rows or columns from the beginning or end of an array. If A2:C100 contains records already sorted with the newest record first, this formula returns the first five rows:
=TAKE(A2:C100,5)
To take rows from the end instead, use a negative row count, such as =TAKE(A2:C100,-5). The negative count selects the last five rows; it does not sort the data. TAKE can also select columns by supplying a column count as its third argument.
Recommended Free Tools
DROP: exclude the first or last part
DROP removes a specified number of rows or columns from the beginning or end of an array. To remove a header row from a result in A1:C20, use:
=DROP(A1:C20,1)
A negative row count removes rows from the end; the optional third argument specifies columns to drop. TAKE and DROP are listed with Microsoft’s 2024 version marker, which indicates they are unavailable in earlier versions. Check Microsoft’s TAKE documentation and DROP documentation for syntax and availability details.
Rank #4
Consolidate and reshape arrays
VSTACK: append lists vertically
VSTACK appends arrays in sequence, placing each one below the previous one. If January and February records are in A2:C20 and E2:G15, respectively, this formula combines them vertically:
=VSTACK(A2:C20,E2:G15)
Use it when the source lists have compatible column layouts. Review the spilled result if source arrays have different dimensions. VSTACK carries Microsoft’s 2024 version marker; see Microsoft’s VSTACK documentation.
CHOOSECOLS: return selected fields
CHOOSECOLS extracts specified columns from an array, which is useful for creating a compact view without changing the source data. If A2:F100 contains six fields and you need only the first, third, and sixth, use:
=CHOOSECOLS(A2:F100,1,3,6)
The numbers identify the columns’ positions in the supplied array, not worksheet column letters. CHOOSECOLS also carries Microsoft’s 2024 version marker. Check Microsoft’s CHOOSECOLS documentation for details.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Which function fits the job?
| Task | Function | Best fit |
|---|---|---|
| Make a complex formula easier to follow | LET | Name values or intermediate calculations within one formula. |
| Reuse a calculation across a workbook | LAMBDA | Define a named custom function and call it with arguments. |
| Split text using a delimiter | TEXTSPLIT | Turn a text string into a spilled row or column of results. |
| Return or remove contiguous array edges | TAKE or DROP | Keep or exclude rows or columns at the beginning or end. |
| Combine lists vertically | VSTACK | Append arrays in order, provided their layouts are compatible. |
| Show selected fields from a wider array | CHOOSECOLS | Extract columns by their position in the source array. |
LET and LAMBDA can help organize formula logic, while TEXTSPLIT, TAKE, DROP, VSTACK, and CHOOSECOLS operate on text or arrays. The right choice depends on the task and whether everyone opening the workbook has a compatible Excel version.
Will these formulas work in my version of Excel?
Availability varies by function, edition, and release channel. Microsoft’s function catalog marks TAKE, DROP, VSTACK, and CHOOSECOLS with the 2024 marker and says marked functions are unavailable in earlier versions. Its documentation lists LET and LAMBDA for Microsoft 365, Excel 2024, and Excel 2021, and TEXTSPLIT for Microsoft 365 and Excel 2024. Those edition listings are a useful starting point, not a guarantee for every installation or update channel. Check the function’s Microsoft page and test the workbook on the Excel versions your collaborators actually use before depending on a newer function.
Compatibility matters even for familiar formulas: Microsoft says XLOOKUP is not available in Excel 2016 and Excel 2019, so a workbook built in a newer release may not work for someone using those editions. Consult Microsoft’s Excel function catalog and the individual function documentation when checking support.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
