Excel TAKE Function Not Working? Fix the Common Causes

If Excel’s TAKE function is not working, first confirm that your Excel version supports it, then check the formula’s array and row or column counts. In Microsoft 365, Excel for the web, and Excel 2024, TAKE returns a specified number of rows or columns from an array; common problems include an unsupported version, a zero or oversized count, and a blocked spill range.

What the TAKE function does

TAKE is a dynamic array function. It returns part of a range or array, taking rows or columns from the beginning or end. A positive number selects from the start; a negative number selects from the end. You can use it to show the latest records, create a short preview of a larger list, or extract a few columns without changing the source data.

For example, assume a worksheet has a five-row by three-column dataset in A2:C6. The formula below returns its first three rows, across all three columns, so the result occupies a three-by-three area:

With the dataset in A2:C6, enter =TAKE(A2:C6,3). It returns the values from A2:C4.

To return a subset of columns as well, TAKE accepts an optional third argument. With the same data in A2:C6, =TAKE(A2:C6,3,2) returns the first three rows and first two columns, corresponding to A2:B4. A negative argument counts from the end: with the same layout, =TAKE(A2:C6,-2) returns the last two rows, A5:C6.

The syntax is TAKE(array, rows, [columns]). The array is the source range or array expression; rows is the number of rows to return; and columns, which is optional, is the number of columns to return. The brackets in the syntax indicate an optional argument—they are not typed into a formula.

Check whether your Excel version supports TAKE

An unsupported version is a frequent reason Excel does not recognize TAKE. The function is available in Microsoft 365, Excel for the web, and Excel 2024. It is not available in older perpetual versions such as Excel 2021, Excel 2019, or Excel 2016. A workbook containing TAKE may display a compatibility warning or an unrecognized-function error when opened in an older release.

On Windows, check your installation by selecting File > Account and looking at the product information and update status. On Mac, open the Excel menu and choose About Microsoft Excel to see the version. Excel for the web runs in a browser and is updated separately from a desktop installation, so opening the workbook there can help distinguish an old desktop version from a problem with the formula itself.

If your organization manages Microsoft 365 updates, the available features can depend on its update channel and deployment schedule. Install available Office updates, or ask your administrator whether the organization has delayed feature updates. If the function remains unavailable in an older Excel release, use a compatible alternative rather than expecting a formula edit to add the missing function.

Verify the formula and the count arguments

Check that the function name is spelled TAKE and that each argument is in the right position. The first argument must identify the source array; the second is the row count, not the column count. The third argument is optional and sets the column count. A misplaced argument can produce an error or a result with the wrong shape.

For a practical check, assume the data is in A2:C6, with five rows and three columns. The formula =TAKE(A2:C6,3,2) asks for three rows and two columns, and returns a three-by-two result from A2:B4. By contrast, =TAKE(A2:C6,2,3) returns two rows and three columns, from A2:C3. Confirm the dimensions you intend before changing the data or rewriting the function.

TAKE can count backward from the bottom or right edge. With data in A2:C6, =TAKE(A2:C6,-2,-1) returns the last two rows and the last column, which is C5:C6. A negative sign is useful when a list grows over time and the newest entries are at the bottom, but it can select the wrong records if your data is sorted in the opposite direction.

Use a positive row count to take rows from the top, or a negative count to take them from the bottom.
Use a positive column count to take columns from the left, or a negative count to take them from the right.
Do not use zero for either count: a zero row or column argument returns #CALC!.
Keep the requested count within the source array’s dimensions. Asking for more rows or columns than the array contains can return #VALUE!.

Excel’s argument separator can also vary with regional settings. Many installations use commas, as in the examples here; some use semicolons. If Excel rejects a formula that otherwise looks correct, check the separator used by another working formula in that installation and replace commas with the appropriate local separator if necessary.

Fix #NAME? and other recognition errors

#NAME? usually means Excel cannot resolve a name in the formula. With TAKE, first check the spelling and make sure the function is supported by the version in which the workbook is open. If a formula shows as ordinary text instead of calculating, check whether the cell is formatted as Text. Change the cell format to General, then re-enter the formula so Excel evaluates it.

