Documentation/Calc Functions/COLUMN

    From The Document Foundation Wiki
    Other languages:


    Function name:

    COLUMN

    Category:

    Spreadsheet

    Summary:

    Determines the column number(s) of a given reference to a single cell or to a range of cells.

    Syntax:

    COLUMN([Reference])

    Returns:

    Returns one or more positive integers in the range 1 to 1024 (inclusive) which is/are the column number(s) of the cell(s) specified in the argument.

    • If the Reference argument is omitted, the column number of the cell in which the formula is entered is returned.
    • If the Reference argument refers to a single cell, the column number of that cell is returned.
    • If the Reference argument refers to a rectangular cell range and the formula was not entered as an array formula (completed using Enter), the number of the leftmost column in the range is returned.
    • If the Reference argument refers to a single rectangular cell range and the formula was entered as an array formula, all column numbers of the area are returned in a single-row array.

    Arguments:

    Reference is a specific cell reference (for example, "$Sheet1.A1"), a specific cell range (for example, "$Sheet1.$C$28:$H$29"), or the name of a named or database range.

    • If Reference is omitted, the column number of the cell in which the formula is entered is returned.
    • If Reference is not a valid reference to a single cell or a single rectangular cell range, then COLUMN reports a parameter list error (Err:504). COLUMN does not process references that comprise multiple cell ranges.

    Additional details:

    • Assuming that you are using A1 notation for formula syntax, column A is 1, column B is 2, and so on. Formula syntax is selected in the Formula Options area of the Tools ▸ Options ▸ LibreOffice Calc ▸ Formula dialog (LibreOffice ▸ Preferences ▸ LibreOffice Calc ▸ Formula on macOS).
    • The detailed behaviour of the COLUMN function depends on the nature of the argument and whether the function call has been entered as an array formula.
    • To create an array formula, type the formula in the Input line in the upper part of the Calc window and on completion press Control + ⇧ Shift + ↵ Enter (⌘ Command + ⇧ Shift + ↵ Enter on macOS) rather than the more usual Enter. Alternatively, use the Function Wizard to construct the call to the COLUMN function and tick the Array toggle button before pressing OK. If you click on a cell that contains an array formula and view the formula in the Input line, then the content of that cell will be surrounded by curly brackets, for example {=COLUMN(A1:Z26)}. It is not possible to create an array formula by simply typing the curly brackets around a non-array formula.

    Examples:

    Formula Description Returns
    =COLUMN() where this formula is entered in cell Z14. Here the function determines the column number of cell Z14 and returns 26. 26
    =COLUMN(D9) Here the function returns 4, the number of column D. 4
    =COLUMN($Sheet4.C11:E17) Here the function returns 3, the number of column C. 3
    {=COLUMN(myArea)} where the formula is entered as an array formula and the named range myArea covers the cell range D11:F16. The function returns a three element, single-row array containing the numbers of columns D, E, and F. (4, 5, 6)
    {=COLUMN(B2:B7)} where the formula is entered as an array formula. Here the function returns the number of column B – although entered as an array formula, the supplied reference only contains cells in column B. Since the non-array formula =COLUMN(B2:B7) also returns 2, it does not make a difference whether or not the formula is used as an array formula. 2

    Related LibreOffice functions:

    COLUMNS

    ROW

    SHEET

    ODF standard:

    Section 6.13.4, part 2

    Related (or similar) Excel functions:

    COLUMN