Excel’s CELL function doesn’t get a lot of press but it can be handy to know.
The CELL Function simply returns information about the formatting, contents or location of a cell.
Let’s look at an example:
In cell A5 I have a date and in C5 I have my CELL formula =CELL(“format”,A5). The result returned in cell C5 is the text D1, as you can see below:
The result, D1, refers to the type of format applied to cell A5; ‘D’ for date and 1 for the type of date format, being dd/mm/yyyy.
It might not seem much use at first glance but I’ll show you some applications for it in a moment.
First some theory.…
CELL Function Syntax
The syntax for CELL is:
The info_type allows us to specify the type of information we want to return. We can choose from a long list:
More on what each info_type means in a moment.
The reference is the cell we want to return information about. It’s an optional argument and if you omit it Excel will return the information for the last changed cell. Alternatively, if the reference is for a range of cells then you will get the information for the upper left cell in the range only.
|address||The address of the first cell in reference, as text. e.g $A$1|
|col||Column number of the cell in reference. e.g. a cell in column C would return 3|
|color||The value 1 if the cell is formatted in color for negative values; otherwise returns 0 (zero). Note: this is not the color of the text in the cell, it simply indicates if the format of the cell has special color formatting for negative values.|
|contents||The actual value of the cell; not a formula.|
|filename||Filename (including full path) of the file that contains reference, as text. Returns empty text ("") if the worksheet that contains reference has not yet been saved.|
|format||Text value corresponding to the number format of the cell. More on this below. Returns:
|parentheses||The value 1 if the cell is formatted with parentheses for positive or all values; otherwise returns 0.|
|prefix||Text value corresponding to the "label prefix" of the cell. Returns:
|protect||The value 0 if the cell is not locked; otherwise returns 1 if the cell is locked.|
|row||Row number of the cell in reference. e.g. a cell in row 2 would return 2.|
|type||Text value corresponding to the type of data in the cell. Returns:
|width||Column width of the cell, rounded off to an integer. Each unit of column width is equal to the width of one character in the default font size.|
CELL Function Examples
Ok, that’s enough theory, let’s look at some examples and then some practical uses for CELL.
The table below shows the results for some of the different ‘info_types’ for the values in column A (that is, columns C through H contain CELL formulas which reference the values in column A):
The ‘Format’ info-type has a range of results. The table below explains what those results represent:
|Result Returned||Actual Cell Format (as applied via the Format Cells Dialog Box)|
|"D1"||d/mmm/yy or dd/mmm/yy|
|"D2"||d/mmm or dd/mmm|
|"D4"||m/d/yy or m/d/yy h:mm or mm/dd/yy|
|"G"||# ?/? or # ??/??|
CELL Function Examples
Ok, now for some practical applications of CELL.
Ignore Cells with Specific Formatting
A while back Kyle wrote in with this question:
My cells contain one of the four things listed below
- random dates
- the letter N
- the letter I
I’m trying to write a formula that will give me a percentage of the cells that contain dates out of the total number of cells that I have selected. I would also like to exclude from the percentage listed above all cells containing N/A. How would l do that?
And Catalin, our in-house Excel Guru, replied with this solution using the CELL function:
Assuming that your range is in column A, starting from row 1, use this formula in cell B1:
=CELL(“format”,A1) and copy it down as needed.
Then you can count the results of the CELL formula and calculate the percentage with this formula:
Of course this formula relies on the cell format matching the data. It is possible to format a cell containing text as a date which would result in an error.
Return the File Path:
We can display the file path of the current workbook using this formula:
Notice how we can omit the reference argument since the file name is not dependent on one cell.
The result is the file path, file name and sheet name:
D:\My Documents\Training\Blog\Excel CELL Function\[excel_cell_function.xlsx]Sheet1
This is handy for inserting the file name in my worksheet so that when I print the report the file path is visible. It helps you find the file months later!
You can put this in the header/footer too but it’s just as easy to use the CELL function in a cell somewhere.
File and Worksheet Name
The above =CELL("filename") formula can also be used in INDIRECT formulas to populate the worksheet name but you need to isolate just the worksheet name component from the whole file path first, which can be done with this formula:
Which will return:
Note: with the single apostrophe at each end and an exclamation mark the worksheet name is ready for use in your formulas. The apostrophes are only required if your sheet name has a space in it, but it’s handy to put them in anyway because if you add a space to your sheet name later on your formulas won’t break.
More on using the MID function to extract text strings here.
Warning – Advanced Use of CELL
I learnt this tip from fellow Excel MVP, Jordan Goldmeier of OptionExplicitVBA.com
If you ever use the INDEX function to return a cell reference to a single cell you might want to verify that the formula is actually returning a cell reference as opposed to the value in the cell.
You can do so by wrapping the INDEX formula in a CELL function like so:
The CELL function will return the address given by INDEX as opposed to the value that typically appears.
CELL Function Tips
- If you change the format of a cell you need to hit F9 to recalculate the formulas as it doesn’t automatically recalculate upon a formatting change.
- The info_type argument “Color” doesn’t refer to the color of the text or format of the text, it refers to whether the number format is such that it formats negative values in red, or any other color formatting applied through a custom number format.
- The CELL function isn’t a native Excel function, it’s actually provided for compatibility with other spreadsheet programs.
CELL Function Errors
#VALUE!: if your CELL formula returns the #VALUE! error it’s likely that the info_type argument supplied isn’t recognised by Excel.
#NAME?: The #NAME? error will appear if you don’t enter a valid ‘Reference’ argument or if you misspell the function name.
Hi, How can I find a bold formatted Cell?
Create a defined name IsBold, with this formula: =GET.CELL(20,!A1)
Then, use it in cell B1: =IsBold
Will tell you if at least first char in B1 is bold.
I just like the title of this article! 🙂
🙂 thanks, Steven.
I am working with a sumif formula and the contents of the cell are returning a #Value error. I see from your post that the formula is not recognizing the type.
I have used the cell formula and the result is ,2-.
How can I correct the error?
Can you please upload your file to our Help Desk system so we can take a look at this problem? It’s hard to detect the reason without seeing the data.
Thanks for understanding,
Thanks Catalin. If figured it out!
You’re wellcome 🙂
I have Excel 2007
I only get D4, no matter how cell A5 is formatted
Nothing else works. Hitting F2 and enter, or F9 does not change “D”
If you type a date in A5, =CELL(“format”,A5) will return D1 if the cell is formatted to show as short date, and G if the cell is formatted to show long date (format: [$-F800]dddd, mmmm dd, yyyy). Of course, you have to press F9 after changing the format in A5.
Hope it helps 🙂
Hi again Mynda
A word of caution for users when selecting the “Filename” info_type:
If you omit the Reference argument [e.g. =CELL(“Filename”) ] the filename and path returned is that of the ACTIVE workbook – which may NOT be the host workbook holding the formula/function! To ensure that you get the filename and path of the host workbook, you must include a reference to a cell in that workbook. Similarly, if you also want the name of the host worksheet then the reference must be to a cell on the target sheet (which could be another sheet, but this requires the target sheet’s name in the reference, like entering =CELL(“Filename”,Sheet2!A1) in Sheet1!A1.)
Try this to see what I mean.
1. Have two previously saved workbooks open and both showing (two windows).
2. In one of them enter two formulae: Say =CELL(“Filename”,A1) in A1 and =CELL(“Filename”) in A2.
3. Activate the other workbook
4. Press F9 to calculate.
The 1st formula will return the info for the host workbook, whereas the 2nd will return the details of the active workbook. Potentially dangerous, eh?
Surprisingly, or not depending on your opinion of Microsoft, the Help info about this useful function does not mention this trap.
I have this function as described above entered in cell A1 of my Book.xlt* and Sheet.xlt* templates, as well as having the following macro (saved in my Personal.xlsb file) linked to a button on my QAT, which when clicked will insert the =CELL(“Filename”,[ref]) function into the active cell, and then format that cell as bold & 8 point Arial font.
.NumberFormat = “General”
.Bold = True
.Name = “Arial”
.Size = 8
ActiveCell.FormulaR1C1 = “=CELL(“”filename””,RC)”
Enjoy the trip to the MVP Summit. While you’re there, any chance of persuading MS to fix all the bugs and things in Excel that just don’t work the way we all want them to rather than give us yet more bells and whistles??
Cheers, Col. Thanks for letting us know about that trap.