How to Count Number of Columns in Excel?

Get FREE Advanced Excel Exercises with Solutions!

We may need to count the number of columns in a selection in Excel for a range of purposes including data validation, manipulation, management, organization, etc. It is helpful while creating charts and visualization purposes too. It gives a general idea about a dataset and the number of particulars involved. In this tutorial, we will focus on how to count the number of columns in Excel using different techniques.

how to count number of columns in excel

Excel offers some easy ways to count the number of columns, both in a range and throughout the whole worksheet. We will go through them one by one in their own sub-sections. For the demonstration, we are using the following dataset containing six people’s sales data of an organization.

dataset to count columns

We will be using Excel 365 for all the demonstrations.


1. Using Status Bar to Count Number of Columns in Excel

The status bar is the horizontal bar displaying information at the bottom of the Excel spreadsheet. It contains and provides helpful information, and can give quick access to various useful settings and features. We can use this feature to count the number of columns in Excel quite easily.

  • Select the headers of the chart or dataset (or a particular row, which doesn’t have any empty cells) that you want to measure the number of columns. This is important to not select the whole dataset or more than a row here. We have selected the range B4:E4 which contains the headers here.
  • Now, observe the status bar. The number of columns available there will show as Count in it.

counting columns from status bar

Note: The Count in the status bar only shows the non-empty cells in a selection.


2. Counting the Number of Columns in the Range Using the COLUMNS Function

Excel provides the COLUMNS function to achieve the same result too. Although the previous method is the quickest, you can select any number of rows using the function.

The COLUMNS function takes an array as the argument and returns the total number of columns used there.

  • To count the number of columns in Excel with the function, select cell C12 and insert the following formula.
=COLUMNS(B4:E4)

using columns function to count columns

  • You can use the range B4:E10 (whole dataset) as an argument too and get the same result.
=COLUMNS(B4:E10)

how to count number of columns in excel


3. Counting the Total Number of Columns Available on the Entire Sheet from the Column Index

You can count the total number of columns available in the entire worksheet in Excel too. You can easily count them from the column index. For this to work, the select cell’s row should be empty.

  • Select the very first cell (cell A1) on the spreadsheet.

selecting the first blank cell

  • Then press Ctrl+Right Arrow on your keyboard. The index number showing on the top of the last one is the total columns available in the spreadsheet.

total number of columns in alphabetical index

  • If you want to see it in numbers, go to File > Options. Then go to the Formulas tab and under the Working with formulas section, check the R1C1 reference style.

enabling r1c1 reference style

  • After clicking on OK, you will have numbers in the column index and see the spreadsheet has 16384.

total number of columns in numeric index

Note: If you are selecting a cell that does not belong to an empty row, select a cell that doesn’t contain any values on the right of it and press Ctrl+ Right Arrow.

Count the Total Number of Columns Available in the Entire Sheet Using the COLUMNS Function

You can use the COLUMNS function to count the total number of columns available in Excel too. As mentioned earlier, the function takes an array and gives the total number of columns available in that array. So if we pass up an entire row as the array argument, it will return the total column number of the sheet.

  • For an entire row, the formula should be like this.
=COLUMNS(1:1)

total columns in sheet using columns function

As we can see, we can get the same value 16384, which indicates the total number of columns available in the sheet.


4. Using the COUNTA Function to Count the Number of Columns with Entries

The COUNTA function provides users with the non-blank values it takes as the argument. Keep in mind, it returns the total number of cells in the selection that are not empty. We can utilize this feature to pass only the column headers or a row without any empty cells as the argument and get our desired result.

  • The formula should look like this.
=COUNTA(B4:E4)

counting number of columns using counta function

As we can see the result is the same as the previous methods. However, the selection should be similar to the first method.


5. Using VBA to Count the Number of Columns of the Selected Range in Excel

To count the number of columns in Excel using VBA, we have to create a custom function.

However, you need to have the Developer tab on your ribbon first. If you don’t have one, click here to find out how to display the Developer tab on your ribbon.

  • Go to the Developer tab and click on Visual Basic from the Code group of the ribbon.

