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 excelCOLUMN In ExcelColumn function finds out the column numbers of the target cells in excel. It takes 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. read more, 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

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Excel Column to Number (wallstreetmojo.com)

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.

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

    Excel Column to Numbers example 1.1

  2. 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

  3. 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

  4. 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 Cell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more 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.

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 excelCOLUMNS Function In ExcelCOLUMNS function falls under the category of Lookup Functions in Excel. It returns the total number of columns in the given array or collection of references. read more, 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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion