Logical

BYCOL Function in Excel

Applies a LAMBDA to each column and returns an array of results.

Syntax

  • =BYCOL(array, lambda)

Arguments

  • array (required): Array to process
  • lambda (required): LAMBDA to apply to each column

Examples

  • =BYCOL(A1:C5, LAMBDA(col, AVERAGE(col))) - Average each column - Result: Column averages

Excel BYCOL function documentation

  • BYCOL applies a LAMBDA to each column of an array and returns a row of results.
  • Syntax: =BYCOL(array, LAMBDA(column, calculation)). Microsoft 365.
  • Column-wise aggregates without TRANSPOSE tricks — modern replacement for some matrix patterns.
  • Related: [BYROW](/functions/byrow/), [MAP](/functions/map/), [LAMBDA](/functions/lambda/), [MAKEARRAY](/functions/makearray/).

BYCOL syntax and LAMBDA shape

  • array: 2D range — each column passed as 1D vector to LAMBDA.
  • LAMBDA first parameter represents one column at a time.
  • Result spills horizontally — one cell per column.
  • Requires LAMBDA literacy — start with simple SUM col: =BYCOL(A1:C10, LAMBDA(c, SUM(c))).

Step-by-step: column totals as spill row

  • Step 1 — Data A1:C100.
  • Step 2 — =BYCOL(A1:C100, LAMBDA(col, SUM(col))).
  • Step 3 — Clear row above spill if #SPILL!.
  • Step 4 — Compare SUM on same range — BYCOL when each column needs custom logic.
  • Step 5 — Example custom: =BYCOL(rng, LAMBDA(c, MAX(c)-MIN(c))) for column ranges.

BYCOL vs BYROW vs apply each column SUM

  • BYCOL — per-column custom lambda.
  • BYROW — per-row custom lambda.
  • Simple sums — SUM on range faster than BYCOL.
  • Logical category: [/categories/logical/](/categories/logical/).

Worked examples to copy

  • Col sums: =BYCOL(A1:E100, LAMBDA(c, SUM(c))).
  • Col average: =BYCOL(data, LAMBDA(c, AVERAGE(c))).
  • Nonblank count: =BYCOL(data, LAMBDA(c, COUNTA(c))).
  • Text join col: =BYCOL(A1:A10, LAMBDA(c, TEXTJOIN(",", TRUE, c))).

People also ask

  • BYCOL Excel 2019? — No; 365 with LAMBDA.
  • BYCOL #CALC!? — Lambda signature mismatch.
  • BYCOL vs MMULT? — Different purpose; BYCOL is element-wise column logic.
  • BYCOL performance? — Avoid full-sheet columns; bound the range.

Common errors

  • Requires Excel 365
  • LAMBDA receives entire column

Use cases

  • Column calculations
  • Custom column aggregations
  • Array processing

Frequently asked questions

  • What is BYCOL and when should I use it? BYCOL applies a function to each column of an array, returning one result per column (as a row). Use it for custom column statistics, like trimmed means, custom percentiles, or any calculation that needs to process entire columns.
  • How do I calculate trimmed mean for each column? Use =BYCOL(A1:D100, LAMBDA(col, TRIMMEAN(col, 0.1))). This calculates 10% trimmed mean for each column, excluding top and bottom 5% of values. BYCOL makes column-wise custom calculations simple.
  • Can I combine BYROW and BYCOL? Yes! Process columns first, then rows, or vice versa. Example: =BYROW(BYCOL(data, LAMBDA(c, c/MAX(c))), LAMBDA(r, SUM(r))) normalizes each column then sums each row. Powerful for complex transformations.

Editorial review

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

When to use BYCOL

  • Column calculations — common Logical scenario for BYCOL.
  • Custom column aggregations — common Logical scenario for BYCOL.
  • Array processing — common Logical scenario for BYCOL.

BYCOL in the Logical category

  • Browse all Logical functions at /categories/logical/ for related formulas.
  • BYCOL syntax: =BYCOL(array, lambda)
  • array (required): Array to process
  • lambda (required): LAMBDA to apply to each column
  • Confirm BYCOL 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 BYCOL 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

  • BYROW (/functions/byrow/): Applies a LAMBDA to each row and returns an array of results.
  • MAP (/functions/map/): Returns an array formed by mapping each value in the array(s) to a new value using a LAMBDA.
  • LAMBDA (/functions/lambda/): Creates custom, reusable functions that can be called by a friendly name.

Errors to watch for

  • Requires Excel 365 — review causes on linked error pages in the directory.
  • LAMBDA receives entire column — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter BYCOL 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.

BYCOL worked examples to copy

  • =BYCOL(A1:C5, LAMBDA(col, AVERAGE(col))) — Average each column. Expected result: Column averages.

BYCOL reference summary for crawlers and offline review

  • BYCOL belongs to the Logical category in Excel. Applies a LAMBDA to each column and returns an array of results.
  • Full syntax: =BYCOL(array, lambda). Open /functions/bycol/ for parameters, FAQs, and related pages.
  • Common mistakes: Requires Excel 365; LAMBDA receives entire column
  • 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.