Function Problems

Create a Spilled Excel Array from Row and Column Labels

John WilsonJohn Wilson Nov 18, 2026 870 views

Question details

Create a two-dimensional array that combines every column header with every row label.

Create a Spilled Excel Array from Row and Column Labels
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet has headers in one horizontal range and labels in a separate vertical range.
Observed behavior
The desired result is a spilled grid containing one combination for each row and column.
Before you start

Place the column headers in C2:G2 and the row labels in B3:B8, or adjust the formulas to match your ranges. Use a version of Excel that supports dynamic arrays and leave the intended spill area empty.

Solution 1Recommended

Combine the ranges with array broadcasting

Use a single formula to expand the horizontal headers and vertical labels into a grid.

1
Select an empty cell

Click H3, or another empty cell with room for a six-row by five-column result.

2
Enter the formula

Type =C2:G2&B3:B8 in the formula bar and press Enter. Excel combines each column header with each row label.

3
Check the spill area

Review the results in H3:L8. If Excel shows #SPILL!, clear any cells blocking that range.

Combine the ranges with array broadcasting
Change the separator: To put a space between each header and label, use =C2:G2&" "&B3:B8 instead.
Work with arrays in WPS Spreadsheets

Combine row and column labels in WPS Spreadsheets

Open the workbook in WPS Spreadsheets and use the same range-concatenation formula to create a grid when your WPS version supports dynamic array spilling.

  1. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 2. Check the source ranges: Place column headers in C2:G2 and row labels in B3:B8, or identify the equivalent ranges in your sheet.
  3. 3. Enter the formula: Select an empty cell such as H3, enter =C2:G2&B3:B8 in the formula bar, and press Enter.
  4. 4. Review the result: Check whether the formula spills into a six-row by five-column grid. If it does not, check your WPS version's dynamic array support and ensure the output cells are empty.
Combine horizontal headers with vertical labels in a single formulaOpen and edit Microsoft Excel XLSX and XLS filesWork in a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does MAP not create every row and column combination?

MAP applies a LAMBDA to corresponding array elements; it does not automatically produce a Cartesian grid. Use =C2:G2&B3:B8 or MAKEARRAY to generate each combination.

How do I add a separator between the labels?

In the output cell, use =C2:G2&" - "&B3:B8 to place a hyphen between each column header and row label.

What should I do if Excel displays #SPILL!?

Select the formula cell to inspect the intended spill range, then clear any occupied cells blocking it. Also check that the formula is not inside an Excel table.

Can I use different ranges for the headers and labels?

Yes. Replace C2:G2 with your horizontal header range and B3:B8 with your vertical label range. The result will have one column per header and one row per label.