Search code examples
excelworksheet-function

Excel Reference To Current Cell

How do I obtain a reference to the current cell?

For example, if I want to display the width of column A, I could use the following:

=CELL("width", A2)

However, I want the formula to be something like this:

=CELL("width", THIS_CELL)
Solution

  • Create a named formula called THIS_CELL

    1. In the current worksheet, select cell A1 (this is important!)

    2. Open Name Manager (Ctl+F3)

    3. Click New...

    4. Enter "THIS_CELL" (or just "THIS", which is my preference) into Name:

    5. Enter the following formula into Refers to:

      =!A1

      NOTE: Be sure cell A1 is selected. This formula is relative to the ActiveCell.

    6. Under Scope: select Workbook.

    7. Click OK and close the Name Manager

    Use the formula in the worksheet exactly as you wanted

    =CELL("width",THIS_CELL)

    EDIT: Better solution than using INDIRECT()

    It's worth noting that the solution I've given should be preferred over any solution using the INDIRECT() function for two reasons:

    1. It is nonvolatile, while INDIRECT() is a volatile Excel function, and as a result will dramatically slow down workbook calculation when it is used a lot.
    2. It is much simpler, and does not require converting an address (in the form of ROW() COLUMN()) to a range reference to an address and back to a range reference again.

    EDIT: Also see this question for more information on workbook-scoped, sheet dependent named ranges.

    EDIT: Also see @imix's answer below for a variation on this idea (using RC style references). In that case, you could use =!RC for the THIS_CELL named range formula, or just use RC directly.