COLUMN vs COLUMNS Function in Excel (Video in Khmer)

Hello everyone! Today I explain COLUMN vs COLUMNS function in Excel with simple example, so you can understand what is difference and when to use each one.

Just like ROW and ROWS, these two function look almost same but do very different thing. No worry, I explain step by step, very easy!

1. COLUMN Formula in Excel

COLUMN function return the column number of a cell. Column A = 1, Column B = 2, Column C = 3, and so on.

Syntax:

=COLUMN([reference])

• [reference] — Optional. The cell you want to know the column number. If you leave it empty, COLUMN return the column number of the cell where you type the formula.

Examples:

=COLUMN(A1)      → return 1   (column A = column 1)
=COLUMN(C5)      → return 3   (column C = column 3)
=COLUMN(F10)     → return 6   (column F = column 6)
=COLUMN()        → return column number of current cell

Common use case — auto column number for dynamic formula:

When you use INDEX or OFFSET and need to refer to column number, COLUMN help you avoid hardcode number. Example:

=INDEX(A1:F10, 2, COLUMN(C1))

This always refer to column 3 (column C), even if you move your formula to another place.

Video Tutorial in Khmer

💡 Tip: Use =COLUMN(A1) and drag to the right to create auto column number 1, 2, 3, 4, 5... same like how you use =ROW() to create row number. Very useful for dynamic formula!

2. COLUMNS Formula in Excel

COLUMNS function return how many columns are inside a range. You give it a range, it count and tell you total number of column in that range.

Syntax:

=COLUMNS(array)

• array — Required. The range you want count the column of. Example: A1:E1 or B2:F10

Examples:

=COLUMNS(A1:E1)      → return 5   (5 columns from A to E)
=COLUMNS(B2:F10)     → return 5   (5 columns from B to F)
=COLUMNS(A1:A20)     → return 1   (only 1 column, even many rows)
=COLUMNS(A:Z)        → return 26  (all 26 columns A to Z)

Common use case — count total column in your table:

If your table is in A1:G50 and you want know how many column your table have:

=COLUMNS(A1:G50)

👉 Result: 7 — your table have 7 column (A to G).

💡 Tip: COLUMNS is very useful when combine with INDEX to always get the last column. Example: =INDEX(A1:G1, COLUMNS(A1:G1)) — this always return the value in the last column of your range, even if you add more column later.

3. COLUMN vs COLUMNS — What is Difference?

Simple rule to remember:

• Use COLUMN when you want know where a cell is (column position/number)
• Use COLUMNS when you want know how many columns are in a range (count)

Examples

Example 1 — Auto Column Number When Dragging Right

You want create column number 1, 2, 3, 4, 5... in row 1 that auto increment when you drag right:

In cell A1, type:

=COLUMN()

Or use this anywhere and drag right:

=COLUMN(A1)

Drag to the right → B1 become 2, C1 become 3, and so on. Auto numbering for column!

Example 2 — Count Column in Your Data Table

You have a sales table in A1:J100 (10 column) and you want confirm how many column your table have:

=COLUMNS(A1:J100)

👉 Result: 10 — your table have 10 column. Simple and fast!

Example 3 — Use COLUMN Inside CHOOSE for Dynamic Column Select

You want create a formula that return different column based on which column you are in:

=CHOOSE(COLUMN(A1), "Name", "Age", "Score")

When this formula is in column A, it return "Name". Drag to column B → return "Age". Drag to C → return "Score". Very clever!

💡 Tip: COLUMN and COLUMNS work exactly like ROW and ROWS but for column direction. If you already understand ROW and ROWS, COLUMN and COLUMNS is very easy — same logic, just horizontal!

📚 Useful Resources

• ROW vs ROWS Function in Excel Explained
• TAKE vs FILTER Function in Excel Explained
• VSTACK vs HSTACK Function in Excel Explained
• CONCAT vs TEXTJOIN Function in Excel Explained
• Top Microsoft Excel Tips and Tricks Every Spreadsheet User Should Know
• Free Download PDF - 50 Popular Excel Formulas Cheatsheet (Khmer Language)