In excel, if I want to perform a function with the column to the left, how do I go about this?

  • What function do you want to perform? Do you want to create a new column of numbers that uses the "column to the left" or are you trying to condense the entire column to a single number (e.g., a sum or average)? We'll need a lot more information to be able to offer a useful answer. – VoteyDisciple Aug 23 '09 at 11:48
  • I just want to be able to select a different column which is independent of the number, so instead of selecting A1, it selects the cell to the left of the one which is running a function. – Tanami Aug 23 '09 at 12:24

Here's how to reference the cell in the column to the left in a worksheet function:


there are probably other ways to do it, but I haven't found a good, terse way to self-reference an Excel cell.

To reference the entire column to the left in a worksheet function:

=INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN()-1,4),"1","") & ":" &  
| improve this answer | |
  • both of those formulas can be shortened to | =ADDRESS(ROW(),COLUMN()-1) | still long but a bit shorter – Xantec Nov 2 '10 at 13:12
  • @Xantec, that just gives you the text address like '$A$1', he's looking for the value in that cell. I'm glad you had me revisit this because I see that the INDEX function isn't needed, so I'll edit my answer. – Lance Roberts Nov 2 '10 at 18:26

If you are in cell H1, and you want to sum column G, type =SUM(G:G).

| improve this answer | |

Since this is a programming question site, I assume you mean in VBA.

The ActiveCell is the currently marked cell (or range), ActiveCell.Column will give you the column number (leftmost column for a range if memory serves me correctly) that you can use.

Simply subtract one and use it (assuming it's not 1 already, of course). If you want the actual column letter (you don't need it for calculations but may want it anyway), use:

Function GetCol (ByVal rng As Range)
    If rng.Column < 27 Then
        GetCol = Chr (64 + rng.Column)
        GetCol = _
            Chr (64 + (rng.Column - 1) \ 26) + _
            Chr (65 + (rng.Column - 1) Mod 26))
    End If
End Function

along with:

Dim colStr as String
colStr = GetCol (ActiveCell)
MsgBox (colStr)

If this isn't a VBA question, it doesn't really belong here (although I see by one of your comments that you're talking about running a function, so I guess you did mean in VBA after all).

| improve this answer | |
  • or, if all you want is the address of the cell to the left then this code could be a lot simpler. | GetCol = cells(0,rng.column-1).address | keep in mind that this will return an error if it is run with a cell in column A – Xantec Nov 2 '10 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.