Zoho Sheet TAKE: First, Last and Middle Array Windows
Extract contiguous rows and columns in Zoho Sheet with signed counts, complete spill layouts, changed-source checks and middle-window examples.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
=TAKE(A2:C9;3) returns the first three rows of A2:C9. Zoho Sheet TAKE extracts a contiguous rectangle from the beginning or end of an array. A negative row count takes rows from the bottom; a negative column count takes columns from the right.
Availability and version history
- Introduced: Named in the seven-function launch linked from Zoho’s November 2022 release history. No numbered cloud release is given.
- Availability: Documented in the current Zoho Sheet rolling web-service help; no final supported-version endpoint is established.
- Scope: Mobile, offline and imported-workbook parity remain separately unverified.
- Checked: October 14, 2026, against the official TAKE entry. Layouts and numerical examples were independently calculated; no native Zoho Sheet execution was performed.
Syntax and signed counts
TAKE(array; rows; [columns])
The array and row count are required. Columns is optional; Zoho’s row-only example retains all source columns. The official entry also illustrates taking the first row and last two columns with TAKE(array;1;-2).
A count of -3 means take three rows from the bottom, not select a row labeled -3. DROP excludes edge rows or columns instead of retaining them.
Compare the first and last three weeks
Create WeekWindows, reserving A1:P12. Enter Week, Planned and Actual in A1:C1, then this complete fictitious dataset in A2:C9:
| Row | A: Week | B: Planned | C: Actual |
|---|---|---|---|
| 2 | W1 | 10 | 12 |
| 3 | W2 | 12 | 11 |
| 4 | W3 | 14 | 16 |
| 5 | W4 | 16 | 15 |
| 6 | W5 | 18 | 20 |
| 7 | W6 | 20 | 19 |
| 8 | W7 | 22 | 25 |
| 9 | W8 | 24 | 23 |
Leave E2:G4, I2:K4, M2:N2 and M5:N6 empty. Enter =TAKE(A2:C9;3) in E2 and =TAKE(A2:C9;-3) in I2.
| Output row | E: Week | F: Planned | G: Actual | I: Week | J: Planned | K: Actual |
|---|---|---|---|---|---|---|
| 2 | W1 | 10 | 12 | W6 | 20 | 19 |
| 3 | W2 | 12 | 11 | W7 | 22 | 25 |
| 4 | W3 | 14 | 16 | W8 | 24 | 23 |
Zoho’s dynamic-array instructions specify one formula in its anchor followed by Enter. Leave the whole destination rectangle empty; do not fill duplicate formulas across it. An obstruction produces #SPILL!. Keep separate views far enough apart that they cannot overlap.
The source excludes the header, so neither result contains Week/Planned/Actual as a data row. The bottom slice keeps the original W6,W7,W8 order; it does not reverse the records.
Select the right-hand numeric fields
In M2 enter =TAKE(A2:C9;1;-2). M2:N2 contains 10,12, the first record’s Planned and Actual fields.
In M5 enter =TAKE(A2:C9;-2;-2). M5:N6 contains 22,25 / 24,23, the last two records and last two fields. The row and column signs act independently. This view omits the week labels, so keep the original table nearby when the record identity matters.
Summarize each window without mixing it with the full dataset
Enter these formulas separately in P2:P9:
| Cell | Formula | Expected value |
|---|---|---|
| P2 | =SUM(F2:F4) |
36 |
| P3 | =SUM(G2:G4) |
39 |
| P4 | =P3-P2 |
3 |
| P5 | =AVERAGE(G2:G4) |
13 |
| P6 | =SUM(J2:J4) |
66 |
| P7 | =SUM(K2:K4) |
67 |
| P8 | =P7-P6 |
1 |
| P9 | =AVERAGE(K2:K4) |
22.3333333333… |
SUM and AVERAGE operate on the selected numeric cells. The latest three actual values are 19,25,23, not the three largest values in the full column: the earlier value 20 exceeds the selected 19. TAKE does not rank the data.
Copy WeekWindows as ChangedWeek. Change only C8 from 25 to 250. K3 and N5 become 250. P7 becomes 292, P8 becomes 226, and P9 becomes 97.3333333333…. The first-window P2:P5 and last-window Planned total P6 remain unchanged. A slice stays connected to the source; it is not a frozen snapshot.
Advanced: take a middle window after excluding an edge
Create MiddleWindows, reserving A1:O12, and enter the same original A1:C9 dataset. Leave E2:G4 and I2:K4 empty. Enter:
E2: =TAKE(DROP(A2:C9;2);3)
I2: =TAKE(DROP(A2:C9;-2);-3)
E2:G4 is W3,14,16 / W4,16,15 / W5,18,20. The inner DROP first removes W1/W2; TAKE then keeps the next three rows.
I2:K4 is W4,16,15 / W5,18,20 / W6,20,19. Removing W7/W8 first makes W6 the new bottom edge, from which the outer TAKE selects three rows.
M2 =SUM(F2:F4) is 48, M3 =SUM(G2:G4) is 51, and M4 =M3-M2 is 3. O2 =SUM(J2:J4) and O3 =SUM(K2:K4) are both 54; O4 =O3-O2 is 0.
These windows overlap, so adding their totals would count W4 and W5 twice. The composition describes a slice, not a disjoint partition. CHOOSEROWS serves a different task when you need explicitly selected nonadjacent positions.
Documented errors and remaining boundaries
The function-specific help lists #VALUE! for an empty-array case described as a rows or columns argument of zero. These examples avoid zero arguments; do not assume a zero means “leave this dimension alone.” Omit the optional columns argument when you want the documented row-only form.
The entry also lists incorrect argument types, invalid references and #NUM! for an array that is too large, without a numerical size threshold. It does not fully describe fractional counts, counts beyond the source dimension, removal of an entire dimension, formula-empty strings or embedded errors. Their exact output and error rendering remain separately unverified.
Use the sign to choose an edge and the magnitude to choose a count. Keep the source boundary, headers and any totals explicit. A contiguous slice is a positional operation; it does not filter by a condition, sort records or prove that a selected sample is representative.