'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Convert Customer Order Data to a Pivot-Style Table in Excel
Question details
The user needs to transform customer order data into a normalized, pivot-style table using dynamic formulas while retaining elements like the Customer PO.
- Product
- Microsoft Excel
- Device & OS
- Mac
- Scenario
- Transforming multi-column customer order data (including POs and product codes) into a flat, normalized table structure for better analysis.
- Observed behavior
- The data needs to be stacked and flattened correctly using functions like HSTACK, INDEX, SEQUENCE, and TOCOL, while dynamically repeating corresponding row identifiers.
Ensure you are using a modern version of Excel (Microsoft 365 or Excel 2021+) that supports dynamic array functions. On a Mac, verify your app is fully updated via Microsoft AutoUpdate to ensure these functions are available.
Use Dynamic Array Formulas to Flatten Data
Apply a combination of dynamic array functions to automatically reshape the customer order data without manual copying and pasting.
This formula combines HSTACK, INDEX, SEQUENCE, and TOCOL to extract and reshape the multi-column data into a single, flat table. It systematically repeats the Customer PO and aligns the product codes perfectly.
Click on the top-left blank cell where you want the new pivot-style table to begin generating.
Type the formula =HSTACK(INDEX(H2:H38,ROUNDUP(SEQUENCE(COUNTA(H2:H38)*5)/5,0)),INDEX(A2:A38,ROUNDUP(SEQUENCE(COUNTA(A2:A38)*5)/5,0)),INDEX(C1:G1,MOD(SEQUENCE(COUNTA(A2:A38)*5)-1,5)+1),TOCOL(C2:G38)) into the formula bar.
Modify the range references in the formula to match your specific dataset. For example, change H2:H38 for the POs, A2:A38 for customer names, C1:G1 for column headers, and C2:G38 for the actual values.
Press Enter to apply the formula. The data will automatically spill into the adjacent columns and rows, instantly forming a normalized table.
Transform and Normalize Data Easily with WPS Spreadsheet
WPS Spreadsheet provides powerful data handling tools, including robust support for array formulas, allowing you to seamlessly reshape complex order data into clean, normalized tables in seconds.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your existing customer order data spreadsheet.
- 2. Input the array formula: Select a blank destination cell and paste your flattening array formula, ensuring the cell references correspond to your data structure.
- 3. Calculate the array: Hit Enter. WPS Spreadsheet will instantly calculate the formulas and output your normalized pivot-style table.
Frequently Asked Questions
Why is my dynamic array formula returning a #NAME? error?
The #NAME? error usually occurs if your version of Excel does not support new dynamic array functions like HSTACK or TOCOL. You need an active Microsoft 365 subscription or Excel 2021+ to use these specific functions.
Can I normalize data without using complex formulas?
Yes, you can use the Power Query feature. Go to the Data tab, click 'From Table/Range', select the columns you want to unpivot in the Power Query Editor, and choose 'Unpivot Columns' to achieve a flat table structure without manual formulas.
What does the TOCOL function do in this specific formula?
The TOCOL function takes an entire two-dimensional array or range of data (such as multiple columns of product order codes) and systematically transforms it into a single vertical column, making it ideal for unpivoting data.
