VSTACK Function in Excel

What this does

VSTACK appends ranges vertically and HSTACK joins them side by side. VSTACK is the one that changes how workbooks are built: consolidating a sheet per month into one analysis table used to mean Power Query or a macro, and is now one formula that stays live as each sheet is updated. Ranges of unequal width are padded with #N/A rather than refusing, which is a signal worth heeding — it usually means a column is missing from one of the sources. Wrapping the result in SORT or FILTER gives a consolidated, sorted, filtered view in a single cell. Excel 365 only. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “vstack function in excel”. Start on a copy or a tiny sample, keep the affected formula visible, and compare the result with the tool above before you touch the real workbook. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. The point is a calculation you can defend to a CFO or an auditor, but the practical win is that someone else can open the file and understand what happened without asking you.

A worked example

Consolidating twelve monthly sheets: =VSTACK(Jan:Dec!A2:D100) stacks every one of them live. Adding a computed column beside a range: =HSTACK(A2:C50, C2:C50*0.2). Consolidated and deduplicated in one step: =UNIQUE(VSTACK(Sheet1!A2:A100, Sheet2!A2:A100)). VSTACK collapses the sheet-per-month consolidation problem into one live formula, which previously required Power Query or a macro. A practical tip before you scale it up: build it once on a small block of test data, confirm the number against the tool on this page, and only then point it at your real sheet. That one habit catches almost every mistake while it is still cheap to fix, long before a wrong figure reaches a report or a colleague.

In Google Sheets

Excel's 3-D references that span several sheets, such as =SUM(Jan:Dec!B4), are not supported in Google Sheets; there you add the sheets individually, for example =SUM(Jan!B4, Feb!B4, Mar!B4). Some functions on this page are newer additions to Excel: they are in current Microsoft 365 and Excel for the web, while older perpetual Excel versions return #NAME?. Google Sheets maintains its own function list, so confirm each function exists there before relying on the same formula. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. Here is the takeaway for “vstack function in excel”: copy the answer if you are busy, but if you have a spare few minutes, rebuild the example in Excel yourself with the tool above open beside it. That single pass — type it, run it, watch the result move when you change an input — is what turns a formula you found into a technique you trust. Keep your inputs labelled and referenced, never hard-coded, and the same sheet stays correct and auditable as it grows. Done that way, you will not need to look this up again, and you will be the person others ask.

Common mistakes

• Stacking ranges of different widths, which pads with #N/A — usually a sign a source is missing a column.
• Including header rows from every sheet, which scatters them through the result; DROP them first.
• Using them in Excel 2021 or earlier, where they do not exist.

Frequently asked questions