How to Split an Array in Excel

How-to basics · Updated November 22, 2026

To split a one-column array into two parts in Excel for Microsoft 365, enter =TAKE(A2:A11,4) in an empty cell for the first four items, then enter =DROP(A2:A11,4) elsewhere for the remaining items. Replace the range and split position with your own. Both formulas return dynamic arrays that spill into nearby cells, so leave enough empty space for each result.

An array is a group of values treated together. In Excel, it might be a range of cells or the result of a formula. Splitting one is useful when you need separate groups for analysis, want to divide a list at a particular position, or need to separate the rows or columns of a two-dimensional range. The steps below use the TAKE and DROP functions, available in Microsoft 365 and Excel 2024. If your Excel version does not recognize them, see the troubleshooting section for a practical alternative.

What You’ll Need

  • Microsoft Excel for Microsoft 365 on Windows, or Excel 2024. The main method uses TAKE and DROP.
  • A source array, such as a list in A2:A11, and a clear idea of where to split it.
  • Empty cells next to or below the formula locations. Excel needs room to display each spilled result.

The examples assume your source values are in A2:A11 and that you want the first four values in one output and the rest in another. You can use a different range and split point. Do not include a header in the source unless you intend to split it along with the data.

Step-by-Step

  1. Check and select your source array. Find the cells containing the values you want to divide. For a simple list, click the first value and note the full range, such as A2:A11. For a table of rows and columns, note the entire rectangular range, such as A2:D11. Check whether the selection includes blank rows, headers, or totals; these are included in the split if they are part of the range.
  2. Choose the split boundary. Decide how many items should go into the first part. If the list has ten values and the first part should contain four, the split point is four. The second part will contain the remaining six. The split number counts values from the beginning of the selected range, not worksheet row numbers. For example, the fourth value in A2:A11 is in cell A5.
  3. Make room for the first result. Click an empty cell where the first part should begin, for example D2. Make sure the cells below it are clear for at least as many rows as the first part will contain. If you are splitting a multi-column range, also leave enough clear columns. A spilled array cannot overwrite existing content.
  4. Enter TAKE for the first part. With the source list in A2:A11 and the first four values needed, enter =TAKE(A2:A11,4) in D2 and press Enter. Excel returns the first four values, spilling them from D2 down through D5. TAKE keeps values from the start of the array. Change the 4 to your desired count.
  5. Enter DROP for the remaining part. Click another empty starting cell, such as F2, and enter =DROP(A2:A11,4). Excel removes the first four values from the result and spills the remaining six values in F2:F7. Use the same source range and split number as in the TAKE formula so the two outputs meet at the intended boundary without overlap or missing values.
  6. Check both results against the source. Compare the last value in the first output with the next value in the second output, then confirm the combined number of returned values matches the original list. In this example, D2:D5 has four values and F2:F7 has six, so together they account for all ten values in A2:A11. If order matters, verify that it is unchanged.
  7. Split a two-dimensional array by rows if needed. For a source range A2:D11, enter =TAKE(A2:D11,4) in an empty area to return its first four rows, including all four columns. In another clear area, enter =DROP(A2:D11,4) to return the remaining rows. The number in these formulas is a row count because the range has multiple columns.
  8. Split a two-dimensional array by columns if needed. To keep the first two columns of A2:D11, enter =TAKE(A2:D11,ROWS(A2:D11),2) in an empty area. To return the columns after those first two, enter =DROP(A2:D11,0,2) elsewhere. In TAKE, the second argument sets the row count and the third sets the column count; ROWS(A2:D11) keeps all the rows. In DROP, the second argument drops zero rows and the third drops two columns.
  9. Adjust or reuse the split. If the boundary changes, edit the count in both formulas. You can replace the range reference with another range or use a cell reference for the split count. For instance, put the desired count in H1 and use =TAKE(A2:A11,H1) and =DROP(A2:A11,H1). Updating H1 then changes both outputs. Keep H1 as a whole number from zero through the number of values in the source.

On Excel for Mac, the same TAKE and DROP formulas work in Microsoft 365 or Excel 2024. Enter them in worksheet cells just as you would on Windows; the ribbon layout can differ slightly, but no special menu command is needed. Excel for the web also supports dynamic arrays, though available functions can depend on the account and rollout. If Excel displays an unrecognized-function error, check your version and update it before relying on these formulas.

