How to Use the COLUMNS Function in Excel

Do you know how to use the COLUMNS function in Excel? This little-known function can be incredibly useful for businesses and individuals alike. COLUMNS returns the count of columns in a given reference as a number. In other words, COLUMNS tells you how many columns are in a given range or array. The COLUMNS function is versatile and easy to use, so let’s take a closer look at how it works!

What Does COLUMNS Do?

COLUMNS is an Excel Lookup/Reference Function. You can use COLUMNS to look up and provide the number of columns in each reference or array. Thus, the COLUMNS Function, after receiving an Excel range, will return the number of columns that are contained within that range.

This is definitely one of the more straightforward functions in Excel. You will not find many uses for this function, but we want to make sure and cover everything there is to know.

Where To Find It?

Option 1:

You can find the COLUMNS function in the Formulas tab of Excel under Lookup & Reference

Option 2:

You can use Formula Builder to walk you through the formula step by step.

Option 3:

You can manually type the formula into any cell.

The Formula

=COLUMNS(array)

array – this can be a single cell reference, a range, or an array

The COLUMNS Function in Excel's formula builder

Let’s Take a Look at an Example

Excel Workbook

Workbook_COLUMNS_Function Download

Example

These may be some of the most straightforward examples we have ever done on this blog.

First, we will use COLUMNS to count the columns for one cell. The formula is simply the cell reference nested within COLUMNS or =COLUMNS(D4). This returns a count of 1, for 1 column.

Second, we will use COLUMNS to count the columns in one range. The formula is simply the range reference nested within COLUMNS or =COLUMNS(D4:E7). This returns a count of 2, for 2 rows.

Lastly, we will use COLUMNS to count four references in an array. Really getting crazy with these examples. The formula is the two range references nested within COLUMNS or =COLUMNS({D4:E5,F6,G7}). Keep in mind if you are counting multiple areas, you need to add brackets or the formula will return an error. This returns a count of 4, for 4 columns.

Example of using the COLUMNS Function in Excel

Tips and Tricks

Did I get a #NULL error?

This means that you didn’t fill out the arguments in the correct format. You may have entered A 4 instead of A4 or B2C4 instead of B2:C4. Check your entry and try again.

Let’s Recap

The COLUMNS function doesn’t do much, but it’s straightforward and easy to use. I hope this quick walk-through helped expand your knowledge, not many people will know this function exists.

Have any questions? Are there other topics you would like us to cover? Leave a comment below and let us know! Make sure to subscribe to our Newsletter to receive exclusive financial news right to your inbox.