Create a Dynamic Long-to-Wide Table in Excel with MAKEARRAY
Question details
Create a dynamically expanding wide summary table from long-format Excel data when the row and column labels can change.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The source table contains product names, categories, and prices, and the user wants a formula-driven cross-tab summary with dynamic headers.
- Observed behavior
- The desired result is a spilled table with unique products down the rows, unique categories across the columns, and the summed price at each intersection without #CALC! errors.
Place the source data in consistently sized ranges or Excel Table columns named product, category, and prices. Confirm that product and category values do not contain unintended blank entries, because blanks can create unwanted spilled headers.
Build a Dynamic Wide Table with UNIQUE, MAKEARRAY, and SUMIFS
Use this approach when the product and category lists may grow or change and you need a two-dimensional summary that spills automatically.
This formula creates unique vertical and horizontal label lists, generates every row-column intersection with MAKEARRAY, and then adds the headers with VSTACK and HSTACK.
Select the Product, Category, and Price columns, then use Formulas > Name Manager > New to create the names product, category, and prices. You can also use corresponding Excel Table column references in the formula.
Click an empty cell with enough free space to the right and below it for the spilled result, such as G2. Do not place values in the intended spill range.
Enter the following formula and press Enter: =LET(proList,TOCOL(UNIQUE(product),1),catList,TOROW(UNIQUE(category),1),VSTACK(HSTACK("Sum of Price",catList),HSTACK(proList,MAKEARRAY(ROWS(proList),COLUMNS(catList),LAMBDA(r,c,SUMIFS(prices,product,INDEX(proList,r),category,INDEX(catList,c)))))))
Verify that each product appears once in the first column, each category appears once in the top row, and each body cell returns the sum of prices matching that product and category.
Use a Shorter SUMIFS Spill Formula for the Same Summary
Use this alternative when your Excel version supports dynamic arrays and SUMIFS can evaluate the vertical and horizontal criteria arrays directly.
Create an Expanding Long-to-Wide Summary in WPS Spreadsheet
WPS Spreadsheet can help you build a formula-driven summary table from long-format records. Use dynamic lists for products and categories, then calculate each intersection with SUMIFS to keep the report aligned with changing source data.
- 1. Open the source workbook: In WPS Spreadsheet, select Menu > Open and open the Excel workbook containing the product, category, and price data.
- 2. Prepare the source columns: Make sure the Product, Category, and Price columns have clear headers and contain no unintended blank label rows.
- 3. Create unique row and column labels: In an empty worksheet area, use UNIQUE with TOCOL for product labels and UNIQUE with TOROW for category labels, where supported by your WPS Spreadsheet version.
- 4. Calculate the intersection totals: Enter a SUMIFS formula that uses the product label as the row criterion and the category label as the column criterion, then fill or spill the formula across the summary area.
Frequently Asked Questions
Why does my dynamic long-to-wide formula return #CALC! in Excel?
#CALC! can occur when an array function receives an empty or incompatible array. Use TOCOL(UNIQUE(product),1) and TOROW(UNIQUE(category),1) to ignore blanks, and verify that the product, category, and prices ranges have matching row counts.
How do I prevent a #SPILL! error in the output table?
Select the formula cell and check the outlined spill range. Clear any values, merged cells, or table content blocking the cells where Excel needs to place the dynamic results.
Can I use Excel Table references instead of named ranges?
Yes. Replace product, category, and prices with structured references such as Table1[Product], Table1[Category], and Table1[Price]. Excel Tables expand automatically when new records are added.
What happens when a product has no price in a category?
SUMIFS returns 0 when no records match the product and category criteria. If you prefer blanks, wrap the result logic in an IF test that converts zero values to an empty string.
Do BYROW and BYCOL have to be used to create this table?
No. MAKEARRAY with SUMIFS is a direct way to generate the two-dimensional body. A SUMIFS formula using vertical product criteria and horizontal category criteria can also spill the complete matrix without BYROW or BYCOL.