Common Mistakes

  • Using a row number instead of a count. TAKE and DROP count from the beginning of the selected array. If the range starts at A2, the fourth item is on worksheet row 5, but the split count is still 4.
  • Using different split counts. If TAKE keeps four values while DROP removes five, one value is absent from the combined outputs. If DROP removes three, the outputs repeat a value. Use the same boundary in both formulas.
  • Expecting a formula to divide the original cells in place. These functions create output arrays; they do not move or delete the source data. Put the formulas in separate, empty areas and keep the original range intact until you have checked the results.
  • Leaving content in the spill area. A value, another formula, or even a merged cell in the result area can stop Excel from displaying the full array. Clear the obstructing cells or move the formula to a larger empty area.
  • Splitting a range that includes extra rows. If the selected range includes a heading, blank rows, or a total, those cells count as part of the array. Select only the values you actually want to split, or deliberately include those rows in your plan.
  • Confusing row and column splits. In a vertical list, the count normally refers to rows. In a horizontal list, it refers to columns. For a rectangular range, TAKE and DROP can use separate row and column arguments, so check which dimension you intend to divide.

Tips

  • Use a cell for a changeable boundary. Referencing a split count stored in a cell makes it easy to test different group sizes without editing multiple formulas. Keep the value within the source array’s size.
  • Place outputs side by side when comparing them. Leave at least one blank column between the source and outputs, or between output groups, so it is easy to see where each result starts and spills.
  • Use the same source reference in both formulas. This makes the two parts easier to audit. If your source range may move, select it while building each formula or use a named range to make the formulas more readable.
  • Remember that spilled results update. If the source values change, Excel recalculates the formulas. This is helpful for live reports, but it also means the results are not a fixed copy. To keep a snapshot, copy the output and use Paste Special > Values.
  • Consider whether you mean splitting text rather than dividing a list. If one cell contains text separated by commas or another delimiter, that is a different task from splitting an array of values. In Microsoft 365 or Excel 2024, TEXTSPLIT can separate delimited text; choose the delimiter carefully so commas inside quoted or otherwise meaningful text are not treated as separators.

Troubleshooting

Excel shows #SPILL!: Click the formula cell. Excel may outline the cells blocking the result. Clear those cells, unmerge any cells in the spill area, or move the formula to an unobstructed location. Also check that the output does not extend beyond the worksheet edge.

Excel shows #NAME? or says the function is not valid: Your Excel version may not include TAKE and DROP. These functions require Microsoft 365 or Excel 2024. Update Microsoft 365, open the workbook in a supported version, or use a fill-down approach with INDEX in an older version. If your Excel uses localized function names, use the names recognized by that installation.

The second result is empty: Check that the split count is smaller than the number of items in the source. If you drop all values, nothing remains to display. Also verify that the source range is correct and that it contains data.

The outputs do not add up to the original list: Count the source values and each output separately. Ensure both formulas point to the same source range and use the same split count. Watch for a header or trailing blank cells included in one selection but not the other.

The formula returns an unexpected shape: For a rectangular array, check whether you supplied a row count, a column count, or both. TAKE with one count on a multi-column range keeps that many rows; it does not automatically keep that many individual cells. Use the third argument when you need to specify columns.

You need fixed values rather than live results: Once the split looks correct, select the spilled output, copy it, then use Home > Paste > Paste Special > Values in Excel for Windows. On Mac, use Home > Paste > Paste Special > Values if that command is available in your ribbon, or open the Paste Special dialog. This replaces formulas with their current results, so retain a copy of the source if you may need to revise the split.

Frequently Asked Questions

Can I split an array into more than two parts?
Yes. Use TAKE for the first portion and DROP to remove the items already assigned, then apply TAKE and DROP again to the remaining array. For example, a list can be divided into three consecutive groups by setting a boundary for the first group and another boundary for the next group. Keep track of cumulative counts so every item appears once.

Can I split an array into equal-sized groups?
Yes, but TAKE and DROP divide at a chosen boundary; they do not automatically generate a set of separate outputs for every group. Calculate the group boundaries first, then create a pair of formulas for each section. If the item count is not evenly divisible, decide whether the final group should be shorter or whether some groups should differ by one item.

Will the original array change?
No. TAKE and DROP return calculated results in other cells. The source remains as it was. Editing or deleting the source can change the spilled outputs because the formulas recalculate.

Can I split an array horizontally?
Yes. For a one-row array, TAKE and DROP use the column count to keep or remove values from the left. For a multi-row range, use the column argument to split across columns while retaining the rows, as shown in the steps above.

Can I use this method with a formula-generated array?
Yes, if the source formula returns an array that Excel can pass to TAKE or DROP. You can refer to a spilled result with its starting cell followed by the spill operator, such as A2#. Check that the formula’s output is stable and that there is room for the new results to spill.

What if I want each piece in a separate worksheet?
Enter each splitting formula in the desired location on its target worksheet and refer to the original source range. Make sure the source sheet and range are referenced correctly, and leave enough empty cells around each formula for the results to spill.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.