How to Count Number of Columns in Excel: 5 Methods

Method 1 – Using Status Bar to Count Number of Columns in Excel

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. Its important not to select the whole dataset or more than a row here. We have selected the range B4:E4, which contains the headers here.
Observe the status bar. The number of columns available there will show as Count.

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

Method 2 – Counting the Number of Columns in the Range Using the COLUMNS Function

Select cell C12 and insert the following formula.

=COLUMNS(B4:E4)

Use the range B4:E10 (whole dataset) as an argument too and get the same result.

=COLUMNS(B4:E10)

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

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

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.

Go to File > Options. Then go to the Formulas tab, and under the Working with formulas section, check the R1C1 reference style.

Click OK; you’ll have numbers in the column index and see the spreadsheet has 16384.

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

For an entire row, the formula should be like this.

=COLUMNS(1:1)

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

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

The formula should look like this.

=COUNTA(B4:E4)

The result is the same as the previous methods. The selection should be similar to the first method.

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

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

Click the Insert tab in the VBA window and select Module from the drop-down menu.

In the created module, insert the following code.

Function NumberofColumns(rnge As Range) As Integer
    NumberofColumns = rnge.Columns.Count
End Function

Close the VBA window and insert the following formula in the spreadsheet.

=NumberofColumns(B4:E4)

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)

You 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)

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

How to Count Number of Rows in Excel

Use the following formula.

=ROWS(B4:E10)

The function can be used to find out the total number of rows available in the spreadsheet too.

=ROWS(A:A)

How to Get Column Number of Selected Cells in Excel

Use the following formula.

=COLUMN(C5)

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)

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.

Count Number of Columns.xlsm

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

Get FREE Advanced Excel Exercises with Solutions!

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

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

2 Comments

Nigel Jun 20, 2023 at 11:05 AM

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
  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