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 now? Excel has 16,384 columns, represented by alphabetic characters in Excel. So, suppose you want to find the column CP. How do you tell?

Yes, it is almost impossible to figure out the column number in Excel. However, nothing to worry about because 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 we are in right now or find the column number of the supplied argument.

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

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

Example #1

Example #2

We can get the column number of the different cells by using the COLUMN function in Excel.

  • We have opened a new workbook and typed some of the values in the worksheet. Let us say we are in cell D7, and we want to know the column number of this cell. To find the current column number, we must write the COLUMN function in the Excel cell and do not pass any argument; close the bracket. Press the “Enter” key. As a result, we will have a current column number in Excel.

Getting the current column is not the toughest task at all. Suppose we want to know the column number of the cell CP5 and how we get that column number.

  • We can write the COLUMN function and pass the specified cell value in any cells.

  • Then press the “Enter” key. It will return the column number of CP5.

We 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. However, 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

We can get how many columns are selected in the range by using the COLUMNS function in Excel.

We have learned how to get the current cell column number and specified cell column number in Excel. But, how do you tell how many columns are selected in the range?

We have another built-in function called the COLUMNS function in excelCOLUMNS Function In ExcelThe COLUMNS function 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.

Assume we want to know how many columns are from the range C5 to N5.

  • We can open the formula COLUMNS in any cell and select the range as C5 toN5.

  • Press the “Enter” key to get the desired result.

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

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

Example #4

We can change the cell reference form to R1C1 references in Excel.

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

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

It is called ROW-COLUMN reference in Excel. Now, take a look at the below image and the reference type.

Unlike our standard cell reference, reference starts with a row number followed by a column number, not an alphabet.

Follow the below steps to change it to the R1C1 reference style.

  • We must first go to the “File” and “Options.”

  • Next, go to “Formulas” under “Options.”

  • Working with “Formulas,” select the checkbox “R1C1 reference style” and click “OK.”

Once we click “OK,” cells will change to R1C1 references.

Things to Remember

  • The R1C1 cell reference is the rarely followed cell reference in Excel. As a result, we may get confused easily at the start.We see column alphabet first and row number next in normal cell references. But in R1C1 cell references, the row number will come first and the column number.The COLUMN function can return the current column number and the supplied column number.The R1C1 cell reference makes it easy to find the column number easily.

This article has been a guide to Column Letter to Numbers in Excel. We discuss finding column numbers in Excel using the COLUMN and COLUMNS functions. You may learn more about Excel from the following articles: –

  • Excel Rows FunctionExcel Rows vs. ColumnsExcel Rows and ColumnsConcatenate Excel Columns