You probably don't know this Excel function: =CELL( )

[LouisDeconinck]

I recently came across a function I have never used before and you've probably not heard about it either.

The function I'm talking about is CELL(info_type, [reference]), I think it's quite neat. It gives you information about the current selection in your workbook, at least if you leave the second argument empty.

So all you do is provide an argument with the kind of information you're looking for such as: address, col, color, contents, filename, format, row, type width, ... And you will get back this information. If you fill out the second argument you will get this information for a specified cell, a bit like how the ROW and COLUMN functions work, but a lot more flexible.

Here's some documentation from Microsoft: https://support.microsoft.com/en-us/office/cell-function-51bd39a5-f338-4dbe-a33f-955d67c2b2cf

Now where things get really cool is if you use a little bit of VBA to automatically recalculate your worksheet after every click. That means that with every click the CELL function will update and give you new information about the active cell.

The VBA code you need for that is: Application.Calculate, that's all.

One practical way to use this, is to highlight the active cell and row with conditional formatting. If you'd like a tutorial on this, I made video doing exactly this: https://www.youtube.com/watch?v=lrsdtzSctTM

Do you have any other use cases on how to use the =CELL function?

[#2]

@LouisDeconinck 

A well-known use of the CELL function is to return the name of the active sheet in a cell: see Get sheet name only 

[#3]

@LouisDeconinck 

Also, I like how handy it comes when using CELL("address"). It can be useful when combined with nested INDEX/MATCH functions and then apply an INDIRECT to that cell address in order to perform further calculations.

[#4]

Thanks for the tip, that is indeed a very cool functionality.