**COLUMNS in Excel (Table of Contents)**

## COLUMNS in Excel

**COLUMNS function:**It’s an in-built or pre-built integrated function in excel that is categorized under Lookup & Reference function.- COLUMNS function is most commonly used when we want to get the number of columns in a specified array or range i.e. Total columns in a range.
- Columns Function in Excel is flexible, easy to use & very simple function with only one argument which needs to be entered.
- It is also used or integrated with other excel functions (eg. Columns function along with row function, Vlookup and other functions with single or multiple criteria) to get the desired output.

**Definition**

Returns the number of columns in a cell reference or array.

### COLUMNS Formula in Excel

The Formula for the COLUMNS Function in Excel is as follows:

**The COLUMNS Function formula has a below-mentioned argument:**

**Array: **reference to an array OR range of cells for which we have to calculate a number of columns.

**Note:**

- If the range of cells or array contains multiple rows and columns, only the columns are counted
- COLUMNS Function in Excel will always return a positive numeric value.

**How to Use** COLUMNS Function** in Excel?**

This COLUMNS Function is very simple easy to use. Let us now see how to use the COLUMNS Function in Excel with the help of some examples.

### COLUMNS in Excel Example #1**– **To Find out Total Columns in a Range

In the below-mentioned example, I have a dataset in a range i.e. B7:G13, where it contains company & its share value on a daily basis. Here I need to find out the number of columns in that range with the help of COLUMNS Function.

Let’s apply COLUMNS function in cell “I8”.

Select the cell “I8” where COLUMNS function needs to be applied, Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “COLUMNS” in the search for a function box, COLUMNS function will appear in select a function box.

Double click on COLUMNS function, a dialog box appears where arguments for COLUMN function needs to be filled or entered i.e. =COLUMNS (array)

**Array: **Reference to an array OR range of cells for which we have to calculate a number of columns i.e. Here it is B7:G13 where Here, B7 is a starting cell and G13 is the ending cell in a table or range.

Click ok, after entering the arguments in COLUMNS function. i.e.**=**COLUMNS (B7:G13) COLUMNS function calculates the total number of columns present in a range i.e. 6. The number of columns in between these two cells (B7 & G13) is 6, hence it results in 6.

### COLUMNS in Excel Example #2 – COLUMNS Function with row Function to Find out a Total cell in a range

In the below-mentioned example, I have a dataset in a range i.e. B18:G24, where it contains company & its share value on a daily basis. Here I need to find out the Total cell in range with the help of COLUMNS function.

Let’s apply COLUMNS function in cell “I19”**.**

Select the cell “I19” where COLUMNS function needs to be applied, Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “COLUMNS” in the search for a function box, COLUMNS function will appear in select a function box.

Double click on COLUMNS function, A dialog box appears where arguments for COLUMN function needs to be filled or entered i.e.

=COLUMNS (array)

**Array: **reference to an array OR range of cells for which we have to calculate a number of columns i.e. Here it is B18:G24 where Here, B18 is a starting cell and G24 is the ending cell in a table or range.

Multiplication needs to be performed between the total number of columns and a total number of rows for the given array or range of cells to get the total number of cells in an array or table.

i.e. **=**COLUMNS(B18:G24)*ROWS(B18:G24)

Here, the total number of rows is 7 & a total number of columns is 6. So, the total number of cells is 7*6 = 42

### COLUMNS in Excel Example #3 – COLUMNS Function in VLOOKUP Formula

The COLUMNS function is important when using functions where a column argument is required e.g. VLOOKUP in case of a huge number of datasets, it is not an easy task to manually count the number of columns in a table array.

During these scenarios, In VLOOKUP function, The COLUMNS function is used in it where it returns the col_index_num argument value.

Let check out the difference between Vlookup function with & without Columns Function

In the below-mentioned example, table2 contains company in column B & its share value on a daily basis ( 6^{th}, 7^{th},8^{th}, 9^{th} & 10^{th}Nov 2018) in column C, D, E, F & G respectively.

Table 3 contains company name in column B, our objective here in this table, is to find out its share value on 6^{th} Nov 2018 in column C, with the help of table 2 reference by using VLOOK function.

Prior to applying VLOOKUP formula, you should be aware of it. Vertical lookup or VLOOKUP references vertically-aligned tables and quickly finds data in relation to the value the user enters.

**VLOOKUP Function without COLUMNs Function**

Let’s apply VLOOKUP function in cell “C29”.

Select the cell “C29”. where VLOOKUP function needs to be applied, Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “VLOOKUP” in the search for a function box, VLOOKUP function will appear in select a function box.

Double click on VLOOKUP function, A dialog box appears where arguments for VLOOKUP function needs to be filled or entered.

The Formula for the VLOOKUP function is:

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

**lookup_value**: The value you want to look up i.e. “B29” or “MEDICO”

**table_array**: Range where the lookup value is located i.e. select table 2 range B18:G24 & click function f4 key to lock a range i.e. $B$18:$G$24.

**col_index_num:** column number in a table array from which the matching value should be returned. Here the company share value on 6^{th} November 2018 is in table 2 & it is in the second column i.e**.** 2

**range_lookup: **FALSE for an exact match or TRUE for an approximate match. Select 0 or false.

**=**VLOOKUP(B29,$B$18:$G$24,2,0) returns the share value of Medco on 6^{th} November 2018 i.e. 69

To get the finalized data for other company share price, click inside cell C29 and you’ll see the cell selected, then Select the cells till C31. So that column range will get selected, once it got selected click on Ctrl + D so that the VLOOKUP formula is applied to a whole range.

**VLOOKUP Function with COLUMNS Function**

Above the table 2 column header, B17 to G17 is the column number which needs to be mentioned prior to applying Vlookup with columns function.

Table 4 contains company name in column G, our objective here in this table, is to find out its share value on 8^{th} Nov 2018 in column H, with the help of table 2 reference by using VLOOK with COLUMNS function.

In VLOOKUP Function without COLUMNS Function, Formula was**:**

=VLOOKUP(C29,$B$18:$G$24,2,0)

Where 2 was the column index number, here let’s replace this by COLUMNS($B$17:$E$17) function in the Vlookup function in cell H29 of table 4. Here we need a share price value for 8^{th} Nov 18, hence the select column reference B17 to E17 in columns function of the table.

i.e. **=**VLOOKUP(G29,$B$18:$G$24,COLUMNS($B$17:$E$17),0)

**=**VLOOKUP(G29,$B$18:$G$24,COLUMNS($B$17:$E$17),0) returns the share value of Medco on 8^{th} November 2018 i.e. 68

To get the finalized data for other company share price, click inside cell H29 and you’ll see the cell selected, then Select the cells till H31. So that column range will get selected, once it is selected Press Ctrl + D so that the formula is applied to a whole range.

Here simultaneously, we have to change the column reference in column function & lookup value based on the data requirement

**Things to Remember About** COLUMNS** Function in Excel**

- The COLUMNS Function in Excel is important or significant when using functions where a column argument is required (e.g. VLOOKUP), In case of a huge number of datasets, it is not an easy task to manually count the number of columns in a table array (Explained in example 3).
- The array argument in the COLUMNS Function can be either a range of cells or single cell address
- If the range of cells or array contains multiple rows and columns, only the columns are counted
- COLUMNS function in Excel will always return a positive numeric value.

### Recommended Articles

This has been a guide to COLUMNS in Excel. Here we discuss the COLUMNS Formula in Excel and how to use COLUMNS Function in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –