Products

COLUMN function

PRO BASIC

The COLUMN function in Jspreadsheet Formulas Pro is a handy tool that gives you the column number of a specific reference. For instance, if you want to know the column number of a particular cell, you would use this function. You simply input the cell reference into the function, and it will return the corresponding column number. This is particularly useful when managing large datasets and you need to quickly identify the location of specific information.

Documentation

Returns the column number of a reference.

Category

Lookup and reference

Syntax

COLUMN([reference])

Parameter Description
[reference] Optional. The cell or range of cells for which you want to return the column number. If omitted, returns the column number of the cell in which the formula appears.

Behavior

The COLUMN function returns the column number of a specified cell. If no cell is specified, the function returns the column number of the cell in which the formula is placed.

  • Empty cells: If the reference is an empty cell, COLUMN will still return the column number of that cell.
  • Text: COLUMN does not directly interact with text. If the referenced cell contains text, COLUMN will still return the column number of that cell.
  • Booleans: COLUMN does not directly interact with boolean values. If the referenced cell contains a boolean value, COLUMN will still return the column number of that cell.
  • Errors: If the cell reference is invalid or results in an error, COLUMN will also return an error.

Common Errors

Error Description
#REF! This error occurs when the referenced cell is invalid. It means that the cell does not exist, such as referencing a column that goes beyond the limit of the spreadsheet software.
#VALUE! This error occurs when the function COLUMN is used with an invalid argument. For example, using text instead of a cell reference.

Best practices

  • Always ensure that the cell reference used with COLUMN function is valid to avoid errors.
  • Use COLUMN function in combination with other functions to create dynamic formulas that adjust based on the column number.
  • Be aware that COLUMN function function returns column numbers relative to the spreadsheet, not to the range. For example, COLUMN(B1) returns 2, not 1.
  • Avoid using hard-coded column numbers in your formulas, instead use COLUMN function to make your formulas more flexible and easier to maintain.

Usage

A few examples using the COLUMN function.

COLUMN(A1) returns 1  
COLUMN(B4:C7) returns [[2, 3]]  
COLUMN() returns the column number of the cell containing this formula  

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

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Product",
        "Price",
        "Stock"
    ],
    [
        "Widget A",
        25.99,
        100
    ],
    [
        "Widget B",
        34.5,
        75
    ],
    [
        "=COLUMN(A1)",
        "=COLUMN(B2)",
        "=COLUMN(C1:C3)"
    ]
]
  }]
});
</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('NDE3NmFiZTQ1M2RiZWRkMjMzZDhjM2MzY2FmY2IyZTMwNGJiM2NlMTBjOWNkNjQwYjgwNjk2MWM1NWYzMDQxMmZiYWE2ZmQ1YTE1NjI1MzFhOTIwMjgzZTcwOTQxZGMzODU5YjBjMWZiZDMwYWMxZmIxMGJhZmRiODNlNjAyYzIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOekF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "Product",
        "Price",
        "Stock"
    ],
    [
        "Widget A",
        25.99,
        100
    ],
    [
        "Widget B",
        34.5,
        75
    ],
    [
        "=COLUMN(A1)",
        "=COLUMN(B2)",
        "=COLUMN(C1:C3)"
    ]
];

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

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Product",
        "Price",
        "Stock"
    ],
    [
        "Widget A",
        25.99,
        100
    ],
    [
        "Widget B",
        34.5,
        75
    ],
    [
        "=COLUMN(A1)",
        "=COLUMN(B2)",
        "=COLUMN(C1:C3)"
    ]
]

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

// 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: [
    [
        "Product",
        "Price",
        "Stock"
    ],
    [
        "Widget A",
        25.99,
        100
    ],
    [
        "Widget B",
        34.5,
        75
    ],
    [
        "=COLUMN(A1)",
        "=COLUMN(B2)",
        "=COLUMN(C1:C3)"
    ]
]
            }]
        });
    }
}