Part of the free Module 5: Excel Formulas and Functions · Function 7 of 102 · Full Excel course
The Excel CELL function returns information about a cell’s formatting, location or contents, such as its address, column number, number format, or whether it is locked. CELL in Excel is the tool for formulas that need to react to a cell’s properties rather than its value, and for retrieving the workbook’s file name and path.
CELL syntax
=CELL(info_type, [reference])Arguments
| Argument | Required | Meaning |
|---|---|---|
| info_type | Required | Text naming the property you want (see table below), in quotes. |
| reference | Optional | The cell to inspect. For a range, CELL reads the top-left cell. If omitted, it reports on the last cell that was changed, which is rarely what you want; always supply it. |
info_type values
| info_type | Returns |
|---|---|
| “address” | Absolute address as text, e.g. $A$1. |
| “col” | Column number. |
| “row” | Row number. |
| “contents” | The value in the cell (not the formula). |
| “type” | “b” for blank, “l” for label (text), “v” for value (anything else). |
| “format” | Code for the number format: “G” general, “F2” two decimals, “C2” currency, “D1” date, “P0” percent, and so on. |
| “filename” | Full path, file name and sheet name, e.g. C:\Reports\[Sales.xlsx]Q1. Empty until the file has been saved. |
| “protect” | 1 if the cell is locked, 0 if unlocked. |
| “width” | Column width rounded to an integer (Excel 365 also returns TRUE/FALSE for default width). |
| “color” | 1 if negative numbers are shown in colour, otherwise 0. |
| “parentheses” | 1 if positive numbers are shown in parentheses, otherwise 0. |
| “prefix” | Alignment prefix character of text: ‘ left, ” right, ^ centred. |
Step-by-step example
=CELL("address", A1)- Excel reads the info_type “address”.
- It inspects the reference A1.
- It returns the text $A$1. Point the reference at a MATCH result and you get the address of a found value.
Practical use cases
1. Sheet name in a cell
=MID(CELL("filename", A1), FIND("]", CELL("filename", A1))+1, 255)2. Workbook name only
=MID(CELL("filename", A1), FIND("[", CELL("filename", A1))+1, FIND("]", CELL("filename", A1))-FIND("[", CELL("filename", A1))-1)3. Highlight unlocked input cells
=CELL("protect", A1)=0Use as a conditional-formatting rule to shade every cell users are allowed to edit.
4. Flag numbers stored as text
=CELL("type", B2)="l"5. Address of the maximum value
=CELL("address", INDEX(C2:C100, MATCH(MAX(C2:C100), C2:C100, 0)))6. Check a cell is formatted as a date
=LEFT(CELL("format", A2), 1)="D"Common mistakes and errors
- #VALUE! – info_type is misspelt or not in quotes. Note that the info_type strings are English in every language version of Excel.
- Stale results – CELL is volatile for some types but does not detect formatting changes until recalculation. Press F9 after changing formats or protection.
- Empty “filename” – the workbook has never been saved. Save it first.
- Omitted reference – CELL reports on the last edited cell, giving unpredictable results. Always pass a reference.
- Excel for the web – a limited set of info_types is supported (address, col, contents, row, type, filename among others); format and protect may return #VALUE!.
Tips and best practices
- Always pass a reference, even A1, so the result is tied to a known cell and to the sheet the formula lives on.
- Save the workbook before using “filename”; unsaved files return an empty string.
- Press F9 after formatting or protection changes because those do not trigger recalculation.
- Prefer dedicated functions (ROW, COLUMN, ISTEXT) where they exist; they are simpler and spill correctly in Excel 365.
- Keep the info_type strings in English regardless of your Excel language.
Related functions
- ADDRESS – builds an address from row and column numbers.
- INFO – environment information such as Excel version and OS.
- TYPE and ISTEXT – simpler data-type tests.
- ROW and COLUMN – direct alternatives to CELL(“row”) and CELL(“col”).
- Browse the Excel Formulas hub or watch videos on YouTube.com/@PKAnExcelExpert.
Frequently asked questions
Can CELL return information about several cells at once?
No. Given a range, CELL reads only the top-left cell. Use one CELL formula per cell, or ROW, COLUMN and ISTEXT which do spill across arrays in Excel 365.
Why does CELL not update after I change formatting?
Formatting changes do not trigger recalculation. Press F9, or edit any cell, and the CELL result refreshes.
How do I get the sheet name with CELL?
Use =MID(CELL(“filename”, A1), FIND(“]”, CELL(“filename”, A1))+1, 255). The A1 reference ensures the formula reports the sheet it sits on, and the workbook must have been saved at least once.
Need a ready-made template? Browse 7,000+ Excel, Power BI and Google Sheets templates at NextGenTemplates.com.