Zoho Sheet EXPAND: Resize Arrays and Choose Safe Padding

• Introduced: Included in the fifteen-function launch linked from Zoho’s March 2023 release history. No numbered cloud release is specified.
• Availability: Listed in the current Zoho Sheet function help; no final supported-version endpoint is established.
• Scope: Current web-service documentation. Individual mobile clients, offline use and imported workbook behavior remain separately unverified.
• Checked: October 14, 2026, against the official EXPAND reference. The worked layouts and calculations were independently derived; no native Zoho Sheet test was performed.

EXPAND(array; rows; [columns]; [fill_with])

The official entry requires the source array and requested row count. Columns and fill_with are optional in its signature. It states that omitted fill_with uses #N/A; it does not explain the omitted-column default. All dimensioned examples below therefore supply both row and column counts explicitly.

Zoho’s fifteen-function announcement describes enlarging an array without losing its existing values and filling the extra cells with a chosen value. The examples use positive whole-number target dimensions at least as large as the source. Smaller, zero or fractional targets are outside their scope.

Create TextPadding, reserving A1:M10. Put headings in A1:B1 and enter these numbers in A2:B3:

Leave D2:F5 empty and put =EXPAND(A2:B3;4;3;"-") in D2.

Zoho’s dynamic-array instructions say to enter one anchor formula and press Enter. Its results occupy adjacent cells automatically. Leave the complete output area empty; an obstruction produces #SPILL!. Do not fill duplicate formulas across the intended result.

The independent expansion model retains the original two-by-two block at the upper-left of the result and fills the added positions:

There are 12 output positions, of which 4 contain the original numbers and 8 contain the text marker. The target is four rows total, not four additional rows.

Put =COUNT(D2:F5) in H2 for 4 and =AVERAGE(D2:F5) in H3 for 25. Zoho’s COUNT reference excludes text; its AVERAGE reference also ignores ordinary nonnumeric text. The hyphen is deliberately not a numeric string.

H4 =D2+E2+D3+E3 gives 100, independently reconstructing the total of the four retained numbers. H5 =4*3-2*2 gives 8, counting added positions without interpreting a text marker as a measurement.

On TextPadding, leave J2:L5 empty and put =EXPAND(A2:B3;4;3;0) in J2. The first two rows are 10,20,0 and 30,40,0; the final two rows are 0,0,0.

Put =COUNT(J2:L5) in J7 for 12 and =AVERAGE(J2:L5) in J8 for 8.3333333333, the rounded value of 100/12. J9 =SUM(J2:L5) gives 100. Both padded blocks contain the same four measured numbers, but the zero-padded block has eight extra numeric observations for these aggregate functions.

Use zero only when it has the intended meaning. If the added positions represent observations that have not occurred, including them as measured zeros can lower an average misleadingly. COUNT and AVERAGE make this difference visible.

Copy TextPadding as ChangedSource and change only B3 from 40 to 100. The corresponding retained result becomes 100 in E3 and K3. All padding positions keep their chosen markers.

The four-number total becomes 160. H2 remains 4, H3 becomes 40, J7 remains 12, J8 becomes 13.3333333333 (160/12), and J9 becomes 160. H5 remains eight because the dimensions did not change.

These controls distinguish a data change from a shape change. Do not replace the original source with the displayed padded result just to make the exercise look consistent; the formula references should remain visible.

Create ReportSlots, reserving A1:N10. Put text A, B and C in A2:A4. Leave D2:E6 and H2:K4 empty. Enter =EXPAND(A2:A4;5;2;"Pending") in D2 and =EXPAND(A2:A4;3;4;"Pending") in H2.

The first result is five rows by two columns:

The second is three rows by four columns:

Put =5*2-3*1 in M2 for 7 added positions and =3*4-3*1 in M3 for 9. More columns can create more padding even when the row count stays unchanged. Neither rectangle creates new source records or moves B and C into a horizontal sequence; TOROW has that different purpose.

On a separate DefaultPadding sheet, re-enter the original 10,20 / 30,40 block in A2:B3. Leave D2:F5 empty and enter =EXPAND(A2:B3;4;3) in D2. The reference documents unavailable-value errors in the eight padding positions when fill_with is omitted. Its prose writes #N/A while its generic error table uses #N/A!; these labels do not establish an exact native display spelling on every client. Keep this control separate from the text- and zero-padding summaries.

Padding is not a general missing-data repair: these examples concern positions added outside a fully populated source rectangle. They do not establish how an existing blank, formula-empty string or source error is represented. The short help page also does not define every smaller-size error or size limit. Use explicit supported dimensions, preserve the input, and inspect SUM or other downstream calculations according to the padding policy.