Create a Spilled Excel Array from Row and Column Labels
Question details
Create a two-dimensional array that combines every column header with every row label.

- 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.
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.
Combine the ranges with array broadcasting
Use a single formula to expand the horizontal headers and vertical labels into a grid.
Click H3, or another empty cell with room for a six-row by five-column result.
Type =C2:G2&B3:B8 in the formula bar and press Enter. Excel combines each column header with each row label.
Review the results in H3:L8. If Excel shows #SPILL!, clear any cells blocking that range.

Build the grid with MAKEARRAY
Use MAKEARRAY when you want to specify how each row and column combination is constructed.
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. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 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. Enter the formula: Select an empty cell such as H3, enter =C2:G2&B3:B8 in the formula bar, and press Enter.
- 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.

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.




