Excel KPI Card Layout: Automatic Placement with WRAPROWS, VSTACK, and SPARKLINE

Standardize your Excel KPI card layout with dynamic array spills (#), WRAPROWS, VSTACK, and SPARKLINE, and it will automatically update and arrange itself as your data grows. You can reproduce the example below immediately by copying and pasting it.

Quick Fix: Complete in 10 Minutes (4 Card Types)

1. Convert the source data into a table (Sales) with Ctrl+T.
2. Create a daily total spill (Date|Revenue|Orders) in J2.
3. Create four 2-column KPI cards in K2 with WRAPROWS.
4. Create Revenue/Orders SPARKLINEs in L2.
5. Automatically arrange a 2×2 grid from O2 with VSTACK/HSTACK.

Daily Total Spill (J2)

=LET(
 d, SORT(UNIQUE(Sales[Date])),
 rev, MAP(d, LAMBDA(x, SUM(FILTER(Sales[Revenue], Sales[Date]=x)))),
 ord, MAP(d, LAMBDA(x, SUM(FILTER(Sales[Qty],     Sales[Date]=x)))),
 HSTACK(d, rev, ord)
)

Four KPI Cards (K2)

=LET(
 totalRev, SUM(Sales[Revenue]),
 totalOrd, SUM(Sales[Qty]),
 aov, IF(totalOrd=0, 0, totalRev/totalOrd),
 end, TODAY(),
 cur7, SUM(FILTER(Sales[Revenue], Sales[Date] > end-7)),
 prev7, SUM(FILTER(Sales[Revenue], (Sales[Date] <= end-7)*(Sales[Date] > end-14))),
 delta, IF(prev7=0, 1, (cur7-prev7)/prev7),
 flat, {"Revenue", totalRev, "Orders", totalOrd, "AOV", aov, "7d vs prev", delta},
 WRAPROWS(flat, 2, "")
)

Sparklines (L2)

=LET(
 r, INDEX(J2#, , 2),
 o, INDEX(J2#, , 3),
 HSTACK(SPARKLINE(r), SPARKLINE(o))
)

Automatic Grid Placement (O2)

=LET(
 base, KPI_Base,
 names, TAKE(base,,1),
 vals,  TAKE(base,,2),
 spark, L2#,
 VSTACK(
   HSTACK(INDEX(names,1), INDEX(vals,1), INDEX(spark,1), ""),
   HSTACK(INDEX(names,2), INDEX(vals,2), "", ""),
   HSTACK(INDEX(names,3), INDEX(vals,3), "", ""),
   HSTACK(INDEX(names,4), INDEX(vals,4), "▲/▼", "")
 )
)

Troubleshooting

Conclusion

You can automatically arrange an Excel KPI card layout using only WRAPROWS, VSTACK, and SPARKLINE. The next post continues with Power Query basics.