COLUMNS in Excel (Table of Contents)
COLUMNS in Excel
The columns function in excel is another easiest function to apply, which simply counts the number of columns that are selected. It returns the count of the number of columns selected in the array. For this, we just have to select the complete columns or columns in the form of a table or matrix. It will simply return the count of columns there in an 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.
Example #1
In the below-mentioned example, I have a dataset in a range, i.e. B7:G13, where it contains the 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 the COLUMNS Function.
Let’s apply the COLUMNS function in cell “I8”.
Select the cell “I8” where the COLUMNS function needs to be applied, Click the insert function button (fx) under the 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 the 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.
Example #2
In the below-mentioned example, I have a dataset in a range, i.e. B18:G24, where it contains the company & its share value on a daily basis. Here I need to find out the Total cell in range with the help of the COLUMNS function.
Let’s apply the COLUMNS function in cell “I19”.
Select the cell “I19” where the COLUMNS function needs to be applied, Click the insert function button (fx) under the 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 & the total number of columns is 6. So, the total number of cells is 7*6 = 42
Example #3
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 easy to manually count the number of columns in a table array.
During these scenarios, In the VLOOKUP function, The COLUMNS function is used to return the col_index_num argument value.
Let check out the difference between the Vlookup function with & without Columns Function.
In the below-mentioned example, table2 contains the company in column B & its share value on a daily basis ( 6th, 7th,8th, 9th & 10thNov 2018) in columns C, D, E, F & G, respectively.
Table 3 contains the company name in column B; our objective here in this table is to find out its share value on 6th Nov 2018 in column C, with the help of table 2 reference by using the VLOOKUP function.
Prior to applying the 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 the VLOOKUP function in cell “C29”.
Select the cell “C29”. where the VLOOKUP function needs to be applied; click the insert function button (fx) under the 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 the VLOOKUP function, A dialog box appears where arguments for the VLOOKUP function need 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 6th 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 6th November 2018, i.e. 69
To get the finalized data for other company share prices, 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 that needs to be mentioned prior to applying Vlookup with the columns function.
Table 4 contains the company name in column G; our objective here in this table is to find out its share value on 8th 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 with the COLUMNS($B$17:$E$17) function in the Vlookup function in cell H29 of table 4. Here we need a share price value for 8th 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 8th November 2018, i.e. 68
To get the finalized data for other company share prices, 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
- The COLUMNS Function in Excel is important or significant when using functions where a column argument is required (e.g. VLOOKUP); in the 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 addresses.
- 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 examples and downloadable excel templates. You may also look at these useful functions in excel –
