WallStreetMojo

WallStreetMojo

WallStreetMojo

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

Excel Column to Number

Column Letter to Number in Excel

Figuring out which row you are in is as easy as you like, but how do you tell which column you are in at the moment. Excel has 16384 columns in total, which are represented by alphabetic characters in excel. Suppose if you want to find the column CP how do you tell?

Yes, it is almost an impossible task to figure out the column number in excel. However, nothing to worry about because, in excel, we have a built-in function called COLUMN in excel, which can tell the exact column number you are in right now, or you can find the column number of the supplied argument as well.

Excel-Column-to-Number

How to Find Column Number in Excel? (with Examples)

You can download this Column to Number Excel template here – Column to Number Excel template

Example #1

Get Current Column Number by using the COLUMN Function in Excel.

  • I have opened a new workbook and typed some of the values in the worksheet.

Excel Column to Numbers example 1.1

  • Let’s say you are in the cell D7, and you want to know the column number of this cell.

Excel Column to Numbers example 1.2

  • In order to find the current column number, write the COLUMN function in excel cell, and do not pass any argument; just close the bracket.

Excel Column to Numbers example 1.3

  • Press Enter, and we will have a current column number in excel.

Excel Column to Numbers example 1.4

Example #2

Get Column Number of Different Cell by using COLUMN Function in Excel

Getting the current column isn’t the toughest task at all. Let’s say if you want to know the column number of the cell CP5, how you get that column number.

  • In any of the cells, write the COLUMN function and pass the specified cell value.

Excel Column to Numbers example 2.1

  • Then press Enter, and it will return the column number of CP5.

Excel Column to Numbers example 2.2

I have applied the COLUMN formula in cell D6 and passed the argument as CP5, i.e., cell reference of CP5 cell. Unlike normal cell reference, it will not return the value in the cell CP5; rather, it will return the column number of CP5.

So the column number of the cell CP5 is 94.

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

Example #3

Get How Many Columns are selected in the Range by Using COLUMNS Function in Excel.

Now we have learned how to get the current cells column number and specified cell column number in excel. How do you tell how many columns are selected in the range?

We have another built-in function called COLUMNS function in excel, which can return the number of columns selected in the formula range.

Excel Column to Numbers 2

Assume you want to know how many columns are there from the range C5 toN5.

  • In any of the cells, open the formula COLUMNS and select the range as C5 toN5.

Excel Column to Numbers example 3.1

  • Hit enter to get the desired result.

Excel Column to Numbers example 3.2

So totally, we have selected 12 columns in the range C5 toN5.

In this way, by using the COLUMN & COLUMNS function in Excel, we can get the two different kinds of results, which can help us in calculations or identify the exact column when we are dealing with huge datasets.

Example #4

Change Cell Reference Form to R1C1 References

In Excel, by default, we have cell references, i.e., all the rows are represented numerically, and all the columns are represented alphabetically.

Excel Column to Numbers example 4.1

This is the usual spreadsheet structure we are familiar with. The cell reference is started by the Column alphabet and then followed by row numbers.

As we learned earlier in the article, to get the column number, we need to use the COLUMN function. How about changing the column headers from the alphabet to numbers just like our row headers, like the below image.

Example 4.2

This is called ROW-COLUMN reference in excel. Now take a look at the below image and reference type.

Example 4.3

Unlike our regular cell reference, here, reference starts with a Row number then followed by a column number, not an alphabet.

Follow the below steps to change it to the R1C1 Reference Style.

  • Go to FILE and OPTIONS.

Example 4.4

  • Go to Formulas under OPTIONS.

Excel Options

  • Working with Formulas, select the checkbox R1C1 Reference Style and click ok.

Example 4.6

Once you click ok, then cells will change to R1C1 References, as shown above.

Things to Remember

  • The R1C1 cell reference is the rarely followed cell reference in excel. You may get confused easily at the start.
  • In normal cell references, we see column alphabet first and row number next. But in R1C1 cell references, the row number will come first and the column number.
  • COLUMN function can return both the current column number as well as the supplied column number.
  • R1C1 cell reference makes it easy to find the column number easily.

Recommended Articles

This has been a guide to Column Letter to Numbers in Excel. Here we discuss how to find column numbers in excel using the COLUMN and COLUMNS function. You may learn more about excel from the following articles –

  • Excel Rows Function
  • Excel Rows vs. Columns
  • Excel Rows and Columns
  • Concatenate Excel Columns
0 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 to Number Excel template

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