In this tutorial, you will learn how to find the first column number in a range. As the name suggests COLUMN function is a formula combining ‘FIRST’ and ‘COLUMN’ condition. It implies that this function can return the column number for that reference when given a single cell reference or items that satisfy particular criteria. The Excel worksheet has a built-in function called the ‘COLUMN’ which returns the column number for that reference in a given single cell reference based on a single criterion.
First column number in a range
= MIN (COLUMN [data])
In an Excel worksheet, COLUMN function is entered as a part of a formula in a single cell along with the first column number in the range. The method of how to use this function as a formula explanation is in the next section.
Excel COLUMN function uses the following arguments:
Range (required): The range of a single cell reference for which the column number returns.
Criteria (required): It determines an array that contains the entire column numbers for the range. Note that this parameter has the limit to a column, number or a range and the function can use array constants.
[Column range] (Optional): MIN function alone can be used to remove only the first column number, being the lowest in an array if you want.
Now let us try how to apply the formula in an Excel worksheet and understand them to step by step.
Let us take a simple example as shown below. Suppose we have three days sales report. Our objective is to find the total amount earned on the first day.
Figure 1. COLUMN function successfully applied to a single cell reference of 1st Sept sales.
In the above sample, COLUMN function was applied to F5 [row5 column B] in a single cell reference of first column number in the range.
We can also try and apply the COLUMN function formula to return first column number in multiple columns. The ‘range’ here will be cell F5 (row 5 and column B) to F10 (row 10 and column B).
In that case, the COLUMN function will return an array from F5 [row5 and column B] to F10 [row 10 and column B] respectively as the first column numbers in the range.
Figure 2. COLUMN function can return an array of ‘multiple columns’ that contains all the column numbers for the range based on the criteria.