COLUMN Function in Excel

COLUMN Function in Excel

Many times at work, especially if one is involved in IT, our days are smeared with “excels” and “spreadsheets”. The COLUMN Function is a Lookup/Reference Function. It is a very handy function that I have been using at work. I had run into a situation where the data was such that the columns went up to ‘DEF’ and I needed the cell reference of cells in the farthest reaches of the sheet. This Column Function is useful for finding out the column number of a given cell reference.

For example, the formula =COLUMN(DEF56) returns 2840, because column DEF is the 2840^th column.

COLUMN Formula in Excel

The Formula for the COLUMN function is as follows:

The COLUMN function asks for only one argument – that is the reference. It is the cell or a range of cells for which we want the column number(s). It returns a numeric value as the cell reference is always numeric.

It is important to note at this point:

1. The cell reference in question can be of a single cell or even a group of cells or range.
2. This reference argument is optional. If not provided by the user, then it simply returns the reference of the cell where the function is called.
3. Including multiple cell references in a single COLUMN function is not allowed.

So, now that the definition is covered, let’s see how this function works.

How to Use COLUMN Function in Excel?

This COLUMN is very simple easy to use. Let us now see how to use COLUMN function with the help of some examples.

It’s a built-in function that is used as a worksheet function and the best way to understand its functionality is through an example.

Example #1

In the first case, observe that we did not give a reference to the formula. So, it gave us the result 4 with the COLUMN function in cell C11 as shown below.

Now in the next case, a reference was provided- cell G5. Now column G is the 7^th column, hence the result displayed is 7. The formula used is mentioned in cell C12.

The third case shows how even a range reference can be used with this formula. It is able to return the column reference 2 in this case as the B is the second column.

Example #2

This function can be used along with other Excel formulae and that is where the real usefulness comes out.

Suppose we need to generate a table having the details for a home loan, where the installments are payable at the end of every quarter and the amount payable for this home loan per quarter is 200,000. So, it would become very easy for the user, if a formula could be developed which would automatically generate the fixed payment value for every third month.

So, we have the fixed loan amount mentioned in cell A2. Using this value, we generate the list for payments for each quarter as applicable by the following formula:

Here, we see that two functions are used- MOD and COLUMN. Now the calculation, in this case, is dependent on the MOD function. MOD is generally a commonly used function which is well suited for situations where it is required to do a specified act every nth time.

So, let’s break this full formula down to understand its mechanism. The first number is created using the COLUMN, which returns the column number of cell B8, which is the number 2, minus 1, which is hardcoded so that can force excel to always start counting with the number 1, irrespective of the actual column number.

The divisor is also hardcoded as 3 in the MOD function since the payments are to be made every quarter. By testing for a zero remainder, the expression will return TRUE at the 3^rd, 6^th, 9^th, and 12^th months, and FALSE for every other month, which satisfies our requirement. Now we put the formula thus far, inside an IF and set the TRUE value to pick from cell A2 and the FALSE is set to 0.

Example #3

One of the most commonly used functions is the VLOOKUP. Let’s see how the VLOOKUP can be used along with the COLUMN.

Suppose we a dataset having employee data, containing employee ID, Name and Salary information. Now, if we have to find out the name from ID then we can use VLOOKUP function combined with COLUMN as shown below:

The formula used to get the VLOOKUP NAME is:

=VLOOKUP(A2,A2:C4,COLUMN(B2),FALSE)

The formula used to get the VLOOKUP SALARY is:

=VLOOKUP(A2,A2:C4,COLUMN(C2),FALSE)

So basically, we are looking for the Employee Name using the Employee ID in VLOOKUP and the col_index_num in the VLOOKUP function is set using COLUMN. The same thing is done for VLOOKUP SALARY.

Recommended Articles

This has been a guide to COLUMN. 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 –

1. Excel Basic Functions For Beginners
2. Feature Of Excel – Printing Headers and Titles
3. Steps on How to Use Microsoft Excel Skills