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)

asked Apr 16, 2009 at 18:21 13.7k 27 27 gold badges 104 104 silver badges 150 150 bronze badges

13 Answers 13

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).

1 1 1 silver badge answered Aug 13, 2014 at 16:38 1,271 11 11 silver badges 14 14 bronze badges

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").

Commented Aug 13, 2014 at 16:48 This is great! Love it. You, my friend, get a bounty. Commented Aug 13, 2014 at 18:32

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-)

Commented Aug 14, 2014 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.

Commented Oct 26, 2018 at 4: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.

Commented Nov 1, 2018 at 20:57

Create a named formula called THIS_CELL

In the current worksheet, select cell A1 (this is important!)
Open Name Manager (Ctl+F3)
Click New.
Enter "THIS_CELL" (or just "THIS", which is my preference) into Name:
Enter the following formula into Refers to: =!A1 NOTE: Be sure cell A1 is selected. This formula is relative to the ActiveCell.
Under Scope: select Workbook .
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:

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.
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.

1 1 1 silver badge answered Mar 8, 2014 at 2:54 44.7k 16 16 gold badges 80 80 silver badges 121 121 bronze badges

Great trick but I couldn't get it to work in Conditional Formulas (tested in Excel 2003 and 2016). The answer from @imix works.

Commented Oct 26, 2018 at 4:17 @RickTeachy Yes, I mean inside formulas for conditional formatting. Commented Oct 27, 2018 at 5: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.

Commented Oct 28, 2018 at 4:17 @RicksupportsMonica re your assertion that CELL is nott volatile, it actuall is See this Commented May 8, 2022 at 5: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?

Commented May 8, 2022 at 14:56

=CELL("width", INDIRECT(ADDRESS(ROW(), COLUMN())))

22.8k 32 32 gold badges 113 113 silver badges 132 132 bronze badges answered Apr 16, 2009 at 18:27 Patrick McDonald Patrick McDonald 65.1k 14 14 gold badges 111 111 silver badges 125 125 bronze badges

This makes the formula volatile. Therefore, provided there are non-volatile solutions, don't use this one.

Commented Apr 16, 2009 at 18:46

It should probably be noted that the CELL function is already volatile, so whether or not the method you use to calculate the current cell is volatile will not affect the volatility of the function (in this case)

Commented Feb 15, 2018 at 10:40 what does volatile mean? Commented Nov 1, 2018 at 18:57 @Jonathan There's an explanation here: decisionmodels.com/calcsecretsi.htm Commented Nov 1, 2018 at 19:35

=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.

15.6k 16 16 gold badges 58 58 silver badges 75 75 bronze badges answered Apr 11, 2012 at 11:43 339 3 3 silver badges 2 2 bronze badges

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.

Commented Apr 2, 2015 at 16:13

=ADDRESS(ROW(),COLUMN()) =ADDRESS(ROW(),COLUMN(),1) =ADDRESS(ROW(),COLUMN(),2) =ADDRESS(ROW(),COLUMN(),3) =ADDRESS(ROW(),COLUMN(),4)

answered Dec 28, 2013 at 14:22 Sebasitankg Sebasitankg 91 1 1 silver badge 1 1 bronze badge

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

answered Apr 16, 2009 at 18:23 352k 86 86 gold badges 698 698 silver badges 694 694 bronze badges Don't worry about addressing style. All formulas use R1C1 internally anyway, it doesn't matter. Commented Apr 16, 2009 at 18:46

Ah, thanks. Didn't bother to look it up right now (wading through the XML isn't exactly fun usually :))

Commented Apr 16, 2009 at 18:47

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

answered Jul 2, 2014 at 14:01 Cosmin Rus Cosmin Rus 398 2 2 silver badges 9 9 bronze badges

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.

answered Jun 28, 2013 at 16:11 Barry LaBonte Barry LaBonte 31 1 1 bronze badge

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.

You can then choose to hide column C