Shakil Ahmed
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 COLUMN(B4:E8)+COLUMNS(B4:E8)-1 returned {5;6;7;8}
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.
