How to Use the COLUMNS Function in Google Sheets

Published on

The COLUMNS function returns the number of columns in the specified range, making it a useful component in other functions.

You may find this function in many formulas that use 2D arrays to dynamically calculate the column count and return the desired number of columns.

The syntax of the COLUMNS function in Google Sheets is as follows:

COLUMNS(range)
  • range: The array or range whose column count will be returned.

Basic Examples

Before we jump into the examples, it’s important to note that if the number of physical columns is less than the specified range, the formula will return the actual count of physical columns in the range.

For example, the following formulas will return 26 if there are 26 or more columns in your sheet (i.e. if the range extends to or beyond column Z):

=COLUMNS(A1:Z1)
=COLUMNS(A1:Z)

However, if your sheet only contains columns A, B, and C, the formula will return 3, not 26.

Real-Life Uses of the COLUMNS Function

The COLUMNS function in Google Sheets has many practical applications. It helps dynamically select columns in functions such as VLOOKUP, database functions, QUERY, and more. Let’s explore some important use cases.

COLUMNS Function with VLOOKUP

The following VLOOKUP formula searches for “Sophia” in the first column of the range A:G and returns values from columns B to G (2nd to 7th column):

=ArrayFormula(VLOOKUP("Sophia", A:G, {2, 3, 4, 5, 6, 7}, 0))

Instead of manually inputting the index numbers {2, 3, 4, 5, 6, 7}, you can dynamically generate these values using the COLUMNS function with SEQUENCE, as shown below:

=ArrayFormula(VLOOKUP("Sophia", A:G, SEQUENCE(1, COLUMNS(A:G)-1, 2), 0))
Dynamic columns using the COLUMNS function in VLOOKUP in Google Sheets

Where:

  • =COLUMNS(A:G) will return 7, which is the total number of columns in the range.
  • =SEQUENCE(1, COLUMNS(A:G)) will generate the numbers 1 to 7, where 1 is the number of rows and COLUMNS(A:G) is the number of columns.
  • To get the column numbers from 2 to 7, you can use:
=SEQUENCE(1, COLUMNS(A:G)-1, 2)

Here, 1 is the row count, COLUMNS(A:G)-1 calculates the number of columns minus the first one, and 2 sets the starting column for the sequence.

COLUMNS Function with Database Functions

There are several database functions in Google Sheets. For example, consider the DGET function:

=ArrayFormula(DGET(A1:G, {2, 3, 4, 5, 6, 7}, {"Name"; "Sophia"}))

The DGET function looks up the name “Sophia” in the “Name” column and returns values from columns 2 to 7.

You can replace the static index numbers {2, 3, 4, 5, 6, 7} with the COLUMNS and SEQUENCE combination as follows:

=ArrayFormula(DGET(A1:G, SEQUENCE(1, COLUMNS(A:G)-1, 2), {"Name"; "Sophia"}))

In this way, the COLUMNS function helps make your formulas more dynamic and adaptable in various real-life scenarios in Google Sheets.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Hyperlink to Jump to the Last Used Row in Excel

In a vertical range, you can create a hyperlink to jump to the last...

Find the Last Used Row’s Last Value Address in Excel

In a large vertical dataset in Excel, how do you find the cell address...

Find the Last Used Row Number in Excel

When working with large datasets such as sales records, purchase data, or bills of...

More like this

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.