Buzz
Combining multiple data files, consolidating columns or rows, and creating a unified view of information can all be achieved with the powerful VSTACK and HSTACK functions in Excel, simplifying this process without resorting to complex formulas.
What Are VSTACK and HSTACK Functions? How to Use Them in Excel
I. Understanding VSTACK and HSTACK Functions
- VSTACK and HSTACK are new functions in Excel, aiding users in appending and stacking data horizontally or vertically. Both functions utilize dynamic array environments to append and stack data as lists, specific header arrays, etc.
- As these are new Excel functions, only Microsoft 365 users can utilize them to enhance work efficiency.
II. Syntax and Usage of VSTACK Function in Excel
1. Syntax of VSTACK Function in Excel
- The syntax for the VSTACK function: =VSTACK(array_1, [array_2], ...)
- Where 'array_1' refers to the mandatory data range, subsequent arrays can be optional.
2. How to Use VSTACK Function in Excel
- Imagine you have two tables as shown below and want to merge both data horizontally using the VSTACK function:
+ In the formula bar type =VSTACK(
+ Then, input the array for the first table followed by a comma. The first table from Mytour is B4:D8
+ After the comma, enter the next array for the second table: F4:H8, close the parentheses, and press Enter on your keyboard
+ The final syntax for VSTACK in Excel is =VSTACK(B4:D8,F4:H8) with the result as shown below.
- Apart from the above method, you can also merge 2 data ranges and static arrays directly within the Excel VSTACK function. Specifically:
=VSTACK({'ProductID', 'Group', 'SubGroup'}, B4:D8, F4:H8)
- Additionally, readers can merge data tables together, and when new data ranges are added, the VSTACK function's result updates automatically.
+ In the formula bar type =VSTACK(
+ Input the first table 'prod1' and add a comma to separate it from table 'prod2', close the parentheses, and press Enter on the keyboard.
+ Result: =VSTACK(prod1,prod2)
III. Syntax and Usage of HSTACK Function in Excel
1. Syntax of HSTACK Function in Excel
- Standard syntax for the HSTACK function: =HSTACK(array_1, [array_2], ...)
- Similar to the VSTACK function, array 1 is a mandatory parameter referencing a data range, and array 2 can be optional.
- While the VSTACK function concatenates and stacks data horizontally, HSTACK does the opposite and stacks them vertically.
2. How to Use HSTACK Function in Excel
- Suppose you have 3 tables: ProductID, Group, and Subgroup as shown below. To streamline, you can merge the data from all 3 tables together using the HSTACK function.
+ In the formula bar, enter =HSTACK(
+ Input data from table 1 to table 3, and add commas to separate arrays.
+ The final syntax for this example will be =HSTACK(B4:B9,E4:E9,H4:H9). Along with the displayed result on the screen.
Through the examples above, readers must have grasped how to utilize VSTACK and HSTACK functions in Excel effectively. Thanks to this, you can apply the formulas to your work most efficiently.
In Excel, the Vlookup function is also widely used, allowing you to search for values by column and return the corresponding row-wise method. This is a quite popular and convenient function for users who often use Microsoft Excel.
- Read more: How to use Vlookup function in Excel
