82

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)
Steven
  • 13,501
  • 27
  • 102
  • 146

13 Answers13

56

Several years too late:

Just for completeness I want to give yet another answer:

First, go to Excel-Options -> Formulas and enable R1C1 references. Then use

  =CELL("width", RC)

RC always refers the current Row, current Column, i.e. "this cell".

Rick Teachey's solution is basically a tweak to make the same possible in A1 reference style (see also GSerg's comment to Joey's answer and note his comment to Patrick McDonald's answer).

Cheers
:-)

Community
  • 1
  • 1
imix
  • 1,231
  • 11
  • 13
  • 5
    Btw. the width actually only depends on the current column and can also be asked for with `=CELL("width", C)` where `C` is basically the "current column" (as `R` is the "current row"). – imix Aug 13 '14 at 16:48
  • 1
    This is great! Love it. You, my friend, get a bounty. – Rick Aug 13 '14 at 18:32
  • 3
    Wow, many thanks, Rick! Now I can write comments everywhere and I can edit Community Wikis. That's really great and a lot more freedom for me on SO. I will use the Force wisely 8-) – imix Aug 14 '14 at 20:08
  • I wasn't sure how portable this would be since you need to change a global setting. But it turns out the R1C1 option is also saved with the workbook, so it still works if someone else with default settings opens your file. Note if they switch the option off, Excel will convert all those RC's to normal references. At least that's what I inferred from a couple quick tests. Would appreciate if someone can corroborate this behavior. – rkagerer Oct 26 '18 at 04:47
  • @rkagerer: Note GSergs comment to Joeys answer. Internally, Excel works always with R1C1 style references so the option only allows the user to see and enter them also. It will still work after switching the option back to A1 reference style, just that you can't enter "RC" anymore and instead of RC the formulas show the (always changing) names of the cells they are in instead of the "constant" RC. – imix Nov 01 '18 at 20:57
53

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.

Community
  • 1
  • 1
Rick
  • 43,029
  • 15
  • 76
  • 119
  • 1
    Great trick but I couldn't get it to work in Conditional Formulas (tested in Excel 2003 and 2016). The answer from @imix works. – rkagerer Oct 26 '18 at 04:17
  • @RickTeachy Yes, I mean inside formulas for conditional formatting. – rkagerer Oct 27 '18 at 05:03
  • @rkagerer iirc it works if the name is scoped to the sheet instead of the workbook. This is a bummer since it means you have to duplicate names across multiple sheets sometimes, but it does work. Will try to remember to confirm later. – Rick Oct 28 '18 at 04:17
  • 1
    @RicksupportsMonica re your assertion that `CELL` is nott volatile, it actuall _is_ [See this](https://www.decisionmodels.com/calcsecretsi.htm) – chris neilsen May 08 '22 at 05:34
  • @chrisneilsen Thank you for pointing this out! The CELL() formula was just an example of how to use this feature... the THIS_CELL feature itself I describe is not volatile. I should find a different nonvolatile example though. Any suggestion? – Rick May 08 '22 at 14:56
40

You could use

=CELL("width", INDIRECT(ADDRESS(ROW(), COLUMN())))
Lance Roberts
  • 22,383
  • 32
  • 112
  • 130
Patrick McDonald
  • 64,141
  • 14
  • 108
  • 120
32

=ADDRESS(ROW(),COLUMN(),4) will give us the relative address of the current cell. =INDIRECT(ADDRESS(ROW(),COLUMN()-1,4)) will give us the contents of the cell left of the current cell =INDIRECT(ADDRESS(ROW()-1,COLUMN(),4)) will give us the contents of the cell above the current cell (great for calculating running totals)

Using CELL() function returns information about the last cell that was changed. So, if we enter a new row or column the CELL() reference will be affected and will not be the current cell's any longer.

Code Lღver
  • 15,573
  • 16
  • 56
  • 75
andy
  • 329
  • 3
  • 2
  • Thanks for this. In twenty years I've never knowingly used ADDRESS but when you have six columns, each with 500 rows that need a range-based conditional format replaced, it does speed things up somewhat, compared to having to redo every single one. – Rich Harding Apr 02 '15 at 16:13
8

A2 is already a relative reference and will change when you move the cell or copy the formula.

Joey
  • 344,408
  • 85
  • 689
  • 683
  • 1
    Don't worry about addressing style. All formulas use R1C1 internally anyway, it doesn't matter. – GSerg Apr 16 '09 at 18:46
  • Ah, thanks. Didn't bother to look it up right now (wading through the XML isn't exactly fun usually :)) – Joey Apr 16 '09 at 18:47
