Skip to content
HOW TO

Build a Pivot Style Grid

Goal

Build a pivot-style summary from dynamic table data with U formulas.

Prerequisites

  • Runline U Add-in installed in Excel.
  • A table-like data range with label columns and value columns.
  • A clear output area for the generated grid.

Steps

1. Retrieve or Stage the Source Table

Use a direct range, a cached report, or U.GET output as the source table.

=U.GET(report_group, report_name)

2. Sort or Filter First

Apply row-level filtering or sorting before converting to a grid.

=U.VSORT(source_table,{"Time",-1;"Strategy",1})

3. Convert the Table to a Grid

Use U.TABLE_TO_GRID to aggregate duplicate row/column intersections.

=U.TABLE_TO_GRID(sorted_table,"trunc(Time),Strategy","Broker","PnL",,TRUE)

4. Keep Choices Visible

Put row-label, column-label, value-field, and filter choices in cells so users can change the report without editing formulas.

Result

The workbook displays a dynamic pivot-style grid that can respond to live source data and formula-driven report choices.

Common Variations

  • Use U.VFILTER before the grid conversion.
  • Use multiple row label levels for drill-down-style summaries.
  • Use U.CACHE when the source table is expensive to retrieve or transform.

Troubleshooting

  • If the grid shape changes unexpectedly, inspect the distinct row and column labels in the source data.
  • If totals differ from Excel pivot tables, confirm which fields are being grouped and whether dates/times are transformed before aggregation.
  • If the output is slow, cache the staged table or reduce the source range before converting it.