selecting visual basic from ribbon

  • Now click on the Insert tab in the VBA window and select Module from the drop-down menu.

inserting module in vba

  • In the newly created module, insert the following code.
Function NumberofColumns(rnge As Range) As Integer
    NumberofColumns = rnge.Columns.Count
End Function

vba code for counting column numbers

  • Now close the VBA window and insert the following formula in the spreadsheet.
=NumberofColumns(B4:E4)

counting number of columns using udf for headers

  • We can use more than the headers as the argument for this UDF, such as the range B4:E10. You can use the following formula and get the same result.
=NumberofColumns(B4:E10)

counting number of columns using udf for other ranges

  • Additionally, we can use this function to determine the total number of columns available in the sheet too. Modify the formula in this way.
=NumberofColumns(1:1)

couting number of columns using udf for entire sheet

This is how we can use VBA to count the number of columns in Excel.


How to Count Number of Rows in Excel

We can count the number of rows in Excel in the same way as when we count the columns. Selecting the row headers or a column of a dataset that does not have any empty cells within it will show the row number as the count in the status bar. However, we need the ROWS function to count the number of rows in Excel, instead of the COLUMNS function.

  • To count the number of rows in the selection, use the formula like this.
=ROWS(B4:E10)

counting number of rows

  • The function can be used to find out the total number of rows available in the spreadsheet too.
=ROWS(A:A)

counting rows for entire sheet


How to Get Column Number of Selected Cells in Excel

Besides the COLUMNS function, Excel also offers the COLUMN function. The difference is the COLUMN function returns the count of the column of the reference or array in the spreadsheet. Just for the purpose of finding out the column number, it may not be effective as we can see easily from the column index. However, there are many important uses of the function. That includes constructing it with other functions, uses in conditional formatting, and many different array formulas.

  • The formula can be used like this.
=COLUMN(C5)

column function usage

  • We can use it for a range too, in those cases it will return an array. For example, we have used the following formula in cell D13.
=COLUMN(C5:E6)

column function for a range

These values do not have any direct practical uses but are used as references in other formulas.


Things to Remember

  • All methods of counting ranges can be applied to tables too.
  • Make sure to select only the headers if you are applying the status bar method or the COUNTA.
  • If you are not selecting the header, make sure all the cells in the row are non-empty.
  • Use the COLUMNS function to get the number of columns and the COLUMN function to get the column index of the selection.

Download Practice Workbook

You can download the workbook used for the demonstration from the link below.


Conclusion

That concludes our article on how to count the number of columns in Excel which includes the status bar, functions, and VBA. Usually, the methods are pretty straightforward. But I hope you found what you were looking for and can count the number of columns for both the selection and spreadsheet in Excel comfortably now. Hopefully, you have found the article helpful and effective. If you have any questions or suggestions, let us know in the comments below.


Frequently Asked Questions (FAQs)

  • How to Insert Extra Rows and Columns in Excel?

To insert an extra row or columns in Excel, right-click on the column or row index and select Insert from the context menu. If you do so from the column index, Excel will insert an extra column. Otherwise, you will get an extra row in between. Be sure to right-click on the column/row index before which you want the extra column/row to insert.

  • Is there a formula or function to automatically count the number of columns in a table?

The COLUMNS function described in method 2 can be used for tables too. For tables, the argument selected manually will automatically be converted into a dynamic range and it will count the number of columns on its own if there are any changes.

  • How do I count the number of columns with data in a specific row?

You can use the COUNTA function to find out the number of non-empty cells in any range. You can use this to count the number of columns with data in a specific row. For example, let’s say for the third row, using =COUNTA(3:3) will give the number of cells with values in the row.


Excel Count Columns: Knowledge Hub


<< Go Back to Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Abrar-ur-Rahman Niloy, holding a B.Sc. in Naval Architecture and Marine Engineering, has contributed to Exceldemy for nearly 1.5 years. As a leader in Excel, VBA, and Content Development teams, he authored 114+ articles and assisted the Exceldemy forum. Presently, as a project writer, he prioritizes stepping out of his comfort zone, aiming for constant technical improvement. Niloy's interests encompass Excel & VBA, Pivot Table, Power Query, Python, Data Analysis, and Machine Learning libraries, showcasing his commitment to diverse... Read Full Bio

