HOME  /  FORMULAS & FUNCTIONS

VSTACK, HSTACK, TOCOL and TOROW: combine and reshape data with one self-updating formula and know when Power Query is the better tool

FAQ — What does this article answer?

Q: What do these four functions actually do?

A: They split the task of combining data into two. VSTACK and HSTACK assemble ranges: one stacks blocks on top of each other and the other places them side by side. TOCOL and TOROW reshape a rectangular block into a single column or row. Each function returns a spilled range that recalculates when its source changes.

Q: How is this different from copying and pasting or using a helper column?

A: Copy-pasting produces a static snapshot that becomes inaccurate the moment the source changes, whereas a helper column containing the formula =Sheet2!B2 is fragile and breaks when a row is inserted. These functions read live ranges instead, so the combined result is always current and never needs rebuilding.

Q: Can I really consolidate twelve monthly sheets with one formula?

A: Yes, with the formula =VSTACK(Jan, Feb, Mar, …), every monthly range is stacked into one long table. Add the next month's sheet and extend the formula to grow the consolidated table, no copying, no pasting and no macros required.

Q: Which Excel versions have this functions?

A: All of these functions are available in Microsoft 365 and Excel for the web. Unlike GROUPBY and PIVOTBY, they are also available in perpetual Excel 2024 for Windows and Mac. They are not in Excel 2021, 2019 or 2016, where they return #NAME?.

Q: When should I use for Power Query instead?

A: When combining many separate files or folders, when the data sources need cleaning before they can be joined, when the data is large or needs to be refreshed on a schedule, or when merging on a key rather than simply stacking. Part 8 draws the line.

For years, "combining data" in Excel involved one of a few manual tasks: copying a range and pasting it below the last one, repeating this process every month; creating a helper column of cross-sheet references to pull values together; or manually converting a rectangular block into a single list. Each of these methods only works once. However, none of them are self-maintaining — the copied data becomes outdated by the time the next entry is made, the helper column breaks when a row is moved and the reshaped data has to be redone from scratch whenever the source grows.

A family of functions introduced in 2022 removes the need for these manual processes. Together with the trim-and-pick companions VSTACK, HSTACK, TOCOL and TOROW, as well as the trim-and-pick companions TAKE, DROP, EXPAND, CHOOSEROWS and CHOOSECOLS, they turn combining and reshaping into a formula task. Like FILTER and SORT before them, they return a spilled range that refreshes automatically. This article is as much about a change in approach as it is about nine functions and, importantly, about recognising where that approach ends and Power Query begins.

Part 1: A family with two jobs: assembly and reshaping

It helps to consider the whole family in mind at once, because each function is a small, single-purpose tool that works with the others.

The four core functions are arranged by job: VSTACK and HSTACK assemble ranges, TOCOL and TOROW reshape a block into a line; and the trim-and-pick companions are below

Two functions assemble separate ranges into a larger one: VSTACK stacks them vertically and HSTACK places them horizontally. Two functions reshape a single block into one line: TOCOL pours it into a column and TOROW lays it out as a row. The remaining five trim and pick, they refine whatever the first four produce, keeping or removing edge rows, padding a block to a fixed size, or selecting and reordering specific rows and columns.

This is the same significance thread that runs through the modern Excel toolkit: a combined dataset can now be a live formula result rather than a pasted snapshot. This single change is what makes these functions worth learning — and it is also why the closing question of this article is "when" rather than "how", since a formula is not always the right tool for the job.

Part 2: The data we're working with

The examples below use three small monthly ranges, each defined as an Excel table named Jan, Feb and Mar.

January February March
Region Amount Region Amount Region Amount
North 12,400 North 15,300 North 8,900
South 6,200 South 9,750 South 4,600
East 3,300 East 7,800 East 11,100

There are two further ranges that support the horizontal examples: a single column of Region labels, and two separate columns of annual totals, Y2025 (30,000; 18,000; 12,000) and Y2026 (36,600; 20,550; 22,200). The latter is being each region's three-month total. The three monthly tables sum to 79,350, and every result below reconciles to that figure.

💡 Tip: Convert each range to a table with Ctrl+T and give it a clear name. =VSTACK(Jan, Feb, Mar) is far easier to read and maintain than a formula full of A2:B4 addresses, and each table automatically grows to include new rows. The reasoning behind this is set out in Why every dataset should be an Excel table.

Part 3: VSTACK, consolidate monthly data with one formula

This use case justifies the whole family: repetitive monthly consolidations that are still done by hand in so many workbooks.

Three colour-coded monthly tables flow through =VSTACK(Jan; Feb; Mar) into one combined nine-row table containing a total of 79,350

