VSTACK function
The VSTACK
function in Jspreadsheet Formulas Pro is a useful tool that allows you to compile data from several different ranges and stack them vertically in one single column. This means you can gather various pieces of information and have them all neatly organized in one place. It's like stacking blocks on top of each other, but with data. This tool makes data management simpler and more organized, aiding in efficient analysis and comparison of information.
Documentation
Stacks values from multiple ranges vertically into a single column.
Category
Lookup and reference
Syntax
VSTACK(range1, [rangeN], ...)
Parameter | Description |
---|---|
range1 |
The first range of cells to stack. |
rangeN |
Optional. Additional ranges of cells to stack. |
Behavior
The VSTACK
function is used to stack multiple ranges of cells vertically into a single column. Here's how it handles different types of inputs:
- Empty Cells: Empty cells are included in the output range as blank cells.
- Text: Text cells are stacked in the same order they appear in the input range.
- Booleans: Boolean values are treated as regular cells and stacked in the order they appear.
- Errors: If one of the input ranges contains a cell with an error,
VSTACK
will return an error.
Common Errors
Error | Description |
---|---|
#NULL! | This error occurs when the ranges you are trying to stack have different number of columns. All ranges need to have the same number of columns for VSTACK to work correctly. |
#VALUE! | This error occurs when an unsupported argument or incompatible data type is included in one of the stacked ranges. |
#REF! | This error is returned when the formula refers to a cell that is not valid. This can happen if a cell has been deleted or moved. |
Best practices
- When using the
VSTACK
function, ensure that all the ranges or arrays you want to stack have the same number of columns. This is a common mistake that usually results in an error.- Avoid including cells with errors in the input ranges. If a cell with an error is included, the
VSTACK
function will return an error.- Be mindful of the order of the ranges you input into the
VSTACK
function. The ranges will be stacked in the order you input them.- If you want to ignore empty cells, consider using a filter function in combination with
VSTACK
.
Usage
A few examples using the VSTACK function.
// Example 1: Stacking two ranges
VSTACK(A1:A3, B1:B3)
// Output: A1, A2, A3, B1, B2, B3 stacked vertically
// Example 2: Mixing range with a literal value
VSTACK(A1:A3, "Text")
// Output: A1, A2, A3, Text
// Example 3: Including Boolean values
VSTACK(TRUE, FALSE, A1:A2)
// Output: 1, 0, A1, A2 (TRUE = 1, FALSE = 0)
// Example 4: Handling empty cells
VSTACK(A1:A2, C1:C3)
// If C2 is empty → Output includes a blank row at that position
Interactive Spreadsheet Demo
<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>
<div id="spreadsheet"></div>
<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YTZiZGY4N2MyMTdkMDFkYWJhNGQxZTliNTg1YzlmNjRhOGIyODFiZjY5M2E5OGY5ODY3MDEzNDdhMmQ5OTg1OGJhN2IzZDg1MDU5ODZiNmNmOTk0NmIyYjMxNjM5NGU3Y2ZlY2RjMzY1MDNhMGVjODAwODk3YjI2NmJjZWU5MTQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pNekE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[
"Quarter 1",
"Quarter 2",
"All Quarters"
],
[
100,
150,
"=VSTACK(A1:A4,B1:B4)"
],
[
120,
180
],
[
110,
160
],
[
130,
170
]
]
}]
});
</script>
</html>
import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
// Set license
jspreadsheet.setLicense('YTZiZGY4N2MyMTdkMDFkYWJhNGQxZTliNTg1YzlmNjRhOGIyODFiZjY5M2E5OGY5ODY3MDEzNDdhMmQ5OTg1OGJhN2IzZDg1MDU5ODZiNmNmOTk0NmIyYjMxNjM5NGU3Y2ZlY2RjMzY1MDNhMGVjODAwODk3YjI2NmJjZWU5MTQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pNekE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Worksheet data
const data = [
[
"Quarter 1",
"Quarter 2",
"All Quarters"
],
[
100,
150,
"=VSTACK(A1:A4,B1:B4)"
],
[
120,
180
],
[
110,
160
],
[
130,
170
]
];
// Render component
return (
<Spreadsheet ref={spreadsheet}>
<Worksheet data={data} />
</Spreadsheet>
);
}
<template>
<Spreadsheet ref="spreadsheet">
<Worksheet :data="data" />
</Spreadsheet>
</template>
<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";
// Set license
jspreadsheet.setLicense('YTZiZGY4N2MyMTdkMDFkYWJhNGQxZTliNTg1YzlmNjRhOGIyODFiZjY5M2E5OGY5ODY3MDEzNDdhMmQ5OTg1OGJhN2IzZDg1MDU5ODZiNmNmOTk0NmIyYjMxNjM5NGU3Y2ZlY2RjMzY1MDNhMGVjODAwODk3YjI2NmJjZWU5MTQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pNekE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Worksheet data
const data = [
[
"Quarter 1",
"Quarter 2",
"All Quarters"
],
[
100,
150,
"=VSTACK(A1:A4,B1:B4)"
],
[
120,
180
],
[
110,
160
],
[
130,
170
]
]
return {
data
};
}
}
</script>
import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YTZiZGY4N2MyMTdkMDFkYWJhNGQxZTliNTg1YzlmNjRhOGIyODFiZjY5M2E5OGY5ODY3MDEzNDdhMmQ5OTg1OGJhN2IzZDg1MDU5ODZiNmNmOTk0NmIyYjMxNjM5NGU3Y2ZlY2RjMzY1MDNhMGVjODAwODk3YjI2NmJjZWU5MTQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pNekE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
@Component({
standalone: true,
selector: "app-root",
template: `<div #spreadsheet></div>`
})
export class AppComponent {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
// Worksheets
worksheets: jspreadsheet.worksheetInstance[];
// Create a new data grid
ngAfterViewInit() {
// Create spreadsheet
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
worksheets: [{
data: [
[
"Quarter 1",
"Quarter 2",
"All Quarters"
],
[
100,
150,
"=VSTACK(A1:A4,B1:B4)"
],
[
120,
180
],
[
110,
160
],
[
130,
170
]
]
}]
});
}
}