A leading apostrophe can also make a formula display as text. Select the cell and inspect the formula bar for an apostrophe before the equals sign. If one is present, remove it and enter the formula again. Also check that Show Formulas has not been switched on: on Windows, Ctrl+` toggles formula display; on Mac, the equivalent shortcut can vary with the keyboard layout, so use the Formulas tab’s Show Formulas control if needed. When that display is enabled, Excel shows formulas in cells instead of their results.

If Excel presents a formula entry or syntax error, verify the parentheses and separators, and ensure the formula starts with an equals sign. In some regional settings, a formula using commas will be rejected even though the function is supported. If the function works in a new workbook but not in the original, inspect the cell’s format and the workbook’s calculation or editing state before changing the formula.

Resolve #SPILL! and table limitations

TAKE can return multiple cells, so Excel needs an unobstructed area in which to place the result. If that area is blocked, Excel returns #SPILL!. Select the error cell to see the outlined spill range and, where available, an explanation of what is preventing the result. Clear any values, formulas, or other contents in the intended output cells; moving the formula to a clear area may be the simplest fix.

Merged cells in the spill area can also prevent an array result from expanding. Unmerge cells if appropriate, or choose a different output location. Do not clear the source data by mistake: the blocked cells are where the result would appear, not necessarily the cells referenced by TAKE.

Dynamic array formulas do not spill from inside an Excel table. If you enter TAKE in a table column and expect its result to fill several cells, move the formula outside the table, leaving space for the output. You can still use a table as the source, but place the formula in a regular worksheet cell where the result can expand. This distinction explains why a valid formula may work in one location and show an error in another.

Understand #CALC!, #VALUE!, and blank-looking results

#CALC! is expected when a row or column count is zero. Check for a zero entered directly or produced by another formula. For example, if a count is calculated from a cell that is blank or contains zero, TAKE may be receiving zero even though the visible formula appears reasonable. Adjust the count logic so it supplies a positive or negative number when there is data to return.

An empty source or an invalid array expression can also lead to a calculation error. Confirm that the referenced range contains the data you expect and that any formula used as the array argument returns an array. If the source range is based on a changing list, a situation with no matching records may need to be handled before TAKE receives the result.

#VALUE! can indicate that the requested number of rows or columns is larger than the corresponding source dimension. Count the source range’s rows and columns, then reduce the requested size or expand the source range. If the count is calculated dynamically, inspect its current value rather than assuming it is within bounds.

A result that looks blank is not always a TAKE failure. The selected cells may themselves be empty, contain formulas that return an empty string, or use formatting that hides their values. Click a returned cell and check the formula bar, and compare the output with the source cells. TAKE selects positions in the array; it does not remove blank records or sort the data.

Where the result appears and how to use it

In a regular worksheet range, a single TAKE formula spills into adjacent cells automatically. Enter the formula only in the top-left cell of the intended output; do not copy it across the whole output area unless you specifically want separate formulas. If the result changes size, Excel updates the spill range when it can, provided the cells remain clear.

TAKE also works inside larger formulas that produce arrays, making it possible to limit a result without creating a helper range. However, debugging is easier if you first test TAKE by itself against a small, known source range. Once you have confirmed the returned rows and columns, combine it with other functions and check that those functions produce the dimensions you expect.

TAKE does not alter, delete, or rearrange the source data. It returns a view of selected positions in the array. If you need to sort records, filter by a condition, or remove duplicates, TAKE alone does not perform those tasks. For example, taking the last five rows means the last five rows in the source’s current order—not automatically the five newest dates unless the data is already ordered by date.

Alternatives when TAKE is unavailable

If you use Excel 2021, Excel 2019, or Excel 2016, you can often select a range manually or use INDEX-based formulas to return a particular row or cell. The best substitute depends on whether you need a fixed-size extract, a result that updates as data changes, or a formula that returns several cells. Older versions have different array-formula behavior, so a modern dynamic-array formula may not be a direct drop-in replacement.

For a simple, one-time extract, copying the needed rows to another area may be clearer than building a complex formula. For a repeatable report, consider whether your Excel version supports another suitable feature or whether the workbook can be opened in Microsoft 365 or Excel for the web. Before sharing a workbook, confirm that recipients’ Excel versions can calculate its functions; a formula that works on your computer may not work in an older release.

Do not replace TAKE with a similarly named function unless it matches the task. TAKE selects a specified number of rows or columns from an edge of an array. It does not search for a value, filter records by criteria, or choose arbitrary row positions. Identifying the actual requirement first helps prevent a workaround that returns plausible but incorrect data.

Frequently Asked Questions

Why does Excel say TAKE is an invalid function?
Most often, the workbook is open in an Excel version that does not support TAKE, such as Excel 2021 or earlier. Confirm the version, update Microsoft 365 if possible, or open the file in Excel for the web or Excel 2024.

Why does TAKE return #SPILL!?
The formula is trying to return results into cells that are not available. Clear the obstructing cells, unmerge cells in the output area, or move the formula outside an Excel table to a blank range.

What does a negative number do in TAKE?
A negative row count selects rows from the bottom of the array, and a negative column count selects columns from the right. The source’s existing order determines which records are at those edges.

Why does TAKE return #CALC! when the formula looks correct?
Check whether the row or column count is zero, including a zero produced by a calculation. TAKE returns #CALC! when either count is zero; also confirm that the source expression is not empty.

Can I use TAKE inside an Excel table?
You can refer to table data as the source, but a dynamic array result cannot spill from a formula cell inside the table. Put the TAKE formula in a regular worksheet cell with enough clear space for its output.

Does TAKE automatically return the newest records?
No. It returns rows from the beginning or end of the array as currently ordered. Sort the source appropriately first, or use another method to identify newest records when their order is not reliable.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.