7
=ADDRESS(ROW(),COLUMN())
=ADDRESS(ROW(),COLUMN(),1)
=ADDRESS(ROW(),COLUMN(),2)
=ADDRESS(ROW(),COLUMN(),3)
=ADDRESS(ROW(),COLUMN(),4)
Sebasitankg
  • 71
  • 1
  • 1
6

Without INDIRECT(): =CELL("width", OFFSET($A$1,ROW()-1,COLUMN()-1) )

Cosmin Rus
  • 330
  • 2
  • 8
2

I found the best way to handle this (for me) is to use the following:

Dim MyString as String
MyString = Application.ThisCell.Address
Range(MyString).Select

Hope this helps.

Barry LaBonte
  • 21
  • 1
1

There is a better way that is safer and will not slow down your application. How Excel is set up, a cell can have either a value or a formula; the formula can not refer to its own cell. You end up with an infinite loop, since the new value would cause another calculation... . Use a helper column to calculate the value based on what you put in the other cell. For Example:

Column A is a True or False, Column B contains a monetary value, Column C contains the folowing formula: =B1

Now, to calculate that column B will be highlighted yellow in a conditional format only if Column A is True and Column B is greater than Zero...

=AND(A1=True,C1>0)

You can then choose to hide column C

Ester
  • 29
  • 1
  • 1
  • 11
1

Full credit to the top answer by @rick-teachey, but you can extend that approach to work with Conditional Formatting. So that this answer is complete, I will duplicate Rick's answer in summary form and then extend it:

  1. Select cell A1 in any worksheet.
  2. Create a Named Range called THIS and set the Refers to: to =!A1.

Attempting to use THIS in Conditional Formatting formulas will result in the error:

You may not use references to other workbooks for Conditional Formatting criteria

If you want THIS to work in Conditional Formatting formulas:

  1. Create another Named Range called THIS_CF and set the Refers to: to =THIS.

You can now use THIS_CF to refer to the current cell in Conditional Formatting formulas.

You can also use this approach to create other relative Named Ranges, such as THIS_COLUMN, THIS_ROW, ROW_ABOVE, COLUMN_LEFT, etc.

Nic Paul
  • 31
  • 4
1

Inside tables you can use [@] which (unfortunately) Excel automatically expands to Table1[@] but it does work. (I'm using Excel 2010)

For example when having two columns [Change] and [Balance], putting this in the [Balance] column:

=OFFSET([@], -1, 0) + [Change]

Note of course that this depends on the order of the rows (just like most any other solution), so it's a bit fragile.

Jannes
  • 1,784
  • 1
  • 17
  • 20
0

Reference to a cell that include this formula (self reference): address(row();column())

E.g. getting the value of the cell above: indirect(address(row()-1;column()))

Or what the OP asked: =Cell(width;address(row();column()))

Rian Alotha
  • 1
0

EDIT: the following is wrong, because Cell("width") returns the width of the last modified cell.

Cell("width") returns the width of the current cell, so you don't need a reference to the current cell. If you need one, though, cell("address") returns the address of the current cell, so if you need a reference to the current cell, use indirect(cell("address")). See the documentation: http://www.techonthenet.com/excel/formulas/cell.php

Myer
  • 3,670
  • 2
  • 39
  • 51