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).

## 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.

#### 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 Function

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.

#### 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.

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.

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)`

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. **

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)`

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

**returned {5;6;7;8}**

`COLUMN(B4:E8)+COLUMNS(B4:E8)-1 `

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))`

#### 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. **

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

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.

### 3. Things to Remember

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

Inserted a single cell.

The answer has been produced. Found the column number.

Provided a range of cells.

The function returned the column numbers.

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

Inserted rows and columns in the 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.