This situation is all too common: you need to consolidate data from multiple Excel sheets for data analysis or reporting purposes. Copying and pasting the data can work, but it's an error-prone process, and the resulting range will not be dynamic (many people just like their data to be dynamic). But don't worry—Excel has you covered with the VSTACK and HSTACK functions.

These functions were introduced in Microsoft 365 specifically for the purpose of combining arrays or ranges into a single entity. They also keep things dynamic, making them ideal for data that changes constantly. I will show you how to use them, as well as what you can do to make them account for data growthusing the FILTER function.

VSTACK

For data that needs to be ready for analysis

The VSTACK function combines data from multiple ranges or arrays by stacking them vertically—one on top of the other and in the order they're entered. The result is a longer array that can be used withstatistical analysis tools like PivotTables and charts.

=VSTACK(range1, [range2], ...)

The parameterrange1 is the first range or array you want to stack. Therange2 parameter and other optional parameters are the subsequent ranges and arrays to be placed below the previous ones.

Here is an example of what stacking data with the VSTACK function would look like:

=VSTACK(Sheet1!A2:D21, Sheet2!A1:D21, Sheet3!A1:D21, Sheet4!A1:D21)

For VSTACK to work, the columns must have the same structure across all ranges. Otherwise, the function will return blank cells or zeros in the empty cells (although it will still work), and the sheet will look untidy.

HSTACK

Best for side-by-side comparisons

The HSTACK function combines data horizontally by stacking each selected range or array side by side. This results in a wider array that’s suitable for side-by-side comparisons in tools like reports and dashboards.

=HSTACK(range1, [range2], ...)

Therange1 parameter is the first range or array to be stacked.range2 and optional parameters are subsequent ranges and arrays to be placed to the right of the previous ones. Again, this depends on the order in which they’re entered.

Here is an example of the HSTACK function in action:

=HSTACK(Sheet1!A1:E3, Sheet2!A1:E3, Sheet3!A1:E3, Sheet4!A1:E3)

For the HSTACK function to work as expected, the ranges should have the same number of rows across all sheets. As with the VSTACK function, failing to adhere to this practice will result in either blank cells or zeros.

Making VSTACK and HSTACK more dynamic

It’s always best to account for growth

So far, the data is dynamic, meaning if one of the values in the selected ranges changes, the corresponding ones in the resulting array from the VSTACK or HSTACK will be automatically updated. The problem is that if the ranges grow, the functions will not pick this up automatically.

One way to overcome this is to convert each of those ranges into a table. Here are the steps to do that:

1. Select the range, including the headers.
2. Select theInsert tab.
3. ClickTable in theTables command group of the ribbon.
4. In the pop-up, checkMy table has headers.
5. ClickOK.

If you don't want to go through the hassle of creating tables, you can select extra rows and columns to account for the data that hasn't been added yet. For instance, if the used cells areA2:A21, you can chooseA2:A51 instead—that's 30 extra cells.

You will notice that there are rows or columns that are blank or filled with zeros where the empty cells should be. This will make the sheet look untidy, but you can remove them with the FILTER function.

Here is an example of what the formula would look like:

=FILTER(VSTACK(Sheet1!A2:D51, Sheet2!A2:D51, Sheet3!A2:D51, Sheet4!A2:D51), VSTACK(Sheet1!A2:A51<>"", Sheet2!A2:A51<>"", Sheet3!A2:A51<>"", Sheet4!A2:A51<>""))

If you know how the FILTER function works, then the first parameter is the array we want to filter. The second parameter is the condition that filters itusing Excel's logical operators. Essentially, the formula excludes any row without a value from the result.

Don’t forget to transpose the data when you need to

Make sure the data is in the format you need

As mentioned earlier, the VSTACK function is best suited for data that requires analysis, while the HSTACK function is well-suited for data that needs to be compared. But if you need a VSTACK result for comparison or an HSTACK result for analysis, you can transpose the data, which will flip the rows and columns.

You can transpose the data statistically with a few clicks. Here's how to do that:

1. Select the range you want to transpose.
2. Right-click an empty cell and selectPaste Special in the menu.
3. CheckTranspose in the dialog box.
4. ClickOK.

If you want to transpose the data dynamically (recommended), you can use the TRANSPOSE function. Here's an example:

=TRANSPOSE(VSTACK(Sheet1!A2:D21, Sheet2!A2:D21, Sheet3!A2:D21))

Become the master of stacking in Excel

The VSTACK and HSTACK functions enable efficient data combination from multiple sheets while maintaining dynamic functionality. They're also quite easy to use with other functions if, for example, you want to work with growing datasets or need to transpose the data.

Furthermore, consider using them with theLET function in Excel as well. It will allow you to create variables, assign the sheets to them as values, and reference them in the formula. This will make the formula more human-readable and efficient.