EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Tips COLUMNS Formula in Excel
 

COLUMNS Formula in Excel

Arun Gupta
Article byArun Gupta
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated June 6, 2023

columns formula in excel

 

 

Introduction to COLUMNS Formula

The COLUMNS Formula in Excel determines the number of columns available in the given array or the reference in the input array. It is an in-built function that comes under the Lookup and Reference function. It helps look for the number of columns in the array.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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 5 columns exist in the range of B2 to F7.

Syntax 

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

COLUMNS formula syntax

How to Use COLUMNS Formula in Excel? COLUMNS Formula in Excel is very simple and easy. The Argument in the COLUMNS Function:

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

Let’s understand how to use the COLUMNS Formula in Excel with some examples.

You can download this COLUMNS -Formula -Excel- Template here – COLUMNS -Formula -Excel- Template

Example #1- How to use the COLUMNS Function in Excel

Some data is given in a table in Sheet 1, so a user wants to count the number of columns 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.

COLUMNS formula example 1-1

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

COLUMNS formula example 1-2

Click on cell D2 and apply the COLUMNS Formula.

COLUMNS formula example 1-3

Now it will ask for an array in which the table’s range means total cells.

COLUMNS formula example 1-4

Press the Enter Key.

COLUMNS formula example 1-5

Now merge cells from D2 to D10.

COLUMNS formula example 1-6

Summary of Example 1:

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

Example #2- Different Types of Arrays and References

If a user wants to count the number of columns in a given data range within a table on Sheet2.

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 number of columns in the range.

COLUMNS formula example 2-1

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

COLUMNS formula example 2-2

Click on cell C2 and apply the COLUMNS Formula.

COLUMNS formula example 2-3

The result is shown below after using the above Formula.

COLUMNS formula example 2-4

Use the Columns Formula in the next cell.

COLUMNS formula example 2-5

The result is shown below after using the above Formula.

COLUMNS formula example 2-6

Use the Columns Formula in the next cell.

COLUMNS formula example 2-7

The result is shown below after using this Formula.

COLUMNS formula example 2-8

Use the Columns Formula in the next cell.

COLUMNS formula example 2-9

The result is shown below after using the above Formula.

COLUMNS formula example 2-10

Use the Columns Formula in the next cell.

COLUMNS formula example 2-11

The result is shown below after using this Formula.

COLUMNS formula example 2-12

Use the Columns Formula in the next cell.

COLUMNS formula example 2-13

The result is shown below after using the columns formula.

COLUMNS formula example 2-14

Use the Columns Formula in the next cell.

COLUMNS formula example 2-15

The result is shown below after using the columns formula.

COLUMNS formula example 2-17

Summary of Example 2:

The user wants to determine 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

Sheet 3 contains a table with data, including Employee ID, Name, and City. So, a user wants to count the number of cells in the table.

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

Open MS Excel; go to Sheet 3, where the user wants to find the table’s total cells.

total cells in the array -1

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

total cells in the array -2

Click on cell E2 and apply the COLUMNS Formula.

total cells in the array -3

It will then ask for an array in which the table’s range means the total cells, select cells A1 to C10.

total cells in the array -4

Press the Enter Key.

total cells in the array -5

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

total cells in the array -6

It will then ask for an array in which the table’s range means the total cells in the table, select cells A1 to C10.

total cells in the array -7

Press the Enter Key.

total cells in the array -8

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.

total cells in the array -9

‘OR’

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

total cells in the array -10

Press the Enter Key.

total cells in the array -11

Summary of Example 3:

The user wants to find out the total number of cells in the table. The function result is available in cell F4, which comes 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.
  • When a user wants to determine the total number of columns present in an array, the array argument can be utilized within the Formula.
  • The array argument permits the inclusion of a single cell or a reference to a single cell within the array. But it will not support multiple cells or references in the columns function; a user can pass a single range at a time.

Recommended Articles

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

  1. Excel COLUMNS Function
  2. Excel COLUMN to Number
  3. Column Header in Excel
  4. Freeze Columns in Excel

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download COLUMNS -Formula -Excel- Template

EDUCBA Login

Forgot Password?

EDUCBA

Download COLUMNS -Formula -Excel- Template

EDUCBA

डाउनलोड COLUMNS -Formula -Excel- Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW