• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

What the CELL!

You are here: Home / Excel Formulas / What the CELL!
Excel CELL Function
October 30, 2014 by Mynda Treacy

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:

Excel cell function example

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:

=CELL(info_type, [reference])

The info_type allows us to specify the type of information we want to return. We can choose from a long list:

cell function info_types

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.

CELL info_types

info_type Returns
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:
  • "-" at the end of the text value if the cell is formatted in color for negative values.
  • "()" at the end of the text value if the cell is formatted with parentheses for positive or all values.
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:
  • a single quotation mark (') if the cell contains left-aligned text,
  • a double quotation mark (") if the cell contains right-aligned text,
  • a caret (^) if the cell contains centered text,
  • a backslash (\) if the cell contains fill-aligned text,
  • and empty text ("") if the cell contains anything else.
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:
  • "b" for blank if the cell is empty,
  • "l" for label if the cell contains a text constant,
  • "v" for value if the cell contains anything else.
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):

Excel cell function multiple example

Format Legend

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
"D3" mmm/yy
"D4" m/d/yy or m/d/yy h:mm or mm/dd/yy
"D5" mm/dd
"D6" h:mm:ss AM/PM
"D7" h:mm AM/PM
"D8" h:mm:ss
"D9" h:mm
"G" General
"F0" 0
",0" #,##0
"F2" 0.00
",2" #,##0.00
"C0" $#,##0_);($#,##0)
"C0-" $#,##0_);[Red]($#,##0)
"C2" $#,##0.00_);($#,##0.00)
"C2-" $#,##0.00_);[Red]($#,##0.00)
"P0" 0%
"P2" 0.00%
"S2" 0.00E+00
"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  
  1. random dates
  2. the letter N
  3. the letter I
  4. N/A

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:

=COUNTIF(B1:B8,"D*")/(COUNTA(A1:A8)-COUNTIF(A1:A8,"#N/A"))

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:

=CELL("filename")

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:

="'"&MID(CELL("filename"),FIND("]",CELL("filename"))+1,256)&"'!"

Which will return:

'Sheet1'!

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:

=CELL("address", INDEX(…MATCH()…))

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.

Excel CELL Function

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.




Category: Excel Formulas
Previous Post:getpivotdata functionExcel GETPIVOTDATA Function
Next Post:My 2014 Microsoft MVP Summit Experience

Reader Interactions

Comments

  1. Cees

    January 21, 2021 at 11:59 pm

    Hi, How can I find a bold formatted Cell?

    Reply
    • Catalin Bombea

      January 22, 2021 at 12:30 pm

      Hi Cees,
      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.

      Reply
  2. Steven Rider

    May 5, 2018 at 3:53 am

    I just like the title of this article! πŸ™‚

    Reply
    • Mynda Treacy

      May 5, 2018 at 8:02 am

      πŸ™‚ thanks, Steven.

      Reply
  3. Eve Peterson

    January 17, 2015 at 10:20 am

    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?

    Thank you!

    Reply
    • Catalin Bombea

      January 18, 2015 at 3:59 pm

      Hi Eve,
      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,
      Catalin

      Reply
      • Eve Peterson

        January 21, 2015 at 2:45 pm

        Thanks Catalin. If figured it out!

        Reply
        • Catalin Bombea

          January 21, 2015 at 3:56 pm

          You’re wellcome πŸ™‚

          Reply
  4. Theodore Wells

    November 1, 2014 at 6:08 am

    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”

    Reply
    • Catalin Bombea

      November 1, 2014 at 7:24 pm

      Hi Theodore,
      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 πŸ™‚
      Cheers,
      Catalin

      Reply
  5. Col Delane

    October 30, 2014 at 4:15 pm

    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.

    Sub Cell_Info()
    With Selection
    .NumberFormat = “General”
    With .Font
    .Bold = True
    .Name = “Arial”
    .Size = 8
    End With
    End With
    ActiveCell.FormulaR1C1 = “=CELL(“”filename””,RC)”
    End Sub

    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 Delane
    Perth, WA

    Reply
    • Mynda Treacy

      October 31, 2014 at 9:12 am

      Cheers, Col. Thanks for letting us know about that trap.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.