How to Use HSTACK and VSTACK to Unpivot Summary Cards in Microsoft Excel

Corporate reporting packages and executive dashboard templates often present data in formatted “metric cards”—isolated summary boxes where metrics like regional revenue, headcount, and budget variances sit arranged in attractive but non-tabular layouts. Transforming dozens of these visual summary cards into a normalized database table for charting or Power BI imports historically required tedious copy-pasting or manual cell referencing. By combining modern Excel’s HSTACK and VSTACK dynamic array functions, you can unpivot summary cards in a single formula.

Understanding Card Unpivoting Strategy

Unpivoting summary cards requires two distinct geometric operations:

• Horizontal Flattening (HSTACK): Extracting disparate attribute cells from an individual card (such as card title in A1, metric value in B3, and growth percentage in C5) and binding them together horizontally as a single cohesive row.
• Vertical Stacking (VSTACK): Taking multiple independently flattened rows and stacking them vertically top-to-bottom into a continuous database table.
• Header Normalisation: Prepending a static array of column names (e.g., {"Department", "Metric", "Variance"}) at the top of the dynamic stack.

Unpivoting Multi-Card Summaries into Tabular Format

Consider three executive summary cards located across your worksheet (Finance Card in B2:D5, Operations Card in B7:D10, and Sales Card in B12:D15):

1. Select an empty destination cell in a summary reporting sheet (such as F2).
2. Enter the unpivoting formula:

   =VSTACK(
       {"Department", "Quarterly Revenue", "Growth Rate"},
       HSTACK(B2, D3, D5),
       HSTACK(B7, D8, D10),
       HSTACK(B12, D13, D15)
   )

3. Press Enter. Excel immediately spills a standardized three-column database table.
4. Because structured cell coordinates maintain dynamic references, any updates to the original visual cards instantly reflect in the normalized table without manual intervention.

Scaling with LAMBDA and MAP for Repeated Grid Layouts

When cards are arranged at regular vertical intervals (e.g., every 5 rows), streamline authoring using REDUCE and INDEX:

=REDUCE(
    {"Region", "Revenue", "Margin"},
    {0, 1, 2},
    LAMBDA(acc, i,
        VSTACK(acc, HSTACK(INDEX(B2:B20, i*5 + 1), INDEX(D2:D20, i*5 + 2), INDEX(D2:D20, i*5 + 4)))
    )
)

Mastering HSTACK and VSTACK unpivoting eliminates manual restructuring and maintains a clean boundary between presentation dashboards and analytical data tables in Excel.