How to use COLUMNS Function in Excel (3 Examples)

There are a number of functions in Excel to perform various lookup and search operations easily and swiftly. Today we are going to show you how to use a lookup function called COLUMNS. For the session, we are using Excel 2019, you can use your preferred version (at least Excel 2003).

Overview - Excel COLUMNS Function

Practice Workbook

You are welcome to download the practice workbook from the link below.

COLUMNS Function in Excel

1. Basic of COLUMNS Function

The COLUMNS function is categorized under Lookup & Reference functions. It returns the count of columns in a given reference.

COLUMNS - Excel COLUMNS Function

Summary

Returns the number of columns in an array or reference

Syntax

COLUMNS(array)

Arguments

array: A reference to a range of cells for which you want the number of columns.

Versions

Workable from Excel version Excel 2003.

2. Use of COLUMNS

There are several usages of the COLUMNS functions. Let’s explore some of the common uses.

To show examples, we have introduced a dataset of some students with their respective cities.

Data table - Excel COLUMNS Function

I. Basic use of COLUMNS

Find the number of columns

The most basic use of COLUMNS is to find the number of columns. To find the result all you need to do is insert the array or reference.

Column number - Excel COLUMNS Function

Here we have written the formula as

=COLUMNS(B3:E3)

You can see our table has 4 columns and the formula has produced that.

Find Total Number of Cells in a Dataset

We can find the total cells from a dataset using the COLUMNS functions.

Total cell - Excel COLUMNS Function

The total number of cells will be found by multiplying the columns and rows. We will find the column using COLUMNS and the row using ROWS

The generic formula will be like

=COLUMNS(array)*ROWS(array)

Total cell - Excel COLUMNS Function

The COLUMNS function provided column numbers and ROWS provided row numbers. Then multiplied together and produced the total cells.

II. Intermediate use of COLUMNS

Find Last Cell of a Dataset

We can find the last cell of a dataset using COLUMNS. 

Last cell of dataset- Excel COLUMNS Function

We need another couple of functions along with the COLUMNS functions; ADDRESS, ROW, COLUMN. 

The ADDRESS function returns the address for a cell based on a given row and column number.

The ROW function returns the row number for reference and the COLUMN function returns the column number for a reference. For more information please visit these articles ROW & COLUMN.

Our formula to find the last cell of the dataset will be like

=ADDRESS(ROW(array_dataset)+ROWS(array_dataset)-1,COLUMN(array_dataset)+COLUMNS(array_dataset)-1)

last cell - Excel COLUMNS Function

We have used the Cell reference of the dataset.

ROW(B4:E8)+ROWS(B4:E8)-1, this portion returned {8;9;10;11;12} and COLUMN(B4:E8)+COLUMNS(B4:E8)-1 returned {5;6;7;8}

From these two arrays, we have found the last cell of the dataset.

For finding the first cell of the dataset use the below formula

=ADDRESS(ROW(array_dataset),COLUMN(array_dataset))

First cell - Excel COLUMNS Function

III. Advanced use of COLUMNS

Use COLUMNS with VLOOKUP

The VLOOKUP function returns lookup data in a table organized vertically.

VLOOKUP(lookup_value,lookup_array,column_num,match_type)

We can provide the column_number using COLUMNS. 

VLOOKUP - Excel COLUMNS Function

Let’s we need to find the address of students comparing their first names.

VLOOKUP - COLUMNS - Excel COLUMNS Function

Here we have set the lookup_value and lookup_array using cell reference. The lookup_value needs to be at the first column of the array. That’s why we have set $C$3:$E$8 as our array.

COLUMNS($C$3:$E$8) returned the column_num. 3 here, the address was stored at the 3rd column (from First Name).

0 for an exact match. Thus we have found the value we were looking for.

Autofill - Excel COLUMNS Function

3. Things to Remember

1. The array argument in the COLUMNS Function can be either a range of cells or single-cell addresses.

Insert one cell - Excel COLUMNS Function

Inserted a single cell.

Result - of one cell input - Excel COLUMNS Function

The answer has been produced. Found the column number.

range of cell - Excel COLUMNS Function

Provided a range of cells.

range in function - Excel COLUMNS Function

The function returned the column numbers.

2. If the range of cells or array contains multiple rows and columns, only the columns are counted.

Rows - columns-Excel COLUMNS Function

Inserted rows and columns in the function.

ignored rows - Excel COLUMNS Function

Ignored the rows and provided column numbers only.

3. COLUMNS in Excel will always return a positive numeric value.

Conclusion

That’s all for today. We have tried showing how you can use the COLUMNS function in different circumstances. You can use the function from a basic operation to advanced operations. Hope you will find this helpful.

Feel free to comment if anything seems difficult to understand. Let us know any of your COLUMNS function-related scenarios where you have stuck, we are ready to help.


Further Readings

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo