Change Column Letter When Formula Copied Down in Single Column

How to change column letters when a formula is copied down?

I think the title makes sense. I am not going to talk about relative and absolute cell references that using the dollar sign here.

We have already discussed that.

Here I am trying to explain how to change the column letter when a formula is copied down/vertically.

In other words, when dragging down the fill handle of a formula cell, the row position/number should be absolute, but the column position/letter should be relative.

What does it mean?

The formula =A1 should be changed to =B1, =C1, =D1, and so on, when copied to down not right.

Usually, what happens is the formula changes to =A2, =A3, =A4, and so on.

That means I want to change column letters when the formula is copied down in a single column.

Problem to Solve in Detail

See the below example.

I have a few country names in row # 1 that are in the range A1:G1.

Change the column letter in the vertical copying of formula

I have a formula (you will get that later) in cell A3 that pointing to cell A1. I’ve copied it to A4, A5, A6, and so on.

Then what happens? It copies the country names in row # 1 down!

Usually, the formula keeps the column heading (column letter) but changes the row number.

For example;

The formula in cell A3 is =A1. Copy this formula to A4. It would be =A2.

But what I want is instead of =A2, I want to get =B1. That’s what I’ve done in the above example.

In other words, I want to change the column letter in the vertical copying of the formula. I want to keep the row number the same but change the column letter.

How to Change Column Letter When Formula Copied Down

Here is that tricky Google Sheets formula that I’ve used in cell A3.

=offset($A$1:1,0,row(A1)-1,1,1)

It’s an Offset formula that dynamically refers to cell A1.

When you copy the above Offset formula down, it refers to B1, C1, D1, and so on, not A2, A3, A4…

In the above formula, the cell reference is A1. If it’s B5, you should change it as below.

=offset($B5:$5,0,row(A1)-1,1,1)
Example to change letter not number in formula in Google Sheets

This formula will return the value from C5, D5, E5, and so on when copied down.

You have got the formula to change column letters only in Google Sheets.

You may be want to know how this Offset based formula works.

If so, you should learn the usage of the functions Offset and Row in Google Sheets.

Feel free to check my useful Google Sheets Functions Guide.

Final Thoughts

I am a great fan of Array Formulas.

But in the above examples, I have not used ArrayFormula instead of a drag-down formula because there is no point in doing so as we have a simple alternative.

Yep, I am talking about TRANSPOSE!

In the last example, the values (texts) that we want to copy down are in the range B5:H5. In an array, you can use this as below.

=transpose(B5:H5)

That’s all.

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.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

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.