2 Comments
  1. I have a question that I cannot get my head around.
    If I have an array of numbers (7 columns x 10 rows) and each cell contains a random number between 1-20 eg:

    1,3,4,7,12,14,17
    1,2,3,4,10,14,19
    2,3,5,7,10,14,17

    etc

    How would I calculate the frequency of 3 & 4 & 5 number combinations that appear in the array?
    For example
    how many times does 1,3,4,14,17 occur or
    how many times does 3,4,14,17 occur or
    how many times does 3,7, 14 occur?

    Any guidance would be appreciated.
    Thanks
    Nigel

    • Reply Avatar photo
      Shahriar Abrar Rafid Jun 20, 2023 at 4:22 PM

      Hello NIGEL,
      We like to solve this kind of problem and it makes us so happy if it solves your problem.
      You can use the following VBA code.

      Sub Calc_Freq()
      
      Count = 0
      
      Set Rng = Application.InputBox("Select the range", "ExcelDemy", Type:=8)
      
      If Rng Is Nothing Then
          MsgBox "No range selected."
          Exit Sub
      End If
      
      A = InputBox("Input the numbers without any space or comma", "ExcelDemy")
          
      For i = 1 To Rng.Rows.Count
          S = ""
          For j = 1 To Rng.Columns.Count
              S = S & Rng.Cells(i, j)
          Next j
          If InStr(1, S, A) > 0 Then
              Count = Count + 1
              Rng.Rows(i).Interior.Color = vbRed
          End If
      Next i
      
      MsgBox Count
      
      End Sub

      After running the macro, it will pop up a message box asking you to enter the whole range of data. Then you will be asked to enter the number maintaining the sequence. And make sure to enter the numbers without any spaces or commas.
      As a result, you will get the calculated frequency in a message box and the row containing the combination will get highlighted.
      But, the above code only works for consecutive matches. For example, if you search for 3,4,5 and the values in a row are 3,7,6,4,1,5 then it cannot count this. So, I’ve modified the code to make it usable with all kind of conditions. The following code can calculate the frequency of combination under any criteria.

      Sub Calc_Freq()
      
      Set Rng = Application.InputBox("Select the entire range of data", "ExcelDemy", Type:=8)
      
      Found = InputBox("Enter the Numbers (Separted by Commas): ", "ExcelDemy")
      
      Found = Split(Found, ",")
      
      Dim Arr() As Variant
      ReDim Arr(1 To Rng.Rows.Count, 1 To UBound(Found) + 1)
      
      For i = 1 To Rng.Rows.Count
          S = ""
          For j = 1 To Rng.Columns.Count - 1
              S = S & Rng.Cells(i, j) & ","
          Next j
          S = S & Rng.Cells(i, Rng.Columns.Count)
          S = Split(S, ",")
          Start = LBound(S)
          For j = LBound(Found) To UBound(Found)
              Arr(i, j + 1) = 0
              For m = Start To UBound(S)
                  If S(m) = Found(j) Then
                      Arr(i, j + 1) = m + 1
                      Start = m + 1
                      Exit For
                  End If
              Next m
          Next j
      
          Matched = True
          For j = LBound(Arr, 2) + 1 To UBound(Arr, 2)
              If Arr(i, j) <= Arr(i, j - 1) Then
                  Matched = False
                  Exit For
              End If
          Next j
          For j = LBound(Arr, 2) To UBound(Arr, 2)
              If Arr(i, j) = 0 Then
                  Matched = False
                  Exit For
              End If
          Next j
      
          If Matched = True Then
              For j = LBound(Arr, 2) To UBound(Arr, 2)
                  Rng.Cells(i, Arr(i, j)).Interior.Color = vbRed
              Next j
              Count = Count + 1
          End If
      
      Next i
      
      MsgBox Count
      
      End Sub

      This code is the ultimate solution for your problem.
      Is this the solution you wanted? If you need something else, put the query in the reply.

      Regards,
      SHAHRIAR ABRAR RAFID
      Team ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo