Column Function in Excel

Column Function in Excel (Table of Contents)

• Column Function Formula
• Column Formula in Excel
• How to Use?

Column Function in Excel

Column excel function is lookup/reference function in excel used to determine the first column value from the supplied reference range. It can be used as worksheet function.

In simple words, this function will provide a numeric value as output which signifies the column number of the supplied reference.

Column Formula in Excel

Recommended Courses

Explanation of Column function in Excel

Column function in excel is used one parameter [reference] as input and returns the column value as output.

Optional parameter:

• [Reference]: reference parameter is optional as if you did not supply the reference to this function it will return the current column number where the function is located.

How to use Column Excel Function?

Column Excel function is very simple and easy to use. Let understand the working of column excel function by some examples.

Excel Column Example #1

Column function in excel provides the column number of current cell where column formula is located if the reference is excluded or omitted as shown in below table.

 

Excel Column Example #2

If the range C5 is supplied to the column function in excel then it will return the column number 3 as output.

Excel Column Example #3

If a range is supplied to column function in excel as input it will return the first column number as output for below table the output will be 3.

Column function with other excel formulas. 

Here are some examples of column function in excel with other excel formulas

Excel Column Example #4

Column excel function with vlookup function in Excel.

Column function in excel can be used with others functions. Here we are using this function with vlookup function.

Suppose we an employee data containing ID, Name and Salary column and we have to find out the name from ID then we can use vlookup function combined with this function as shown below.

=VLOOKUP(B11,$B$11:$D$15,COLUMN(C11),0)

Excel Column Example #5

Column excel function with mod function. 

Suppose a cable connection expense that is payable every third month and cable connection expense amount is 500 and if we want to generate a fixed value every third month then we can use column formula with the MOD function as shown below.

The column formula in excel used is =IF(MOD(COLUMN(B8)-1,3)=0,$A$2,0), as shown below:

Excel Column Example #6

Suppose we need to get the address of the first cell in a supplied range so we can use the ADDRESS function with ROW and COLUMN. The column formula in excel to be used is below:

=ADDRESS(ROW(B24:D24)+ROWS(B24:D24)-1,COLUMN(B24:D24)+COLUMNS(B24:D24)-1)

This column formula in excel returns the ADDRESS of first cell based on a row and column number. here, we use the ROW function to generate a list of row numbers then add ROWS(B5:D5)-1 to ROW(B24:D24) , so that the first item in the array is the last row number.

Then we do the same for COLUMN and COLUMNS: COLUMN(B24:D24)+COLUMNS(B24:D24)-1)

After applying this function it will return an array of addresses. If we enter the column formula in a single cell, we just get the item ($D$24) from the array/range, which is the address corresponding to the last cell in a range as shown in below table.

 

Excel Column Example #7

Suppose we have to find out the last column in a range then we can use this function min function the details as follows:

=MIN(COLUMN(B23:D26))+COLUMNS(B23:D26)-1

As you know if we provide a single cell as a reference to the COLUMN, it will return the column number as output for that particular reference/cell. However, when we supply a range that have multiple columns, this function will give an array as output that contains all column numbers for the given range. If we want to get only the first column number as output, we need to use the MIN function to get just the first column number, which will be the lowest number in the array. The result is shown below:

Things to remember about the Column Function Excel

• Column function excel will return a #NAME! Error is we supply the invalid reference.

You can download this Column function in Excel template here – Column Function Excel Template

Recommended Articles

This has been a guide to Column Function in Excel. Here we discuss the Column Formula and how to use Column function along with practical examples and downloadable excel templates. You can also go through our other suggested articles –

• PV in Excel
• FV in Excel
• NPV function in Excel
• Top 15 Financial Functions
• PMT Excel Function