EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Lookup & Reference Functions in Excel COLUMN Function in Excel
 

COLUMN Function in Excel

Manisha Sharma
Article byManisha Sharma
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated July 5, 2023

COLUMN Function in Excel

 

 

COLUMN Function in Excel

Column Function in Excel is one of the easiest functions to use, which returns the column number from the selected references. It means we select the complete end to end column or any range of cells of any column, and we will get the column number. The first column will show the number in question if we select more than one column in the range. And also, the sequence will be marked counting from column A as the first number.

COLUMN Formula in Excel

The Formula for the COLUMN function is as follows:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Column formula in Excel

The COLUMN function asks for only one argument – 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, it simply returns the cell’s reference where the function is called.
  3. Including multiple cell references in a single COLUMN, a function is not allowed.

Now that we have covered the definition, let’s see how this function works.

How to Use COLUMN Function in Excel?

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

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

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

Example #1

Column Function1

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

Column formula 1

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

Column formula 1.1

The third case shows how even a range reference can be used with this formula. In this case, it can return column reference 2, as B is the second column.

Column formula 1.2

Example #2

In conjunction with other Excel formulas, this function can be utilized. That is where the real usefulness comes out.

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. It would become very easy for the user if a formula could develop to automatically generate the fixed payment value for every third month.

Column Example 2

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

Column formula (function)

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

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 to force Excel always to 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 will be made quarterly. By testing for a zero remainder, the expression will return TRUE at the 3rd, 6th, 9th, and 12th months and FALSE for every other month, which satisfies our requirement.

The formula we have created so far is then placed inside an IF function, where the TRUE value picks from cell A2, and the FALSE value 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 have 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 the VLOOKUP function combined with COLUMN as shown below:

C example 2.1

C example 2.2

The formula used to get the VLOOKUP NAME is:

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

C formula 2

The formula used to get the VLOOKUP SALARY is:

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

C example 2.1

To look for the Employee Name using the Employee ID in VLOOKUP, we set the col_index_num in the VLOOKUP function using COLUMN. The same approach is used for the VLOOKUP SALARY.

Recommended Articles

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

  1. Column Header in Excel
  2. Freeze Columns in Excel
  3. Matching Columns in Excel
  4. Moving Columns in Excel

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

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

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

EDUCBA

Download COLUMN Function Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download COLUMN Function Excel Template

EDUCBA

डाउनलोड COLUMN Function Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW