Our Service


In this comprehensive guide, we will explore everything you need to know about the COLUMN function in Microsoft Excel. The COLUMN function is a useful tool for returning the column number of a given cell reference. This function can be particularly helpful when working with large datasets, as it allows you to quickly identify the column number of a specific cell without having to manually count columns.


The syntax for the COLUMN function is quite simple, with only one optional argument:


reference (optional): This is the cell reference for which you want to find the column number. If the reference argument is omitted, the function will return the column number of the cell in which the formula is entered.

COLUMN Examples

Let’s take a look at some examples of how to use the COLUMN function in Excel:

Example 1: Basic usage

If you want to find the column number of cell D5, you can use the following formula:


This formula will return the value 4, as D is the fourth column in the spreadsheet.

Example 2: Omitting the reference argument

If you want to find the column number of the cell in which the formula is entered, you can simply use the COLUMN function without any arguments:


For instance, if you enter this formula in cell F3, it will return the value 6, as F is the sixth column in the spreadsheet.

COLUMN Tips & Tricks

Here are some tips and tricks to help you get the most out of the COLUMN function:

Tip 1: Using COLUMN with other functions

You can use the COLUMN function in combination with other functions to perform more complex calculations. For example, you can use the COLUMN function with the INDEX function to return the value in a specific row and column:

=INDEX(A1:E5, 3, COLUMN(D1))

This formula will return the value in the third row and fourth column of the range A1:E5, as the COLUMN function returns 4 for cell D1.

Tip 2: Incrementing values with COLUMN

You can use the COLUMN function to create a series of incrementing values. For example, if you want to create a series of numbers starting from 1 in row 1, you can use the following formula:


By copying this formula across the row, you will get a series of incrementing numbers (1, 2, 3, etc.) based on the column number.

Common Mistakes When Using COLUMN

Here are some common mistakes to avoid when using the COLUMN function:

Mistake 1: Using an invalid reference

Make sure to use a valid cell reference as the argument for the COLUMN function. If you use an invalid reference, Excel will return a #REF! error.

Mistake 2: Confusing column numbers with column letters

Keep in mind that the COLUMN function returns the column number, not the column letter. If you need to convert a column number to a column letter, you can use a combination of the CHAR and COLUMN functions:

=CHAR(COLUMN(A1) + 64)

This formula will return the letter “A”, as the COLUMN function returns 1 for cell A1, and adding 64 to this value gives the ASCII code for the letter “A”.

Why Isn’t My COLUMN Function Working?

If your COLUMN function isn’t working as expected, consider the following troubleshooting tips:

Tip 1: Check for errors in the formula

Make sure your formula is entered correctly and that there are no errors in the cell reference or other parts of the formula. Double-check the syntax and ensure that you are using the correct function name (COLUMN).

Tip 2: Verify the cell reference

Ensure that the cell reference you are using in the COLUMN function is valid and exists within the worksheet. If you are using a named range, make sure the name is spelled correctly and refers to the correct range.

COLUMN: Related Formulae

Here are some related formulae that you might find useful when working with the COLUMN function:

1. ROW

The ROW function returns the row number of a given cell reference. It has a similar syntax to the COLUMN function:



The COLUMNS function returns the number of columns in a given range:



The ROWS function returns the number of rows in a given range:



The ADDRESS function returns the cell address as a text string based on the given row and column numbers:

=ADDRESS(row_num, col_num, [abs_num], [a1], [sheet_text])


The INDEX function returns the value of a cell in a given range based on its row and column numbers:

=INDEX(array, row_num, [column_num], [area_num])

By mastering the COLUMN function and its related formulae, you can greatly enhance your efficiency and productivity when working with Excel spreadsheets.


Did you find this article useful?

Share it with your friends or colleagues

About Aepoch Advisors

We are a boutique accounting and consulting firm servicing international businesses operating in China. We offers book keeping and business advisory service. We also help our clients select and implement SAAS business applications
Cloud technology significantly reduces cost foreign companies traditionally spent on tax compliance and ERP systems. Our cloud professionals can help with streamlining your management and controling structure, as well as advising you on how to reduce risks and maximize profits with software purposely built for Chinese business. Contact us today to learn more about our services.