COLUMNS Formula in Excel

COLUMNS Formula in Excel (Table of Contents)

• Introduction to COLUMNS Formula
• How to Use COLUMNS Formula in Excel?

Introduction to COLUMNS Formula

COLUMNS formula is used to find out the number of columns available in the given array or the reference in the input array. It is an in-built function which comes under Lookup and Reference function. It is useful in looking for the number of columns in the array.

As an example, let’s assume there is an array “B2:F7” then in the COLUMNS function (=COLUMNS (B2:F7)) will return 5. It means there are 5 columns existing in the range of B2 to F7.

Syntax 

COLUMNS () – It will return the total number of columns available in the given input. There is only one argument – array.

The Argument in the COLUMNS Function:

• array: It’s is a mandatory parameter, for which the user wants to count the number of columns existence in the cell range.

How to Use COLUMNS Formula in Excel?

COLUMNS Formula in Excel is very simple and easy. Let’s understand how to use the COLUMNS Formula in Excel with some examples.

Example #1- How to use COLUMNS Function in Excel

There is some data given in a table in the Sheet1, so a user wants to count how many numbers of columns are there in the table.

Let’s see how the COLUMNS Function can solve this problem.

Open MS Excel, Go to Sheet1 where the user wants to find out the numbers of columns in the table.

Create one header for the COLUMNS results to show the function result in column D.

       

Click on cell D2 and apply COLUMNS Formula.

Now it will ask for array which is the range of the table means total cells.

Press the Enter Key.

Now merge cells from D2 to D10.

Summary of Example 1:

As the user wants to find out the numbers of columns in the table. The function result is available in column D which is coming as 3 which means there are total 3 number columns in the range A1 to C10.

Example #2- Different Types of Array and References

There is some data range given in a table in Sheet2, so a user wants to count how many numbers of columns are there in the data range.

Let’s see how the COLUMNS Function can solve this problem.

Open MS Excel, Go to Sheet2 where the user wants to find out the numbers of columns in the range.

Create one header for the COLUMNS results to show the function result in column C.

Click on cell C2 and apply COLUMNS Formula.

The result is shown below after using the above formula.

Use the Columns Formula in the next cell.

The result is shown below after using the above formula.

Use the Columns Formula in the next cell.

The result is shown below after using this formula.

Use the Columns Formula in the next cell.

The result is shown below after using the above formula.

Use the Columns Formula in the next cell.

The result is shown below after using this formula.

Use the Columns Formula in the next cell.

The result is shown below after using columns formula.

Use the Columns Formula in the next cell.

The result is shown below after using columns formula.

Summary of Example 2:

As the user wants to find out the number of columns in the table. The function result is available in column C which is coming for each data.

Example #3- Find the Total Cells in the Array or References

There is some data given in a table in the Sheet3, which have Emp ID, Name, and city. So, a user wants to count how many numbers of cells are there in the table.

Let’s see how the COLUMNS Function can solve this problem with the rows function.

Open MS Excel, Go to Sheet3 where the user wants to find out the total cells in the table.

Create three headers for the COLUMNS result, rows result and to show the total cells to show the function result in column D.

Click on cell E2 and apply COLUMNS Formula.

Now it will ask for array which is the range of the table means the total cells, select cells A1 to C10.

Press the Enter Key.

 Click on cell E3 and apply ROWS Function to count the total number of rows in the table.

Now it will ask for array which is the range of the table means the total cells in the table, select cells A1 to C10.

Press the Enter Key.

Now just multiply the cells data of the total number of columns and the total number of rows in the E4 to find out the total number of cells in the table.

 ‘OR’

Calculate the total number of columns and the total number of rows in cell E4 and multiply there only.

Press the Enter Key.

Summary of Example 3:

As the user wants to find out the total number of cells in the table. The function result is available in cell F4 which is coming 30 after calculation.

Things to Remember

• The COLUMNS Function will return the number of columns available in the given array or the reference in the input array.
• It can be used in the formula when a user wants to find out the total number of columns available in the array.
• The array argument is allowed even for a single cell or as provided by a reference in the array which has a single cell. But it will not support multiple cells or reference in the columns function, a user can pass a single range at a time.

Recommended Articles

This has been a guide to COLUMNS Formula in Excel. Here we discuss How to use COLUMNS Formula in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. SUBTOTAL Formula in Excel
2. Creating Lookup Table in Excel
3. What is Chart Wizard in Excel?
4. Time Difference in Excel