CELL Worksheet Function

I learned something today. I “knew” that omitting the optional reference argument from the CELL worksheet function meant that the cell that contained the worksheet function was used. Patrick McDonald correctly pointed out that it uses the last changed cell, not the host cell.

In a new worksheet, type =CELL("width") in cell B3. If you’re using Excel 2003 like me, you’ll probably get 8 as the answer. Now increase the width of column D and type a ‘1’ in D3. Cell B3 will report the width of D3 because it’s the last cell changed.


I like learning things. I don’t like learning things that I should have already known. Hopefully this is the last one and that now I know everything.

Posted in Uncategorized

10 thoughts on “CELL Worksheet Function

  1. I tried typing in =CELL(“address”). Now I get the address of the last cell I changed. That could be useful.

  2. No, it is not the last changed cell. Excel help is wrong.

    Leaving out the cell reference in the CELL formula results in it referring to the *active* cell.

    Try this.
    In A1 enter =CELL(“width”). It will show 8 in Excel 2010.
    Now, resize D. A1 will still show 8.
    Change D1. Now, A1 will show the new width of D.
    Select E1. CTRL+SHIFT+ALT+F9. Now, A1 will show 8.

    Here’s another test.

    Enter =CELL(“address”) in say, A10. A10 will now show $A$10.
    Select E8. CTRL+SHIFT+ALT+F9. Now, A1 will show $E$8.

    Or,
    Save the workbook — say, it’s called Book2.xlsx.
    Now, enter =CELL(“address”) in say A10. A10 will contain $A$10.
    Now, switch to Sheet2. CTRL+SHIFT+ALT+F9. Switch to Sheet1.
    A10 will contain [Book2.xlsx]Sheet2!$A$1

  3. Getting REALLY PICKY, it returns the property of the active cell at the time of the last recalc.

    Do Excel 2007 and 2010 (and 2008 for Macs) require CSA+F9? Excel 2003 updates cells with volatile function calls when just pressing plain F9.

    Lotus 1-2-3’s @CELLPOINTER function was useful in old style 1-2-3 macros. Since Excel circa 1987 had a limited ability to run such 1-2-3 macros, it needed an equivalent function. But ever since MSFT dropped support for such macros from Excel there’s been no need/sensible use for CELL without a reference argument.

  4. Tushar

    Regarding this…

    It will show 8 but that width is not of A1 but of E1.

    I got the width of E1 and not of A1. I did this in Excel 2003.

    In A1 enter =CELL(“width”) >> A1 Showed 8
    Resize D >> A1 Showed 8
    Selected E1 and resized it. Pressed CTRL+SHIFT+ALT+F9 >> A1 showed 23.

    Sid


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.