Spreadsheet Practice

Lesson 30 of 45, Dynamic arrays

VSTACK & HSTACK

Quick answer

Combine tables and calculated columns without copy and paste.

Key formula
=VSTACK(A2:C5,E2:G5) (Two tables, one under the other)
Pro9 minutes, 3 exercisesVSTACKHSTACK

Watch it done

2 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

Two stores export the same report. To analyse them together you used to copy one under the other and hope nobody edits the originals. VSTACK(range1, range2) stacks tables vertically into one live list: change either source and the combined table follows.

HSTACK joins side by side. Use it to put a name column next to a calculated column in one spill, such as product and revenue, without writing the calculation in a separate column first: HSTACK(A2:A5, B2:B5*C2:C5).

The result is an ordinary array, so you can wrap it in SORT, FILTER or UNIQUE. Stack first, then sort or de-duplicate the whole.

Syntax

=VSTACK(A2:C5,E2:G5)Two tables, one under the other
=SORT(VSTACK(A2:C5,E2:G5),2,-1)Combined, sorted by column 2
=HSTACK(A2:A5,B2:B5*C2:C5)Names beside a calculated column
=UNIQUE(VSTACK(A2:A5,E2:E5))Every product from both lists, once

Watch out for

  • VSTACK pads short tables with #N/A when column counts differ. Stack ranges with the same number of columns.
  • Leave room for the spill: it grows when you add rows to a source.
  • Headers are just rows. Leave them out of the ranges you stack unless you want them in the middle.

The exercises

  1. Both stores in one list. Starting in A8, stack the North store rows (A2:C5) on top of the South store rows (E2:G5) into one list.
  2. Combined, best sellers first. In E8, return the same combined list sorted by units, largest first.
  3. Product and revenue. In I2, return the North store product names beside their revenue (units × price) in one formula, with no helper column.

Questions people ask about this

Updated Checked by running: every exercise answer on this page was graded by our formula engine on .

Graded practice for this lesson is part of Pro

The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 43 Pro lessons, Excel and Google Sheets, 125 exercises in all.

  1. Both stores in one list
  2. Combined, best sellers first
  3. Product and revenue