Our data files often fill with columns upon columns, rows upon rows. It is hard to keep track of how many columns and rows you already have in your table unless you want to count it yourself and even so the result might be wrong. Excel provides a simple way, allowing us to count table columns quickly and the formula itself is actually very basic and easy to use.
Count Table Columns in Excel
The formula to count table columns in Excel is:
The table is the name of the table that you want to count the columns
Explanation of the formula
By default, every time you create a table in Excel, it will be named Table1, the next one will be Table2 and so on.
This syntax is based on structure referencing. Table parts can be referred to by the table name. Excel returns a reference to the data in the table region only when a table is referred to by its name. In Figure 1, the whole table range is B4:H14; hence, Table1 returns the range from B5 to H14 to the COLUMNS function.
Example of counting table columns
Figure 1. Example of how to use the Columns function
In the case of the photo above, in order to count the number of columns, simply using the function as below:
And it returns the number of columns in Table1 which is 7.
- Keep in mind that if you have more than 1 table in your excel sheet, check the name of the table you want to count the columns before put the name in the function.
- The advantage of using the table name over the table range is that every time you insert or delete a column, the return value gets updated.
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.