Lookup & Reference

COLUMNS Function in Excel

Returns the number of columns in a reference or array.

Syntax

  • =COLUMNS(array)

Arguments

  • array (required): Range or array to count columns

Examples

  • =COLUMNS(A1:E1) - Count columns in range - Result: 5

When to use COLUMNS

  • Use COLUMNS when you need to retrieve values from a table or range based on a key.
  • Confirm data types match (text vs number) and trim spaces before lookups.
  • Pair with [IFERROR](/functions/iferror/) or [IFNA](/functions/ifna/) for cleaner reports.
  • See the [lookup functions guide](/guides/excel-lookup-functions-guide/) for VLOOKUP, XLOOKUP, and INDEX MATCH paths.

Common COLUMNS mistakes

  • #N/A often means the key is missing or exact match is wrong — see [#N/A error](/errors/na/).
  • Lock table references with $ when copying formulas down a column.
  • Compare legacy vs modern lookups in [XLOOKUP vs VLOOKUP](/compare/xlookup-vs-vlookup/).

Common errors

  • Must be a valid range or array

Use cases

  • Dynamic range sizing
  • Array dimensions
  • Data validation

Frequently asked questions

  • How do I count columns in a table? Use =COLUMNS(Table1) or =COLUMNS(Table1[#All]) to count all columns in an Excel Table. This updates automatically when columns are added or removed from the table.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use COLUMNS

  • Dynamic range sizing — common Lookup & Reference scenario for COLUMNS.
  • Array dimensions — common Lookup & Reference scenario for COLUMNS.
  • Data validation — common Lookup & Reference scenario for COLUMNS.

COLUMNS in the Lookup & Reference category

  • Browse all Lookup & Reference functions at /categories/lookup-reference/ for related formulas.
  • COLUMNS syntax: =COLUMNS(array)
  • array (required): Range or array to count columns
  • Confirm COLUMNS arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm COLUMNS arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • COLUMN (/functions/column/): Returns the column number of a reference.
  • ROWS (/functions/rows/): Returns the number of rows in a reference or array.
  • ROW (/functions/row/): Returns the row number of a reference.

Errors to watch for

  • Must be a valid range or array — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter COLUMNS on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

COLUMNS worked examples to copy

  • =COLUMNS(A1:E1) — Count columns in range. Expected result: 5.

COLUMNS reference summary for crawlers and offline review

  • COLUMNS belongs to the Lookup & Reference category in Excel. Returns the number of columns in a reference or array.
  • Full syntax: =COLUMNS(array). Open /functions/columns/ for parameters, FAQs, and related pages.
  • Common mistakes: Must be a valid range or array
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.