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.

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('MmY2NDZiNWU1NmFhMjFmYWFjNDgzNmI1Y2ZlYmI2MDRlNWUzMjg5ZTdlYTZhNTgwZmY5MGM3ZDY3MmIwMTU5MTI4ODg2ZTI2MGE1ZWQ3OGI3MjM1MDUwZGFlOGY5Njg4ZTEwNmJhMWFiZGEwY2QyMGM5ZDFhZjM0NDA2MTQ5ZjcsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56Y3pPRFEwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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('MmY2NDZiNWU1NmFhMjFmYWFjNDgzNmI1Y2ZlYmI2MDRlNWUzMjg5ZTdlYTZhNTgwZmY5MGM3ZDY3MmIwMTU5MTI4ODg2ZTI2MGE1ZWQ3OGI3MjM1MDUwZGFlOGY5Njg4ZTEwNmJhMWFiZGEwY2QyMGM5ZDFhZjM0NDA2MTQ5ZjcsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56Y3pPRFEwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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('MmY2NDZiNWU1NmFhMjFmYWFjNDgzNmI1Y2ZlYmI2MDRlNWUzMjg5ZTdlYTZhNTgwZmY5MGM3ZDY3MmIwMTU5MTI4ODg2ZTI2MGE1ZWQ3OGI3MjM1MDUwZGFlOGY5Njg4ZTEwNmJhMWFiZGEwY2QyMGM5ZDFhZjM0NDA2MTQ5ZjcsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56Y3pPRFEwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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('MmY2NDZiNWU1NmFhMjFmYWFjNDgzNmI1Y2ZlYmI2MDRlNWUzMjg5ZTdlYTZhNTgwZmY5MGM3ZDY3MmIwMTU5MTI4ODg2ZTI2MGE1ZWQ3OGI3MjM1MDUwZGFlOGY5Njg4ZTEwNmJhMWFiZGEwY2QyMGM5ZDFhZjM0NDA2MTQ5ZjcsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56Y3pPRFEwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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
    ]
]
            }]
        });
    }
}

Edit this page on GitHub