Zoho Sheet DROP: Exclude Edge Rows, Columns, Headers and Totals
Exclude rows and columns in Zoho Sheet with positive and negative counts, complete layouts and explicit header/footer removal examples.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
=DROP(A2:C9;2) returns A2:C9 after excluding its first two rows. Zoho Sheet DROP returns a reduced array in its own result area; it does not delete source cells. Negative counts exclude rows from the bottom or 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 DROP entry. Layouts and numerical examples were independently calculated; no native Zoho Sheet execution was performed.
Syntax and the edge to exclude
DROP(array; rows; [columns])
The source array and row count are required; the column count is optional. The official entry demonstrates a row-only call and a call that removes both top rows and right-hand columns.
Positive counts exclude from the top or left, and negative counts exclude from the bottom or right. TAKE retains an edge slice instead. The function names describe complementary selection tasks, not edits to the original table.
Exclude different edges from eight records
Create StockWindows, reserving A1:T12. Enter Batch, Received and Used in A1:C1 and these complete fictitious inputs in A2:C9:
| Row | A: Batch | B: Received | C: Used |
|---|---|---|---|
| 2 | S1 | 8 | 5 |
| 3 | S2 | 9 | 7 |
| 4 | S3 | 10 | 8 |
| 5 | S4 | 11 | 9 |
| 6 | S5 | 12 | 11 |
| 7 | S6 | 13 | 10 |
| 8 | S7 | 14 | 12 |
| 9 | S8 | 15 | 13 |
Leave E2:G7, I2:K7, M2:N7 and Q2:R7 empty. Enter E2 =DROP(A2:C9;2) and I2 =DROP(A2:C9;-2).
E2:G7 contains S3,10,8 / S4,11,9 / S5,12,11 / S6,13,10 / S7,14,12 / S8,15,13. I2:K7 contains S1,8,5 / S2,9,7 / S3,10,8 / S4,11,9 / S5,12,11 / S6,13,10.
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 six retained records keep their original order. Dropping the last two rows does not return those two rows; it returns the six rows that remain.
Exclude rows and columns in one call
In M2 enter =DROP(A2:C9;2;-1). It removes the first two rows and the last column, giving M2:N7 = S3,10 / S4,11 / S5,12 / S6,13 / S7,14 / S8,15.
In Q2 enter =DROP(A2:C9;-2;1). It removes the bottom two rows and the first column, giving Q2:R7 = 8,5 / 9,7 / 10,8 / 11,9 / 12,11 / 13,10.
A negative column count means remove from the right. It does not mean return the right-hand columns. When labels are excluded, retain a clear connection to the source records instead of treating the remaining numbers as self-describing.
Calculate totals from the retained rows
Enter these formulas in T2:T7:
| Cell | Formula | Expected value |
|---|---|---|
| T2 | =SUM(F2:F7) |
75 |
| T3 | =SUM(G2:G7) |
63 |
| T4 | =T2-T3 |
12 |
| T5 | =SUM(J2:J7) |
63 |
| T6 | =SUM(K2:K7) |
50 |
| T7 | =T5-T6 |
13 |
The totals describe different retained sets, which overlap in S3:S6. They should not be added together as if those sets were disjoint.
Copy StockWindows as ChangedBatch. Change only B8, S7’s Received value, from 14 to 140. F6 and N6 become 140. T2 becomes 201 and T4 becomes 138, while T3 stays 63. The I:K and Q:R views exclude S7, so T5:T7 remain 63,50,13. This is a direct check of which records each formula retains.
Advanced: remove a header and total row explicitly
Create HeaderFooter, reserving A1:K13. Enter the same A1:C9 dataset, including its original header. In A10 type Total; in B10 enter =SUM(B2:B9) and in C10 enter =SUM(C2:C9). These source totals are 92 and 75.
Leave E2:G9 empty. In E2 enter:
=DROP(DROP(A1:C10;1);-1)
The inner function removes the first row, which is the header. Its nine-row result still includes Total; the outer function removes that last row. E2:G9 therefore contains exactly the original eight data records S1 through S8, without deleting A1 or A10.
I2 =SUM(F2:F9) is 92, and I3 =SUM(G2:G9) is 75. I4 =I2-I3 is 17. By contrast, K2 =SUM(B2:B10) is 184, and K3 =SUM(C2:C10) is 150, because those source ranges include their own total row as an additional value.
SUM correctly adds the selected numbers; it cannot know that one of them is already a total. Excluding a footer is part of the source-selection policy.
On a separate HeaderOnly copy of the original StockWindows, clear E2:T9, leaving A1:C9 intact. Enter =DROP(A1:C9;1) in E2. Its eight-row E2:G9 result contains S1:S8 with all three fields. Including the header in the source is why dropping one row retains all eight original records.
The header/footer formulas assume that the first and last rows actually have those roles. If an export inserts a second title row or omits its total, a fixed count can remove the wrong records. Inspect the boundaries before reusing the pattern.
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.