## What Is COLUMNS Function In Excel?

The

COLUMNS Function in Excelcalculates the number of columns in the given array. The function is used to know the number of columns in an array of references.

The **COLUMNS Function in Excel** is categorized under the Lookup & Reference group of the functional library of the formula tab.

For example, the image below depicts the values, and we will calculate the total number of columns in a range using the COLUMNS functions in Excel.

The steps to calculate the number of columns by the **COLUMNS Function in Excel **are as follows:

**Step 1:**First, select the column where we will enter the formula and then, calculate the result. In this example, we have selected cell C2.

**Step 2:**Next, we will enter the**Excel COLUMNS Formula**in cell C2.

**Step 3:**Then, enter the value of the**array**argument as A1:B2, i.e., the range of columns.

**Step 4:**Now, the complete formula entered is.*=COLUMNS(A1:B2)*

**Step 5:**Next, press**Enter**key.

We can see the results in cell C2.

Likewise, we can use columns function in excel to obtain the results.

##### Table of contents

- What Is COLUMNS Function In Excel?
- Syntax
- How To Use COLUMNS Formula In Excel?
- Example #1 – Total Columns In A Range
- Example #2 – Total Cells In A Range
- Example #3 – Get The Address Of The First Cell In A Range
- Example #4 – Get The Address Of The Last Cell In A Range
- Important Things To Note
- Frequently Asked Questions (FAQs)
- Download Template
- Recommended Articles

###### Key Takeaways

- The COLUMN function returns the column number of the given cell reference.
- The syntax of the COLUMN function
.*is =COLUMN([reference])* - The reference argument could not refer to multiple areas.
- If we do not enter the argument value, the function will automatically refer to the cell that contains the COLUMN formula.
- An array is a range or contiguous group of cells.
- The ROW function counts the number of rows.
- Similarly, the COLUMN function counts the number of columns and it always returns a numeric value.

### Syntax

= This is the mandatory argument. This refers to a range of cells with which the number of columns is calculated.*array*

### How To Use COLUMNS Formula In Excel?

**1. Access From The Excel Ribbon**

- To begin with, choose the empty cell which will contain the result.
- Then, go to the
**Formulas**tab and then, click it. - Next, select the
**Lookup & Reference**option from the menu. - Finally, choose
**COLUMNS**from the drop-down menu.

- A window called
**Function Arguments**appears. - Next, enter the value in the
**array.** - Then, select
**OK.**

**2. Enter The Worksheet Manually**

