How to use Microsoft Excel’s VSTACK() function to combine multiple data sets

Have you ever received data that you had to consolidate manually? It’s tedious work and prone to errors. If the data sets share the same structure, you can use 3D referencing, but setup is tedious and prone to error. Thanks to Microsoft Excel’s new VSTACK() function, all you need is one function to combine several columns of data or even full data sets into one long list of data.

In this tutorial, I’ll show you how to use VSTACK() and review some of its flexible behaviors. Then, we’ll look at a simple but common real-life use.

SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)

I’m using Microsoft 365 on a Windows 10 64-bit system. Excel for the web supports VSTACK(), but for now, VSTACK() is available only via Microsoft’s Office Insider Beta channel. This program provides early access to Office subscription features through two channels:

• Beta Channel
• Current Channel (Preview)

Both channels are available for both Windows and Mac devices. For more information, contact your administrator or read Compare Office Insider channels.

About VSTACK() in Excel

Excel’s new function VSTACK() appends arrays in sequence to return a larger array. In this context, array simply means multiple items. In other words, this function combines multiple values into a dynamic single list of values by spilling into the cells below and to the right of the function.

VSTACK()’s syntax is simple, requiring only references to the values as follows:

VSTACK(array1, [array2], …)

The only required argument, array1, references a column of values, and you can reference multiple arrays. The original data can be a normal data range or a Table object. One of the nice things about this function is that the referenced arrays don’t need to be the same size.

VSTACK() will return the referenced values in argument order. You might see the #N/A error when an array has fewer columns than the maximum width of the selected arrays. When this happens, you can wrap VSTACK() in an IFERROR() function, so you don’t see those errors.

Now, let’s look at a simple example of VSTACK().

How to use VSTACK() in Excel

When values or arrays aren’t contiguous, you can use VSTACK() to combine them into a single list. To demonstrate, the function in F3

=VSTACK(D3:D7,D11:D13)

returns a single list of amount values from both D3:D7 and D11:D13, as shown in Figure A. The function returns an array that spills into the cells below, consuming as many cells as necessary to fulfill the arguments.

Figure A

As you can see, the function appends the values in D11:D13 to the list in D3:D7. That’s because D3:D7 is the first argument. The function in G3

=VSTACK(D11:D13,D3:D7)

reverses the arguments, reversing their order in the resulting list in column G.

So far, the references have been in the same column, but that’s not necessary. As you can see in Figure B, VSTACK() combines the values in two different columns: D3:D7 and G3:G10. Not only do the values come from different columns, but G3:G10 has more values than D3:D7. This is no problem for VSTACK().

Figure B

Let’s look at one more simple example that shows how VSTACK() handles an array as an argument. Figure C shows the results of the function in J2:

=VSTACK({"Region","Amount"},C3:D7,C11:D13)

The function appends a two-column list and the {“Region”,”Amount”} array enters headings for both columns.

Figure C

Now that you have an idea of how this function works, let’s use it with a realistic example.

How to consolidate monthly tables using VSTACK() in Excel

A common need is to combine data on different sheets. For instance, let’s suppose you have 13 sheets, one for each month of the year and one that lists all of those data ranges in one long list. You can copy the data at the end of each month into that final list or you can use VSTACK().

Figure D shows a simple Table, and the sheet name is January. You can see from the tabs that there are 12 more sheets. What would you think if I told you that you can combine all these sheets with one simple function? You can.

Figure D

As you can see in Figure E, a simple VSTACK() function appends all 12 data ranges. You might also notice that the results include a lot of records filled with 0s. Look at the function closely:

=VSTACK(January:December!B3:D12)

If you compare the B3:D12 reference to the actual data ranges, you’ll see that each has many blank rows, but if each range has a different number of records, it’s the only way to make sure you get all the data, and even then, you might have one table that has more than the reference allows and miss data altogether. This isn’t a great solution.

Figure E

There’s an easy remedy: Use Excel Tables. By converting the data ranges to Tables, you don’t need to specify a data range. Instead, you’ll reference the 12 Tables.

To accomplish this, convert all the data ranges to Tables. Simply click inside the data range, press Ctrl + T, and then click OK. It’s a bit of a nuisance, but it won’t take long. Then, go to the consolidated sheet, 2021, and replace the first VSTACK() function with the following function:

=VSTACK(Table1,Table2,Table3,Table4,Table5,Table6,Table7,Table8,Table9,Table10,Table11,Table12)

If you’re a slow typist, use the AutoComplete list to enter the table names. Figure F shows the results. This time, the function returns a consolidated list of all 12 Tables, without the empty rows. When you update the Tables, the VSTACK() function in the 2021 sheet will update automatically. The result doesn’t copy cell formatting, so that’s something you’ll have to take care of manually.

Figure F

Stay tuned

Another new function is HSTACK(), which is also available through Microsoft’s Office Insider Beta channel. As you might suspect, this function is similar to VSTACK(), but the output is horizontal. I’ll cover this function in a future article.