WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Column Function in Excel

Column Function in Excel

Excel Column Function

Column function in excel is used to find out the column numbers of the target cells in excel, it is also a built in worksheet function and takes only one argument which is the target cell as reference, note that this function does not give the value of the cell as it returns only the column number of the cell. In simple words, this excel column formula will provide a numeric value as output which signifies the column number of the supplied reference.

Syntax

Column Function formula in excel

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? (Examples)

You can download this Column Function Excel Template here – Column Function Excel Template

Example #1

The column function provides the column number of the current cells where the column formula is located if the reference is excluded or omitted, as shown in the below table.

Column Function formula in excel Example - 1

Example #2

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

Column-Function-formula-in-excel-Example-2-1

Example #3

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

Column-Function-formula-in-excel-Example-3-1

Example #4

Column function with vlookup function in Excel.

The column function can be used with other functions. Here we are using this function with lookup 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 the vlookup function combined with this function, as shown below.

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

Example - 4

Example #5

Column function with mod function. 

Suppose a cable connection expense that is payable every third month and the 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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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

Example - 5

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 the 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 the below table.

Example - 6

Column Function formula in excel Example - 6-1 

Example #7

Suppose we have to find out the last column in a range, then we can min function in excel; the details are 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 has 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:

Example - 7

Point to Remember

  • This function will return a #NAME! Error if we supply the invalid reference.

Recommended Articles

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

  • Column Lock in Excel
  • Compare Two Columns in Excel for Matches
  • Adding Columns in Excel
  • Move Columns in Excel
2 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Column Function Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More