Excel has several built-in features that help analyze data. For example, you can count the number of columns in your data model by selecting those columns, and you will see the count on the bottom right corner. However, this is a repetitive task, one that can be handled with a simple formula. In this article we're going to show you how to get the number of columns using the COLUMNS function.
Syntax
=COLUMNS(cell or range reference you want to get its column count)
Steps
- Begin by typing in =COLUMNS(
- Select or type in the range that contains the reference (i.e. B2:G7)
- Type in ) and press the Enter key to complete the formula
How
The COLUMNS function returns the column count of the target reference or array. The function only needs one input parameter. In our example, we used,
=COLUMNS(B2:G7)
=COLUMNS({75,95,47,5,9,85;24,49,79,96,95,78;47,99,94,86,28,31;52,55,61,65,94,50;36,29,4,72,17,49;15,43,10,80,83,97})
The COLUMNS function is helpful to provide information about dynamic ranges whose dimensions can vary when updated. Also, incremental number series can be calculated by using an expanding range in the COLUMNS function. Use the $A$1:A1 range reference, and copy it down to generate a number sequence. For example,
=COLUMNS($A$1:A1)
Also see our guide on how to remove blank cells and get row count guides to further automate your data tables.