The syntax is as simple as it looks — a list of ranges to stack:

=VSTACK(array1, [array2], ...)

To consolidate the three months into one table:

=VSTACK(Jan, Feb, Mar)

The result is a nine-row range spanning the grid. There is no copying or pasting involved, and, most importantly, it is live. Add a South sale in February and the combined table updates instantly. Add an April table next month and simply extend the formula to =VSTACK(Jan, Feb, Mar, Apr). This is the modern alternative to the copy-and-paste consolidation method described in Combining data from multiple sheets into one.

To include a header row at the top, stack the header range with the bodies. Because a table reference can return its header, the formula reads cleanly:

=VSTACK(Jan[#All], Feb, Mar)     ' Jan's header and body, then the Feb and Mar bodies

There is one important rule: the ranges should have the same number of columns. If they do not, VSTACK pads the narrower ranges to the width of the widest range with #N/A rather than returning an error, this is useful to know and indicates that your source ranges have become misaligned.

Part 4: HSTACK, join columns without a helper column

While VSTACK builds downward, HSTACK builds across. It is the ideal tool for assembling a table from columns that are located separately on the sheet, eliminating the need for a helper column.

Three separate columns, Region, 2025 and 2026, joined by =HSTACK into one three-column table

=HSTACK(array1, [array2], ...)

To place the region labels and the two years of totals side by side:

=HSTACK(Region, Y2025, Y2026)

The three separate ranges are combined into a single, three-column table in real time. The old approach, using a helper column with references to Sheet2!B2 dragged down beside your data, or performing a manual paste is no longer necessary: there is nothing to drag and nothing to keep in sync.

HSTACK adjusts to the tallest range, so if the pieces differ in height, the shorter ones are filled with #N/A. Enclose the formula in an IFNA function to display those gaps neatly:

=IFNA(HSTACK(Region, Y2025, Y2026), "")

The mechanics of tidying these error values are covered in Handling errors with IFERROR and IFNA.

Part 5: TOCOL and TOROW, reshape a block into a line

The second task is reshaping. A rectangular block of values, such as a Region-by-Month matrix, often needs to be converted into a single list in order to be fed into the UNIQUE function, sorted, or used to drive a chart. TOCOL and TOROW do exactly that.

A three-by-three Region-by-Month matrix flattened by TOCOL into one column and by TOROW into one row

=TOCOL(array, [ignore], [scan_by_column])
=TOROW(array, [ignore], [scan_by_column])

Given the nine-cell matrix of monthly amounts:

=TOCOL(Grid)          ' all nine values in one column, read across each row in turn
=TOROW(Grid)          ' the same nine values in one row

Two optional arguments allow for greater precision. The ignore argument determines what is skipped: 0 keeps everything (the default), 1 ignores blanks, 2 ignores errors, and 3 ignores both, invaluable when flattening a block with gaps. The scan_by_column argument controls the direction of the scan. By default, both scan by row, but setting the argument to TRUE causes the scan to go down each column first.

The everyday payoff is composition. To turn a rectangular block into a clean, sorted list of distinct values in one formula:

=SORT(UNIQUE(TOCOL(Grid, 1)))     ' flatten, ignore blanks, dedupe, sort

TOROW is the natural way to transform a column of items into a horizontal header. The inverse operation, wrapping a single list back into a rectangular block of a chosen width or height, is handled by the related functions WRAPROWS and WRAPCOLS. The spill behaviour that all of these functions rely on is explained in Dynamic arrays with FILTER, SORT, UNIQUE and SEQUENCE.

Part 6: The trim-and-pick companions

Assembling and reshaping is only half the work; the other half is arranging the rows and columns exactly as you want them. Five tools handle that, turning a rough combination into a finished table.

Function Purpose Example
TAKE(array, rows, [cols]) Keep a number of rows or columns from the start (positive) or end (negative) =TAKE(sorted, 5) — the top five rows
DROP(array, rows, [cols]) Remove rows or columns from the start or end =DROP(range, -1) — drop a totals row at the foot
EXPAND(array, rows, [cols], [pad_with]) Pad an array out to a fixed size =EXPAND(block, 3, 2, 0) — pad to 3×2 with zeros
CHOOSECOLS(array, col1, ...) Return chosen columns, in any order =CHOOSECOLS(combined, 2, 1) — swap two columns
CHOOSEROWS(array, row1, ...) Return chosen rows, in any order =CHOOSEROWS(combined, 1, 4, 7) — pick specific rows

Two of these deserve a mention. DROP with a negative count is an effective way of removing a rogue totals row before stacking, =DROP(Jan, -1) returns the January table without its final row. EXPAND solves the problem of ragged data: if one month is missing a region, padding each block to a fixed three-row shape with =EXPAND(block, 3, 2, 0) keeps everything aligned when you stack. Because CHOOSECOLS and CHOOSEROWS accept indices in any order, including negative indices that count from the end, they can be used to reorder data as well as filter it.

Part 7: Putting it together, a self-updating consolidation

The functions are designed to nest, with the inner function passing its result to the outer function. This allows a single, readable expression to perform tasks that previously required a series of manual steps.

For example, suppose you want the three months to be consolidated, but with the columns in the order Amount, Region rather than Region, Amount.

=CHOOSECOLS(VSTACK(Jan, Feb, Mar), 2, 1)

Read from the inside out: VSTACK stacks the three months into one range. Then CHOOSECOLS returns column 2 followed by column 1. Alternatively, to publish the five largest sales across the whole quarter in order of size:

=TAKE(SORT(VSTACK(Jan, Feb, Mar), 2, -1), 5)

Here VSTACK consolidates the data, SORT orders the combined range in descending order by amount, and TAKE keeps the top five. Every part of this is dynamic: change a figure in any monthly table and the top-five list will re-ranks itself. When a nested expression becomes too long to read easily, the LET function lets you name each stage, see LET: cleaner, faster formulas.

Part 8: Choosing the right technology

This is the decision that the article has been building up to. While these functions are excellent, they are not the answer to every combining problem. Part of using them well is recognising their limitations.

A three-way decision card showing when to use stack formulas for in-workbook self-updating combines, Power Query for many files or heavy transformation, or copy-paste for genuine one-offs

The formula approach is the right choice when the sources are in the same workbook, the result must stay live and self-update, it will feed another formula, such as a dashboard figure, chart or further calculation, and the shapes are simple and consistent. As the result is a formula in a cell, this approach also fits naturally into a reviewed, auditable workbook.

Power Query is the heavier-duty alternative, and the right tool for the job when it outgrows a formula, such as when combining many separate files or an entire folder of monthly exports, cleaning or transforming sources before they can be joined, working with large data sets or a scheduled refresh, or merging tables on a key rather than simply stacking them. Whereas these formulas assemble tidy, open ranges, Power Query builds a repeatable pipeline that first ingests and reshapes messy external data; the preparation side of that is covered in How to clean messy data in Excel, and the scale story in Power Pivot and the data model. Copy-pasting remains perfectly fine for a genuine one-off, a handful of rows that will never change does not require a formula.

In terms of availability, this family has a better story than GROUPBY and PIVOTBY. All nine functions are present in Microsoft 365, Excel for the web and Excel 2024, which is available on both Windows and Mac. They are only absent from Excel 2021, 2019 and 2016, where they return #NAME?. If your workbook must open on one of these older versions, the fallback options are Power Query or manual consolidation; the full picture can be found in Excel 365 versus older versions.

Part 9: Common failure modes

Symptom Likely cause Fix
#NAME? or _xlfn.VSTACK Opened in Excel 2021 or older Use Power Query or a manual consolidation; see Part 8
#SPILL! A cell in the intended spill range is occupied Clear the cells below and to the right of the formula
#N/A filling part of the result Ranges of unequal width (VSTACK) or height (HSTACK) Align the shapes, or wrap in IFNA to hide the padding
#CALC! (empty array) TOCOL/TOROW returned nothing, e.g. every value ignored Check the ignore argument and the source range
Wrong column kept or reordered CHOOSECOLS/TAKE/DROP count from the result, not column A Recount from the first column of the combined range
Consolidation misaligned Monthly tables have drifted to different column counts Standardise the tables, or CHOOSECOLS each to a common shape
Slow workbook Whole-column references inside the stack Use table references, which cover only the data that exists

Summary

The real value of these nine functions lies in the fact that they make combining and reshaping data a formula task rather than a manual one.

The principle is the same as before: use the tool that produces a correct, self-maintaining result and be deliberate about when the volume or complexity of the data means that Power Query is the better option.

See also: Combining data from multiple sheets into one for the consolidation problem these functions solve, Dynamic arrays with FILTER, SORT, UNIQUE and SEQUENCE for the spill behaviour they rely on, Automating reports with dynamic arrays and tables for the self-updating report pattern, Handling errors with IFERROR and IFNA for tidying the #N/A padding, Why every dataset should be an Excel table for the structured references that keep these formulas stable, and Excel 365 versus older versions for the full availability picture.

This article is part of the helpme.safeoffice.de series, which provides practical guides on Excel functions, workbook modeling and data solutions. The series is aimed at businesses that want effective, maintainable tools that everyone can understand.