Normalize Date Columns in Excel with Dynamic Array Formulas
Question details
Convert multiple date columns into rows containing two text identifiers, the date column name, and its corresponding date.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A source table has Text 1 and Text 2 identifiers followed by multiple date columns that need to be normalized for analysis.
- Observed behavior
- The desired output has one row for each source record and date column, regardless of whether the number of source rows matches the number of date columns.
Arrange Text 1 and Text 2 in adjacent columns and identify the first and last date columns. For the formula method, use an Excel version that supports REDUCE, LAMBDA, VSTACK, HSTACK, DROP, and CHOOSECOLS.
Normalize Date Columns with a Dynamic Array Formula
Use this formula to spill one row per record and date column without assuming the row and column counts are equal.
The formula iterates over the date columns and stacks each column's values alongside the identifiers and its header. It uses the actual number of date columns and source rows.
This example assumes a nonempty Excel table named Table1, adjacent identifier columns named Text 1 and Text 2, and a contiguous date range from Date 1 through Date 5.
Select the source data and choose Insert > Table. In the Table Design tab, name the table Table1, or substitute your table's name in the formula.
In an empty area outside the source table, enter Text 1, Text 2, Date column, and Date in four adjacent cells.
In the cell below the first output header, enter: =LET(ids,Table1[[Text 1]:[Text 2]],dates,Table1[[Date 1]:[Date 5]],headers,Table1[[#Headers],[Date 1]:[Date 5]],DROP(REDUCE("",SEQUENCE(COLUMNS(dates)),LAMBDA(acc,i,VSTACK(acc,HSTACK(ids,IF(SEQUENCE(ROWS(dates))>0,INDEX(headers,1,i),""),CHOOSECOLS(dates,i))))),1))
Select the fourth output column and choose Home > Number Format > Short Date or another date format. Keep the spill area clear, and update the formula's header references if your table or date column names differ.

Unpivot Date Columns with Power Query
Use Power Query when you prefer a refreshable transformation without a long formula.
Work with Excel Tables in WPS Office
WPS Office is a free, lightweight alternative for working with spreadsheets in a familiar interface. Open existing Excel files to make migration easier; Excel-specific formulas and Power Query features may differ.

Frequently Asked Questions
Does the number of records have to equal the number of date columns?
No. The formula uses ROWS(dates) for the source record count and COLUMNS(dates) for the date column count, so the two counts can differ.
How do I include a new date column in the formula?
If the new column is beyond the current endpoint, replace Date 5 in both the dates and headers references with the new last date column's header.
Why does the formula show a spill error?
The output needs an empty area outside the source table. Clear cells blocking the intended spill range and enter the formula in a regular worksheet cell.
How do I update the Power Query output after editing the source table?
Choose Data > Refresh All in Excel. If you used Unpivot Other Columns, new source columns other than the two identifiers will also be unpivoted.
Why are blank dates missing from the Power Query output?
Unpivoting in Power Query omits null values. Check the source date cells before loading if you need to account for records with missing dates.




