30 Excel Functions in 30 Days: 12 – COLUMNS

Icon30DayYesterday, in the 30XL30D challenge, we got cell details with the CELL function, and learned that it’s useful for a few things, like extracting a worksheet name.

For day 12 in the challenge, we’ll examine the COLUMNS function. Will this function be as useful? Or is it just another lazy function, like AREAS? Well, it does count the columns, as promised, and plays well with others, but nothing too exotic or powerful here.

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).

So, let’s take a look at the COLUMNS information and examples, and if you have other tips or examples, please share them in the comments.

Function 12: COLUMNS

The COLUMNS function returns the number of columns in an array or reference..

Columns00

How Could You Use COLUMNS?

The COLUMNS function can show the size of a table or named range:

  • Count columns in an Excel Table
  • Sum last column in a named range

COLUMNS Syntax

The COLUMNS function has the following syntax:

  • COLUMNS(array)
    • array is an array or array formula, or reference to a range.

COLUMNS Traps

If you’re using a range reference, it must be a contiguous range.

Example 1: Count Columns in an Excel Table

In Excel 2007 and Excel 2010, you can create a formatted Excel table, and refer to its name in a formula. In this example, there is a table named RegionSales,

Columns01

and the COLUMNS function counts the number of columns in that table.

=COLUMNS(RegionSales)

Columns02

Example 2: Sum Last Column in Named Range

If you combine the COLUMNS function with SUM and INDEX, you can get the total for the last column in a named range. Here, the range is named MyRange,

Columns03

and this formula sums the last column in the named range.

=SUM(INDEX(MyRange,,COLUMNS(MyRange)))

Columns04

Download the COLUMNS Function File

To see the formulas used in today’s examples, you can download the COLUMNS function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the COLUMNS Video

To see a demonstration of the examples in the COLUMNS function sample workbook, you can watch this short Excel video tutorial.

YouTube link: Count Columns With Excel COLUMNS Function

_____________

You may also like...

4 Responses

  1. Luke Wisbey says:

    COLUMNS (like ROWS) is preferable to COLUMN when used as an “adjustment” given it is not AS susceptible to physical changes in the sheet.
    COLUMN (like ROW) is useful when we want an Array output as opposed to scalar and COLUMNS vice-versa.

  2. Thanks Luke, good tip.

  3. Contextures Blog » 30 Excel Functions in 30 Days: 24 - INDEX says:

    […] COLUMNS to calculate sum of last column in […]

  4. shiva says:

    how to hide the cells without hiding a,b and c while the cursor is moving boyond z…

Leave a Reply

Your email address will not be published. Required fields are marked *