Stacking and appending data is crucial for data transformation and analysis. These tasks allow us to combine multiple datasets, merge columns or rows, and ultimately create a unified view of our information. This can be accomplished with two powerful functions that simplify this process: VSTACK and HSTACK.

By leveraging the VSTACK and HSTACK functions, we can effortlessly stack and manipulate data without writing complicated formulas. In this article, we’ll learn about VSTACK and HSTACK and how to use them in Excel.

What Are VSTACK and HSTACK Functions in Excel?

The VSTACK and HSTACK Functions in Excel are pretty new functions. These functions were released as new text manipulation functions in 2022 and can be used by Microsoft 365 users. Even the Microsoft 365 Basic Tier has access to it. These functions are also one of the many features of Excel for the Web that can be used to level up your productivity.

The VSTACK function, short for the vertical stack, allows you to append data and stack them vertically on top of each other. Its opposite pair is the HSTACK function, also called the horizontal stack, which allows you to append data horizontally and stack them side by side.

Both functions use the dynamic array environment to append data and stack them into a single and more extensive array. The data here can be a list, an array, data from multiple sheets, specific headers, or the same worksheet.

How to Use the VSTACK Function in Excel

The syntax of VSTACK is as follows:

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

The array argument in the function syntax is the arrays you wish to append vertically. Let’s start with a simple example. Imagine we have two tables, each with some alphabet and their respective numbers. To use the VSTACK:

1. In the formula bar, write VSTACK.
2. Select or write the array for the first table within the function and add a comma. Our table is B3:C7.
3. Follow the comma with the array for the second table. In our case, that is E3:F7.

Your final syntax should be:

        =VSTACK(B3:C7,E3:F7)
    

You can also use the function and exclude your headers. Here, we have a list of names, and you wish to stack them together.

1. Write the VSTACK function.
2. In a curvy bracket, you write your headers.
3. Write a comma.
4. Write the array for both tables without their headers.

        =VSTACK({"Name","Age"},B3:C8,B11:C16)
    

We can also sort the name by combining VSTACK with the SORT function in Excel. You can achieve this by wrapping the data in your table in a SORT and VSTACK.

        =VSTACK({"Name","Age"},SORT(VSTACK(B3:C8,B11:C16)))
    

How to Use the HSTACK Function in Excel

The syntax of HSTACK is:

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

The array argument in the function syntax is the arrays you wish to append horizontally. Let’s start with a simple example. We will use the letters and numbers data.

1. Write HSTACK in the formula bar.
2. Within the function, select or write the arrays of your tables.

The final syntax will be:

        =HSTACK(B3:C7,E3:F7)
    

Like the VSTACK, we can use the HSTACK with other functions, like the UNIQUE function in Excel. Let’s see how this plays out. This time, we want a list of unique values from two lists of chocolates. We can wrap our HSTACK within the UNIQUE functions.

        =UNIQUE(HSTACK(B4:C8, E4:F8))
    

Stack Your Data Easily

Excel's VSTACK and HSTACK functions may appear simple and fundamental at first glance. However, their true beauty emerges with other powerful functions like FILTER.

Harnessing the synergy between your functions can help you unlock a whole new level of data manipulation and analysis. So learn the simplicity of VSTACK and HSTACK and explore the endless possibilities they offer when integrated with other Excel functions.