- To start with, select an empty cell for the output.
- Next, type
**=COLUMNS(**in the selected cell. Alternatively, type**=C**and double-click the**COLUMNS function**from the list of suggestions Excel shows. - Finally, press
**Enter**key.

### Examples

### Example #1 – Total Columns In A Range

The following example depicts the name and addresses of people, and we will calculate the total number of columns in a range using the COLUMNS functions in Excel.

In the table,

- Column A, B, C, D, E, and F contains the first name, last name, street, city, state and postal code respectively.
- We need to find the number of columns in row 5.

The steps to calculate the number of columns by the **COLUMNS Function in Excel **are as follows:

**Step 1:**First, select the column where we will enter the formula and calculate the result. The selected cell, in this case, is cell B5.

**Step 2:**Next, we will enter the**Excel COLUMNS Formula**in cell B5.

**Step 3:**Then, enter the value of the**array**argument as A1:F3, i.e., the range of columns.

**Step 4:**Now, the complete formula entered is.*=COLUMNS(A1:F3)*

**Step 5:**Press**Enter**key.

We can see the results in cell B5 as 6.

Similarly, we can find the number of columns using columns function in excel.

### Example #2 – Total Cells In A Range

The following example depicts the name and people’s employment details, and we will calculate the total number of cells in a range using the COLUMNS functions with the ROWS function in Excel.

In the table,

- Column A, B, C, D, E, and F contains the name of employees, year of joining, city, number of columns and number of cells in a range.
- Row 3 contains the Formula.

The steps to calculate the number of columns by the **COLUMNS Function in Excel **are as follows:

**Step 1:**To begin with, select the column where we will enter the formula and then, calculate the result of the total number of columns. The selected cell, in this case, is cell E2.

**Step 2:**Next, we will enter the**Excel COLUMNS Formula**in cell E2.

**Step 3:**Then, enter the value of the**array**argument as A1:C7, i.e., the range of columns.

**Step 4:**The complete formula entered is**=COLUMNS(A1:C7)**.

**Step 5:**Now, press**Enter**key. The result is obtained in cell E2 as 3.

**Step 6:**Select the column where we will enter the formula and calculate the result of the total number of cells. The selected cell, in this case, is cell F2.

**Step 7:**Next, we will enter the Excel COLUMNS Formula and ROWS formula in cell F2.

**Step 8:**Enter the value of the**array**argument as A1:C7, i.e., the range of columns.

**Step 9:**Similarly, enter the value of the**array**argument as A1:C7, i.e., the range of rows.

**Step 10:**The complete formula entered is**=COLUMNS(A1:C7) * ROWS(A1:C7)**.

**Step 11:**Next, press**Enter**key. The result is derived in cell F2 as shown in the image below.

### Example #3 – Get The Address Of The First Cell In A Range

The following example depicts the region-wise monthly sales, and we will calculate the address of the first cell in a range using the COLUMNS functions with the ROWS and ADDRESS functions in Excel.

In the table,

- Columns A, B, C, D contains the Region, Month, Sales
- Columns E, F, and G shows the number of columns, number of cells in a range, and first cell address of the data.
- Row 3 contains the formula.

The steps to calculate the number of columns, cells, and first cell addresses by the **COLUMNS, ROWS & ADDRESS functions in Excel** are as follows:

**Step 1:**First, select the column where we will enter the formula and calculate the result of the total number of columns. The selected cell, in this case, is cell E2.

**Step 2:**Next, we will enter the**Excel COLUMNS Formula**in cell E2.

**Step 3:**Then, enter the value of the**array**argument as A1:C7, i.e., the range of columns.

**Step 4:**The complete formula entered is**=COLUMNS(A1:C7)**.

**Step 5:**Now, press**Enter**key. The result is obtained in cell E2 as shown in the image below.

**Step 6:**Select the column where we will enter the formula and calculate the result of the total number of cells. The selected cell, in this case, is cell F2.

**Step 7:**Next, we will enter the Excel COLUMNS Formula and ROWS formula in cell F2.

**Step 8:**Then, enter the value of the**array**argument as A1:C7, i.e., the range of columns.

**Step 9:**Similarly, enter the value of the**array**argument as A1:C7, i.e., the range of rows.

**Step 10:**The complete formula entered is**=COLUMNS(A1:C7) * ROWS(A1:C7)**.

**Step 11:**Press**Enter**key. The result is derived as 21 in cell F2.

Next, we will calculate the first cell address of the data.

**Step 12:**Select the table as A1:C7.

**Step 13:**Right-click on the selected table.

**Step 14:**Select the**Define Name…**option from the popped-up list.

**Step 15:**A window called the**New Name**opens up.

**Step 16:**Set the table’s name; in this case, it is**Data.**

**Step 17:**Click**OK.**

**Step 18:**Then, select the column where we will enter the formula and calculate the result of the first cell address. The selected cell, in this case, is cell G2.

**Step 19:**Next, we will enter the Excel COLUMN, ROW, and ADDRESS formulas in cell G2.

**Step 20:**Enter the value of the**row number**argument as ROW(data), i.e., the range of rows.

**Step 21:**Similarly, enter the value of the**column number**argument as COLUMN(data), i.e., the range of columns.

**Step 22:**So, the complete formula entered is**=ADDRESS(ROW(Data), COLUMN(Data))**.

**Step 23:** Now, press **Enter** key. The result is obtained in cell G2 as **$A$1**.

Likewise, we can use COLUMNS function in excel to get desired results.

### Example #4 – Get The Address Of The Last Cell In A Range

The following example depicts the students’ marks, and we will calculate the address of the last cell in a range using the COLUMNS functions with the ROWS and ADDRESS functions in Excel.

In the table,

- Column A, B, C, D, E, F, G, and H contains the roll number, name, marks, number of columns, number of cells in a range, first cell address of the data, and, last cell address of the data.
- Row 3 contains the Formula.

The steps to calculate the number of columns, cells, and first cell addresses by the **COLUMNS, ROWS & ADDRESS functions in Excel **are as follows:

**Step 1:**First, select the column where we will enter the formula and calculate the result of the total number of columns. The selected cell, in this case, is cell E2.

**Step 2:**Next, we will enter the**Excel COLUMNS Formula**in cell E2.

**Step 3:**Then, enter the value of the**array**argument as A1:C7, i.e., the range of columns.

**Step 4:**Now, the complete formula entered is**=COLUMNS(A1:C7)**.

**Step 5:**Next, press**Enter**key. The result is derived in cell E2 as shown in the image below.

**Step 6:**Then, select the column where we will enter the formula and calculate the result of the total number of cells. The selected cell, in this case, is cell F2.

**Step 7:**Next, we will enter the Excel COLUMNS Formula and ROWS formula in cell F2.

**Step 8:**Then, enter the value of the**array**argument as A1:C7, i.e., the range of columns.

**Step 9:**Similarly, enter the value of the**array**argument as A1:C7, i.e., the range of rows.

**Step 10:**Now, the complete formula entered is**=COLUMNS(A1:C7) * ROWS(A1:C7)**.

**Step 11:**Press**Enter**key. The result is shown in cell F2 as 21.

Now, let us calculate the first cell address of the data.

**Step 12:**Select the table as**A1:C7.**

**Step 13:**Right-click on the selected table.

**Step 14:**Select the**Define Name…**option from the popped-up list.

**Step 15:**A window called**New Name**opens up.

**Step 16:**Set the table’s name; in this case, it is**Data.**

**Step 17:**Click**OK**.

**Step 18:**Select the column where we will enter the formula and calculate the result of the first cell address. The selected cell, in this case, is cell G2.

**Step 19:**Next, we will enter the Excel COLUMN, ROW, and ADDRESS formulas in cell G2.

**Step 20:**Enter the value of the**row number**argument as ROW(data), i.e., the range of rows.

**Step 21:**Similarly, enter the value of the**column number**argument as COLUMN(data), i.e., the range of columns.

**Step 22:**The complete formula entered is**=ADDRESS(ROW(Data), COLUMN(Data))**.

**Step 23:**Now, press**Enter**key. The result is obtained in cell G2 as shown in the image below.

Next, let us calculate the last cell address of the data.

**Step 24:**Then, select the column where we will enter the formula and calculate the result of the last cell address. The selected cell, in this case, is cell H2.

**Step 25:**Next, we will enter the Excel COLUMN, ROW, and ADDRESS formulas in cell H2.

**Step 26:**Enter the value of the**row number**argument as ROW(data)+ROWS(data)-1, i.e., the range of rows.

**Step 27:**Next, enter the value of the**column number**argument as COLUMN(data)+COLUMNS(data)-1, i.e., the range of columns.

**Step 28:**So, the complete formula entered is**=ADDRESS(ROW(Data)+ROWS(Data)-1,COLUMN(Data)+COLUMNS(Data)-1)**.

**Step 29:**Now, press**Enter**key. The result is shown in cell H2 as $C$7.

### Important Things To Note

- The
**COLUMNS Function in Excel**accepts a single cell address or a range of cells as an argument. - The COLUMNS function cannot take multiple references as arguments.
- If the array contains multiple rows and columns, only the columns are counted.
- The COLUMNS function range reference must be a
**contiguous range.**

### Frequently Asked Questions (FAQs)

**1. When to use Excel COLUMNS Function?**

The Excel COLUMNS function is used when we want to count the number of columns in a specified range or array.

**2. How does the COLUMNS function work in Excel?**

The COLUMNS function works in Excel using the following steps;**• **First, select an empty cell to enter the Formula.**• **Next, type **=COLUMNS(** in the selected cell. Alternatively, type **=C** and double-click the **COLUMNS function **from the list of suggestions Excel shows.**• **Then, press **Enter** key.

For example, the image below depicts the price of fruits, and we will calculate the total number of columns in a range using the COLUMNS functions in Excel.

The steps to calculate the number of columns by the **COLUMNS Function in Excel **are as follows:**• Step 1:** First, select the column where we will enter the formula and then, calculate the result. The selected cell, in this case, is cell C2.**• Step 2: **Next, we will enter the **Excel COLUMNS Formula** in cell C2.

• **Step 3:** Then, enter the value of the **array** argument as A1:B6, i.e., the range of columns. **• Step 4: **So, the complete formula entered is **=COLUMNS(A1:B6)**.**• Step 5:** Now, press **Enter** key. The result is obtained in cell C2 as 2.

**3. Where is the COLUMNS formula in Excel?**

One can activate the **COLUMNS Function in Excel **using the following steps:**• **First, choose the empty cell which will contain the result.**• **Go to the **Formulas **tab and click it.**• **Next, select the **Lookup & Reference **group.**• **Then, select **COLUMNS** from the drop-down menu.**• **A window called **Function Arguments **appears.**• **As the number of arguments, enter the value in the **array**. **• **Select **OK.**

### Download Template

This article must help understand the **COLUMNS Excel Function’s** formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to COLUMNS Function in Excel. Here we explain how to use COLUMNS formula along with examples & downloadable template. You can learn more from the following articles –

## Leave a Reply