# How to add or subtract to, or increment, column letters in Excel?

I'm looking for a simple versatile formula that allows simple mathematics to be done to column positions, returning the appropriate column letter code. For example:

• Given column A, set to +1, it should return B
• Given column E, set to -2, it should return C
• Given column Y, set to +3, it should return AB
• Given column BAA, set to -1, it should return AZZ
• Given column C, set to * 2, it should return F

It should be simple to modify to be either relative to the column it's in, or take a hard-coded column letter, or get a column letter from a formula

Note that the popular formula `=CHAR(CODE(A1)+1)` doesn't work beyond `Z`.

-

Here's the best I've found so far:

=SUBSTITUTE(ADDRESS(1,( COLUMN() + 1 ),4),1,"")

The part in the middle marked in bold is the only part that changes.

In this example, it's taking the current column and adding 1, so returning `B` if it's in column `A` and `AA` if it's in column `Z`.

## Example variants

Example adding to a fixed column (adding 2 to column Z, returns "AB"):

=SUBSTITUTE(ADDRESS(1,( COLUMN(Z1) + 2 ),4),1,"")

Example reading a cell `A1` that contains a letter as text to be treated as a column letter:

=SUBSTITUTE(ADDRESS(1,( COLUMN( INDIRECT(A1&1) ) + 1 ),4),1,"")

Example reading a cell `A1` that contains a number to be treated as a column number (e.g. if it contains 25, it'll return AB):

=SUBSTITUTE(ADDRESS(1,( COLUMN( INDIRECT(A1&1) ) + 1 ),4),1,"")

## Looking up cells with `indirect()`

If your goal is to look up a cell, e.g. using `indirect()`, you can skip the `substitute()` and just pipe in the row number. For example, if you want to look up the cell to the right of the current cell (so increasing the cell letter by one and keeping the row the same):

=INDIRECT( ADDRESS( ROW(), ( COLUMN() + 1 ),4) )

Based partly on this which has a handy formula for transposing to get a column of column letters, incremented each row.

-

The easiest way to do it is with an `INDIRECT` AND `R1C1` reference style

for instance `=INDIRECT("R[1]C[1]",0)` will reference `+1 row` and `+1 column` from the current cell.

`=INDIRECT("RC[3]",0)` will go three columns to the right, where `-3` goes three columns to the left.

You can use some `concatenate` or `&` to reference a cell for starting or increasing if you'd like:

A1 `=INDIRECT("RC["&A2&"]",0)`

A2 = `1`

B1 = `x`

then A1 = `x`

Once you throw in some `cell`, `row`, `column` and `address` it gets more complicated, but the effect is the same.

-