This guide will explain how to use the VSTACK function in Google Sheets.
When working with data from multiple tables or sheets, you may find the need to consolidate or combine data into a single table.
The simplest way to accomplish this with a formula is with curly braces. For example, we can append the ranges A2:B6 and D2:E6 together using the formula ={A2:B6;D2:E6}.
In 2023, Google Sheets released a new function called VSTACK that performs a similar operation. The VSTACK function allows users to vertically append two or more ranges into a single array.
In this guide, we will provide a step-by-step tutorial on how to use the VSTACK function in Google Sheets. We will cover how to use the function to append ranges vertically and how to account for ranges with a different number of columns.
Let’s dive right in!
The Anatomy of the VSTACK Function
The syntax of the VSTACK function is as follows:
=VSTACK(range1, [range2, …])
Let’s look at each term to understand how to use the function.
- = the equal sign is how we start any function in Google Sheets.
- VSTACK() refers to our
VSTACKfunction. This function allows us to append ranges vertically and in sequence. - range1 refers to the first range to append to the new array.
- [range2, …] refers to all additional ranges we want to append to the new array.
A Real Example of Using VSTACK in Google Sheets
Let’s explore a simple use case for the VSTACK function in Google Sheets.
In the table seen below, we have two separate tables for product sales in 2022 and 2023.
We want to find a way to create a new table that combines the data from both of these tables.
To achieve this, we can use the VSTACK function to append the 2022 and 2023 tables into a single array.
We can generate the table above by placing the following formula in cell A10:
=VSTACK(A2:B6,D2:E7)
Do note that the order of the arguments affects the formula’s output. If we want the 2022 data to appear first on the table, we must add the appropriate range as the first argument.
In the example below, we again have two tables we want to combine into a single array. However, the second table has an additional ‘returns’ column not present in the first table.
We can use the VSTACK and IFERROR functions together to fill in the cells with missing data:
=IFERROR(VSTACK(A2:B6,D2:F7),"No available data")
Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below.
Use our sample spreadsheet to see how the VSTACK function works in our sheet.
If you’re ready to try appending ranges using VSTACK, head over to the next section to read our step-by-step tutorial on how to do it!
How to Use the VSTACK Function in Google Sheets.
This section will guide you through each step needed to begin using the VSTACK function in Google Sheets.
Follow these steps to start appending ranges vertically:
- First, select an empty cell and begin typing the
VSTACKfunction.
In this example, we have two sales tables we want to append vertically into a single table.
- Type the first range you want to include in the final output.
- Next, write down the reference to the range you want to append to the range in the first argument.
Do note that these ranges appear in theVSTACKoutput in the order they appear in the function’s arguments.
- Hit the Enter key to evaluate the function. You should now have a new array that appends the two selected ranges.
- The
VSTACKfunction is dynamic and will automatically update when the values in the original cells change.
- The
VSTACKfunction can support ranges with a mismatch in the number of columns. The final array will have the same number of columns as the widest range in the arguments. However, the function will output a #N/A error to fill out the empty cells.
In the example above, we append two tables into a single array. However, since the data from 2022 does not include a returns column, our function returns an error to fill in the missing data.
- We can replace the error with our own custom values by wrapping the
VSTACKfunction with anIFERRORfunction.
In our example, we replaced the #N/A error with the text ‘No available data’ instead.
Frequently Asked Questions (FAQ)
Here are some frequently asked questions about this topic:
- Why should you use the VSTACK function rather than the curly braces method?
TheVSTACKfunction is much easier to use since it only requires the user to input arguments into a function. TheVSTACKfunction also allows you to combine ranges with a different number of columns.
Appending ranges with a different number of columns using the curly braces method will result in a #VALUE! error.
This tutorial should cover everything you need to know about the VSTACK function in Google Sheets.
We’ve explained how the VSTACK function can help append arrays vertically. We’ve also shown how to handle errors when the tables being appended have a different number of columns.
The VSTACK function in Google Sheets is just one of many useful built-in functions you can use. If you found this guide helpful, you may be interested in reading our guide on how to combine two query results in Google Sheets.
You may also check our guide on how to use the IMPORTRANGE function in Google Sheets to learn how to work with data from multiple live spreadsheets at once.
That’s all for this guide on the VSTACK function! If you’re still looking to learn more about Google Sheets, be sure to check out our library of Google Sheets resources, tips, and tricks!
