Convert alphabetical Google Sheet column names to numbers

Lately, I’ve been working with large Google spreadsheets that have many columns. When you write QUERY statements to pull data from one sheet into another, you have to use Col1, Col2, Col3 column names instead of A, B, and C. It’s hard enough to remember the letter O is the 15th letter of the alphabet. There is no chance that I am going to memorize that column BH is the 60th. I made this calculator to solve this conversion:

One of my favorite tricks when a team member needs some data is to create a new Sheet, query data from the primary sheet, and share that subset of the data while protecting the A1 cell so the formula doesn’t get mangled. This method lets our friend get only the columns she needs and live updates in the form of new rows just like the huge primary sheet. It works by passing an IMPORTRANGE call into the first parameter of QUERY.

Categorized as Tools

Leave a comment

Your email address will not be published. Required fields are marked *