Stacking and appending data are essential tasks for data transformation and analysis. They enable us to combine multiple datasets, merge columns or rows, and create a unified view of our information. To simplify this process, we have two powerful functions in Excel: VSTACK and HSTACK.
By utilizing the VSTACK and HSTACK functions, we can easily stack and manipulate data without the need for complex formulas. In this article, we will explore VSTACK and HSTACK and learn how to use them in Excel.
What are VSTACK and HSTACK Functions in Excel?
The VSTACK and HSTACK Functions in Excel are relatively new functions that were introduced as text manipulation functions in 2022. These functions are available to Microsoft 365 users, including those on the Basic Tier. They are among the many features of Excel for the Web that can enhance productivity.
The VSTACK function, also known as the vertical stack, allows you to append data and stack it vertically on top of each other. Its counterpart is the HSTACK function, or horizontal stack, which lets you append data horizontally and stack it side by side.
Both functions leverage the dynamic array environment to append and stack data into a single, larger array. The data can include lists, arrays, data from multiple sheets, specific headers, or even data within the same worksheet.
How to use VSTACK in Excel? VSTACK function in Excel
Its syntax for VSTACK can be described as it follows:
=VSTACK(array1,[array2],...)The argument for arrays in the syntax of the function is the arrays that you want to join vertically. Let’s begin with a basic illustration. Imagine that we will have two tables having an alphabet, and the respective numbers. To utilize the VSTACK:
- Within the Formula Bar, type VSTACK.
- Choose or write the array of the first table within the function. Then, insert an space. The table we are using is B3:C7.
- Then, follow the comma by the array of two tables. In our instance, it is E3:F7.
The final syntax you choose should be:
=VSTACK(B3:C7,E3:F7)
You could also use the function to exclude your headers. Here is an alphabetical list of names and you want to arrange them.
- Write using the VSTACK function.
- With a curve bracket there is a place to put your headers.
- Write a comma.
- Write the arrays for both tables without their headers.
=VSTACK(,B3:C8,B11:C16)
You can also sort names by combining VSTACK and the SORT feature of Excel. This can be accomplished by wrapping your table’s data with a SORT and VSTACK.
=VSTACK(,SORT(VSTACK(B3:C8,B11:C16)))
How do I Use HSTACK Function in Excel? HSTACK function in Excel
HSTACK’s syntax is: HSTACK is:
=HSTACK(array1,[array2],...)The argument to the array in syntax of the function is the arrays that you want to add horizontally. Let’s begin with a straightforward illustration. We’ll be using the numbers and letters data.
- Enter HSTACK within the Formula Bar.
- In the function, choose or type in the table’s arrays.
Final syntax is:
=HSTACK(B3:C7,E3:F7)As with the VSTACK We can also make use of the HSTACK alongside other functions, for instance the UNIQUE function that is available in Excel. Let’s check this out. This time, we need to create a list of unique value from two chocolate lists. It is possible to wrap the HSTACK inside the unique functions.
=UNIQUE(HSTACK(B4:C8, E4:F8))
Stack Your Data Quickly
Excel’s VSTACK and HSTACK functions might appear basic and basic at first. But their true power comes through in other powerful functions, such as FILTER.
Utilizing the synergy of your Excel functions will help you achieve a whole different level of analysis and manipulation. Learn the basics that is VSTACK HSTACK and HSTACK and discover the infinite possibilities that they provide when paired along with various others Excel functions.
