Welcome to the Sheets Insiders newsletter #8, your exclusive, members-only publication.
You can see the full archives here.
This week, we’re looking at some of the newer functions that have been added to Google Sheets in the past couple of years. If you do any kind of data manipulation work then they’re well worth learning.
This week we’re focussing on HSTACK and VSTACK.
Template
Download the HSTACK,VSTACK Template
Click on “Use Template” in the top right corner to make your own copy.
There is no Apps Script with this template.
Introduction
In 2022, Google added ten new functions to our Sheets, including the LAMBDA functions and Named Functions.
Then, in 2023, we got another batch of eleven powerful analytical functions, mostly for doing data transformations.
Obviously, I can’t cover all of these in a single email, but I expect we’ll see all of the them at some point in these membership emails.
Today, I want to focus on HSTACK and VSTACK, two functions that stack data horizontally or vertically.
They’re functionally equivalent to the old-school array literals syntax {…}, but better. These functions are more readable and can work with different sized arrays.
Example Set 1: Functions
HSTACK(1,2) gives this output:
 |
whereas VSTACK(1,2) gives us:
 |
We can include text values, dates, or even other formulas nested inside the HSTACK and VSTACK formulas, e.g.
=HSTACK("Total",SUM(A1:A5))
 |
We can also stack arrays of different dimensions (unlike the old array literals {…}, which won’t allow this).
The VSTACK or HSTACK function will fill any “missing” values in the combined array with #N/A errors, e.g.:
=VSTACK(A1:A2,C1:D1)
 |
We can wrap it with an IFERROR to remove these:
=IFERROR(VSTACK(A1:A2,C1:D1))
 |
Or insert a custom error message:
=IFERROR(VSTACK(A1:A2,C1:D1),"No value")
 |
Let’s now see some practical examples using these functions:
Default Values
Look at this GIF showing a simple table in Google Sheets.
I can type on top of the 100 with different values, but if I delete those values, the cell defaults back to 100 automatically.
 |
Magic!
Or is it?
No, it’s a clever formula that we’ll see below.
But first, let’s see the simple version.
Example 2: Default Values with Hidden Column
In cell A1, enter the product name, e.g. “Product A”.
In cell B1, enter this HSTACK function:
=HSTACK(,100)
This leaves cell B1 blank (because of the empty first argument) and puts the value 100 in cell C1:
 |
Now try typing a different value into cell C1:
 |
Delete it again and the 100 reappears automatically!
Nice! We have a default value system in place.
Of course, that REF error doesn’t look good so we can either:
- Hide that column (the simple approach), or
- Develop a more sophisticated formula
When you hide the column, the REF error is hidden, but the default value will still be displayed whenever the adjacent cell is blank.
Example 3: Default Values with Iterative Calculation
Clear out the existing formulas, or move to a new blank Sheet, and enter this formula in cell A1:
=IF(ISBLANK(B1),HSTACK("Product A" , 100),"Product A")
At its core, it’s still the same HSTACK approach to create an array that places the 100 into the adjacent cell.
The IF function checks if B1 is blank and only inserts the default value if B1 is blank, otherwise it only outputs a value in A1.
For this method to work though, we need to switch on Iterative Calculations in our Sheet.
Go to File > Settings > Calculation
And set Iterative Calculation to “On” and max number of iterations to 1:
 |
Example 4: Multi-Select Dropdowns with Database Functions
Last week during the live session (replay available here) one of the questions that came up was “how can we use the database functions with multi-select dropdowns?”
I recommend watching the video from 42:05 to get the context of this question.
We didn’t finish the answer during the video call so I’m sharing a solution now.
Use this formula to convert a multi-select dropdown selection into criteria for the database function:
=IFERROR(VSTACK("Group",TOCOL(SPLIT(E2,", ",false))))
 |
- SPLIT separates the multiple dropdown choices into separate cells.
- TOCOL transforms the array into a column.
- VSTACK adds the appropriate heading for the database function.
- IFERROR removes any error messages.
We can then feed this dynamic array into our database function as the criteria argument:
=LET(criteria, IFERROR( VSTACK("Group", TOCOL( SPLIT(E2,", ",false)))), DCOUNT(data[#ALL],"values",criteria))
 |
I’ve used a LET function to make the formula more readable.
The output of the previous formula is assigned to a variable called “criteria” and this is then inserted into the database function DCOUNT.
Learn more about database functions in last week’s issue.