Welcome to Excel Jet Consult blog post. In this tutorial, we shall consider additional two out of the 14 newly released function by the Microsoft Excel team. Let’s proceed
Video
VSTACK Function
The VSTACK Function appends data to the first blank cell at the bottom of a range.
Syntax
=VSTACK(array1,[array2],…)
The VSTACK function syntax has the following argument:
array The arrays to append.
Remarks
VSTACK returns the array formed by appending each of the array arguments in a row-wise fashion. The resulting array will be the following dimensions:
- Rows: the combined count of all the rows from each of the array arguments.
- Columns: The maximum of the column count from each of the array arguments.
HSTACK Function
The HSTACK Function returns the array formed by appending each of the array arguments in a column-wise fashion.
Syntax
=HSTACK(array1,[array2],…)
The HSTACK function syntax has the following argument:
array The arrays to append.
Remarks
HSTACK returns the array formed by appending each of the array arguments in a column-wise fashion. The resulting array will be the following dimensions:
- Columns The maximum of the column count from each of the array arguments.
- Rows the combined count of all the rows from each of the array arguments.
In the sample dataset screenshot below, we have three worksheet containing sales data for the month of January, February and March respectively.
In addition, we have formatted the dataset as an Excel tables using the respective month names as the descriptive table names. So, our goal is to stack the dataset in the order of months (January to March). In addition, we want to reference the headers from the month of January only and finally, we want to use the same single formula to calculate the total row at the bottom.
To perform the operation, we are going to use the Tables feature structured referencing syntax which enables us to stay in the Summary sheet and write the formula without moving across to the other sheets
- Therefore, in cell A1 of the Summary sheet, execute the formula: =VSTACK(Jan[#All],Feb,Mar,HSTACK(“Total”,SUM(Jan[[Sales ]],Feb[Sales],Mar[Sales])))
- Click Enter
In the above formula, we used the VSTACK Function to vertically append the dataset in the Jan to Mar Tables. Note, we used Jan[#ALL] syntax which is necessary for the Jan Table to deliver the headers in it. In the other part of the formula, we used HSTACK function and the first array is the Total lable wrapped inside. Then, we used the SUM aggregate function to add up each of the Sales columns from the three tables.
In the screenshot below, we have the final result which is awesome
This is simply amazing. See you in the next tutorial