How to use COLUMN and COLUMNS functions in Excel?

COLUMN and COLUMNS functions in Excel are indispensable tools for data analysis and manipulation. These functions provide users with valuable insights into the structure and organization of their spreadsheets, enabling efficient navigation and referencing. By mastering COLUMN and COLUMNS functions in Excel, users can streamline their workflows, automate repetitive tasks, and unlock the full potential of their data. Whether it’s identifying the column number of a specific cell or calculating the total number of columns in a range, these functions offer unparalleled flexibility and versatility. Empower yourself with the capabilities of COLUMN and COLUMNS functions in Excel, and elevate your productivity to new heights.

This Content Covers:

1. What is the COLUMN Function in Excel?
2. Syntax, Purpose, and Arguments of COLUMN Function
3. Different uses of COLUMN Function.
   • Getting Column Number from a Reference.
   • COLUMN Function with MOD Function to Generate a Fixed Value.
   • COLUMN Function to Get the Sum of Every Nth Column.
4. What is the COLUMNS Function in Excel?
5. Syntax, Purpose, and Arguments of COLUMNS Function
6. Different uses of COLUMNS Function.
   • Getting Total Number of Columns and Cells
   • Getting First and Last Cell Address
   • Combined Formula of COLUMNS Function and VLOOKUP Function.

1. What is the COLUMN Function in Excel?

The COLUMN function in Microsoft Excel is a look-up or reference function that is used for looking up and providing the column number of a given cell reference.

2. Syntax, Purpose, and Arguments of COLUMN Function

Syntax: =COLUMN([reference])

Purpose: The purpose of this function is to get the column number of a reference.

Arguments: [reference]- a reference to a cell or range of cells.

• Reference can refer to either a specific cell address or a group of cells.
• Multiple addresses can’t be used as reference for this function.
• If the reference is not provided, it defaults to the cell that has the column function.

3. Different uses of COLUMN Function.

3.1 Getting Column Number from a Reference.

We will use the below given data to see how we can get column number from a reference using the COLUMN function in Excel.

Step 1: Select B2 and insert the COLUMN formula in it and press Enter key. The result is 4 because the input we gave inside the formula is D5 and column D is situated at the 4^th position.

 

Step 2: If we don’t provide a reference inside the COLUMN function then it will return the column number of the cell in which the formula is typed into. We inserted the formula in B3 without any reference, so it returned 2 as column B is at the 2^nd position.

Step 3: If we provide a range instead of a cell address then the COLUMN function will return the column numbers for the whole range. Now if you want to get only the column number of the first cell from a given range then follow the next step.

Step 4: Simply just put an “@” after the equal sign.

3.2 COLUMN Function with MOD Function to Generate a Fixed Value.

Suppose we have a business, and we have to pay the expenses of this business every 4^th month. We can use the MOD function with the COLUMN function to set a fixed value for every 4^th month.

Step 1: Select cell B10 and insert the following formula inside that cell. Press Enter key.

=IF(MOD(COLUMN(B10)-1,4)=0,$B$5,0)

Step 2: Drag the fill-handle of B10 towards its right to M10 and get the complete result.

Also Read: How to use Excel INDIRECT Function and Excel OFFSET Function?

3.3 COLUMN Function to Get the Sum of Every N^th Column.

In the data table below we have some random digits and numbers. In column H we have the column numbers and column I will display the result. Now we will utilize a formula using the SUMPRODUCT, MOD, and COLUMN functions to sum up N^th columns.

Step 1: Select cell I3 and insert the following formula in this cell. Press Enter key.

=SUMPRODUCT(–(MOD(COLUMN(B3:F3)-COLUMN(B3)+1,H3)=0),B3:F3)

Step 2: Drag the fill-handle of I3 down to I7 to apply the formula in those cells too. Now look at the results, what this formula did was, it took numbers from the N^th numbered columns and added them together. In cell H3 the N^th column was 2 or the second column. So, inside the table for range “B3:F3” the second columns are column C and E. Therefore, the formula in I3 added the numbers of C3 and E3 which resulted in 65. For cell I4 the range was B4:F4 and the N^th number was 3 inside the formula. Now there is only one 3^rd positioned column which is D because there are only five columns in total in this table. So, the formula returned the number of D4 which is 92. The other cells of column I which had this combined formula inserted in them worked the same way.

4. What is the COLUMNS Function in Excel?

The COLUMNS function in Microsoft Excel is a built-in function which is used to calculate the total number of columns in the given array or collection of references.

Also Read: How to use XLOOKUP in Excel?

5. Syntax, Purpose, and Arguments of COLUMNS Function

Syntax: =COLUMNS(array)

Purpose: The purpose of this function is to get the number of columns in an array or reference.

Arguments: [array]- A reference to a range of cells.

6. Different uses of COLUMNS Function.

6.1 Getting Total Number of Columns and Cells

Step 1: Click on cell F3 and enter the following formula.

=COLUMNS(B3:D3)

Step 2: Press Enter key to get the total number of columns.

Step 3: Insert the below given formula in Cell F7 to get the total number of cells from this range and press Enter key.

=COLUMNS(B3:D3)*ROWS(B3:D7)

6.2 Getting First and Last Cell Address

Step 1: Insert the below given formula in Cell F3 to get the first cell address from this range and press Enter key.

=ADDRESS(@ROW(B3:D7),@COLUMN(B3:D7))

Step 2: Click on cell F7 and enter the following formula then press Enter to get the last cell address from this range.

=ADDRESS(@ROW(B3:D7)+ROWS(B3:D7)-1,@COLUMN(B3:D7)+COLUMNS(B3:D7)-1)

6.3 Combined Formula of COLUMNS Function and VLOOKUP Function.

Suppose we want to know the shop address of the worker named “Mariam” and also, we want to know the amount she sold. We can combine COLUMNS function and VLOOKUP function to create a combined formula to get these data.

Step 1: Insert the following formula in F3 and press Enter key to get result.

=VLOOKUP(“Mariam”,$B$3:$C$7,COLUMNS($B$3:$C$7),0)

Step 2: Similarly use this formula in F7 to get the amount Mariam sold.

=VLOOKUP(“Mariam”,$B$3:$D$7,COLUMNS($B$3:$D$7),0)

Application of COLUMN and COLUMNS functions in Excel

• Column Identification: Use the COLUMN function to quickly determine the column number of a specified cell, aiding in precise referencing and formula creation.
• Dynamic Formulas: Incorporate the COLUMN function within formulas to create dynamic calculations based on the position of data within a spreadsheet, enhancing flexibility and efficiency.
• Conditional Formatting: Utilize the COLUMN function in conditional formatting rules to apply formatting styles based on the column number, allowing for targeted visual enhancements.
• Data Analysis: Employ the COLUMNS function to count the number of columns in a given range, facilitating comprehensive data analysis and manipulation tasks.
• Array Formulas: Combine the COLUMNS function with array formulas to perform complex calculations across multiple columns, enabling advanced data processing capabilities.
• Data Validation: Leverage the COLUMN function within data validation rules to restrict input based on specific column numbers, ensuring data integrity and accuracy in Excel spreadsheets.

 

For ready-to-use Dashboard Templates:

1. Financial Dashboards
2. Sales Dashboards
3. HR Dashboards
4. Data Visualization Charts