Appending data sources is a common data cleaning task in Excel. And while Power Query offers robust methods for doing this, the immediacy and hands-on feel of Excel formulas can be unbeatable. The VSTACK() and HSTACK() formulas serve precisely this purpose, allowing you to vertically and horizontally stack data sources within Excel, respectively.
To follow along with this demonstration, please download the exercise file provided below:
Horizontally stacking with HSTACK()
For our first example, let’s examine a common scenario in accounting. We have an income statement for multiple months being started in income-statement and we want to assemble the results from other worksheets into one consolidated view. Specifically, we aim to take the cells from B2:B11 in both the February-statement and March-statement and place them side by side with January:
This is a great application for the horizontal stack, or HSTACK() function. Here’s the syntax:
=HSTACK(array1, [array2], ...)
These arrays are the entities that you want to horizontally combine. You can include multiple arrays in the function, and Excel will stack them horizontally. Each parameter must be either a range, an array constant, or a formula that results in an array.
Of course, this resulting stack will be much cleaner if there is consistency in structure across all the source data points, so ensure that everything looks good here.
Next, I am going to stack February and March’s data starting in cell C2 of the worksheet. Instead of looking up each of these ranges individually, I am going to hold down Shift to select each worksheet, then select B2:B11 in what is called a 3D reference:
=HSTACK('february-statement:march-statement'!B2:B11)
The 3D reference didn’t significantly save time here, as it was only for two months. However, if more months need to be added, you can adjust the function to include a 3D reference across all of them.
Vertically appending with VSTACK()
Next, let’s explore an example of vertically appending data sources. In the previous example, we focused on consolidating various report results; here, we’re dealing with a raw data compilation.
I have organized these data sources into tables. We have data for three regions, each in its own separate table, and we aim to display them in a consolidated format.
The syntax of VSTACK() is quite similar to that of HSTACK(), except that the stacking occurs vertically rather than horizontally.
=VSTACK(array1, [array2], ...)
Using a 3D reference in this example might be riskier because we don’t know if new data will be added to any of these tables, potentially causing the references to become uneven across worksheets. Therefore, I am opting for the somewhat longer but more precise method of specifying each table name within the VSTACK() syntax.
Now, if you were to simply append the north_region, west_region, and east_region tables together, this would not include a header row for any of them; we need to add it ourselves using some method. You could indeed just copy and paste the four header columns into the sheet and then proceed with VSTACK(). However, what I did here was actually refer to the north_region table in its entirety to get headers as well:
This could be a bit risky, for instance, if I added a total row to this table. However, I appreciate how dynamic it is. You can freely add or remove rows from the source tables, and your results will update automatically. You can also add or remove columns, although that could throw off the structure of the results significantly.
Conclusion
The HSTACK() and VSTACK() functions in Excel excel at appending data arrays horizontally and vertically, making them ideal for straightforward data consolidation when dealing with consistent source data of manageable size. These functions simplify compiling multiple data ranges into a single array, aiding in easy visualization and comparison of information.
However, they fall short with very large datasets or intricate data transformation tasks, where tools like Power Query may be better suited.
For a deeper understanding of Excel’s dynamic arrays and more sophisticated data handling techniques, check out my book, Modern Data Analytics in Excel:
Leave a Reply