Fortunately, there are a number of functions in Excel to perform various lookup and search operations easily and swiftly. Today, I am going to show you how to use a lookup function called **COLUMNS**. For the session, we are using Microsoft Office 365. However, you can use your preferred version (at least Excel 2003). In this article, I will show you **5 **ideal examples of using the **COLUMNS** function in Excel. Now, without further ado, letâ€™s begin.

**Table of Contents**hide

## Introduction to COLUMNS Function in Excel

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

**Function Objective**

Checks the number of columns in an array or reference and returns the number in digits.

**Syntax**

`=COLUMNS(array)`

**Arguments**

Argument | Required/Optional | Explanation |
---|---|---|

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

**Returning Parameter**

Integer Values- It returns the number of columns in positive integer values.

**Versions**

Workable from Excel version Excel 2003.

## How to Use COLUMNS Function in Excel: 5 Ideal Examples

Usually, you can use the **COLUMNS **function on various occasions. Letâ€™s explore some common uses of **COLUMNS**. Moreover, we will be using different datasets for different examples. However, keep in mind that these are just some basic examples to demonstrate the usage of the function in different scenarios. At the same time, the usage of the function can go a long way in developing useful formulas for automation. For the purpose of demonstration, I have used the following sample dataset.

### 1. Basic COLUMNS Operation in Excel

In general, the most basic use of the **COLUMNS **function is to find the number of columns. However, to find the result, all you need to do is insert the array or reference. Moreover, the function is very simple and handy to operate. Here, I will use the **COLUMNS **function in order to find the total number of columns for the given dataset. Hence, go through the steps below.

**ðŸ“Œ Steps:**

- Initially, select cell
**C11**and write down the following formula.

`=COLUMNS(B4:C4)`

- Next, press
**Enter**, and it will provide the number of columns.

- After that, put the following formula on cell
**C12**.

`=COLUMNS(B4:C4)*ROWS(B4:C9)`

- Finally, hit the
**Enter**button to get the number of**Total Cells**.

**Read More: Advanced Excel Lookup Functions**

### 2. Use the Excel COLUMNS Function to Get the First and Last Cell

Furthermore, I have joined the **ADDRESS **and **ROW **functions to find the cell references of the dataset in Excel. For the purpose of demonstration, I have chosen the complete dataset and calculated the cell reference of the first cell and last cell. However, the process is quite similar to the previous ones. Hence, follow the steps below in order to get your desired outcome.

**ðŸ“Œ Steps:**

- Firstly, select cell
**C11**and insert the formula below.

`=ADDRESS(@ROW(B4:C9),@COLUMN(B4:C9))`

- Secondly, hit the
**Enter**button, and you will receive your final output. Here, I have found the**First**cell of the dataset.

- Similarly, write the formula below in the next cell.

`=ADDRESS(@ROW(B4:C9)+ROWS(B4:C9)-1,@COLUMN(B4:C9)+COLUMNS(B4:C9)-1)`

**ðŸ”Ž Formula Breakdown:**

- Firstly, the
**ROW**function provides the row numbers sequentially of the cells of data, and the**ROWS**function gives the total number of rows which is**6**. - Secondly, the
**COLUMN**function gives the number of columns sequentially, whereas the**COLUMNS**function provides**2**as the number of columns. - Thirdly, the combination of the
**ROWS**and**COLUMNS**functions provides the last cell of the dataset. - Lastly, the
**ADDRESS**function provides the cell reference of the last cell of the provided dataset.

- Lastly, click the
**Enter**key, and you will get the**Last**cell reference of your dataset.

**Read More: How to Use VLOOKUP and HLOOKUP Combined Formula in Excel**

### 3. Combine COLUMNS with Excel VLOOKUP Function

Last but not least, I applied **the VLOOKUP function**Â to find the Address of Nick. For this reason, I utilized the **COLUMNS **function in addition to it. Moreover, it is suitable for several situations when the sum satisfies particular conditions. For example, I selected Nick from the dataset and want to call his address from the dataset, and it can be found easily by combining both functions. However, read through the steps below in order to complete the task easily.

**ðŸ“Œ Steps:**

- First, click cell
**C11**and write the formula mentioned below.

`=VLOOKUP("Nick",$B$5:$C$9, COLUMNS($B$5:$C$9),0)`

- At last, hit the
**Enter**key, and you will get your desired result, as in the image below.

**Read More: DGET vs VLOOKUP in Excel**

**Similar Readings**

**How to Use MATCH Function in Excel****How to Use LOOKUP Function in Excel****How to Use HLOOKUP Function in Excel****How to Use XLOOKUP Function in Excel**

### 4. Generate a Sequence of Numbers with COLUMNS

Moreover, you can generate a number sequence using the **COLUMNS **function. The process is simple and handy to operate. For the purpose of demonstration, I have used a different dataset. Hence, follow the steps in order to create a number sequence.

**ðŸ“Œ Steps:**

- In the beginning, click cell
**C5**and write the formula mentioned below.

`=COLUMNS($A$1:A1)`

- Next, press
**Enter,**and you will get**1**.

- Similarly, put
**B1**,**C1**,**D1**, etc., instead of**A1**in the formula to generate the sequence.

**Read More: How to Use FILTER Function in Excel**

### 5. Apply Array in COLUMNS Function

After all, you can use an array instead of a reference in the **COLUMNS **function. However, you have to input the array. Hence, go through the below steps. For the purpose of demonstration, I have used the following dataset. Here, you have two 3-column rows, containing 1,2, and 3 in the first row and 4,5, and 6 in the second row.

**ðŸ“Œ Steps:**

- Initially, click cell
**C5**and write the formula mentioned below.

`=COLUMNS({1,2,3;4,5,6})`

- Lastly, press
**Enter**to get the final result.

**Read More: How to Use SORT Function in Excel**

## Things to Remember

- First, the array argument in the
**COLUMNS**Function can be either a range of cells or single-cell addresses. - If the range of cells or array contains multiple rows and columns, only the columns are counted.
- However, you can combine a wide range of functions with the
**COLUMNS**function according to your needs. - You can use the functions or examples for any dataset, but you have to change the dataset parameters accordingly.
- Last, the
**COLUMNS**functions in Excel will always return a positive numeric value.

**Download Practice Workbook**

You can download the workbook used for the demonstration from the download link below.

## Conclusion

These are all the steps you can follow to use the **COLUMNS** function in Excel. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

## Related Articles