CELL Function

Returns information about a cell's location, contents or formatting.

Category

Information

Available in

All versions, Microsoft 365

Returns

Text or a number, depending on info_type

Want to practice CELL in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

CELL answers a question about a cell. The first argument is the question — "address", "row", "contents", "filename" and so on — and the second is the cell you're asking about.

Its most popular trick is CELL("filename"), which returns the file path and sheet name, so a report can label itself.

Syntax

=CELL(info_type, [reference])

Arguments

ArgumentRequired?What it does
info_typeYesWhat you want to know, in quotes: "address", "col", "row", "contents", "type" (b = blank, l = text, v = anything else), "filename", "format", "prefix", "protect", "width", "color" or "parentheses".
referenceOptionalThe cell to ask about. If you give a range, CELL uses its top-left cell. Leave it out and Excel uses the last cell that changed — so always include it.

Examples

Example 1 — Asking different questions about one cell

Each row asks CELL something different about D2.

B2fx=CELL(A2,$D$2)
ABCD
1info_typeAnswerTarget
2address$D$2Northstar
3row2
4col4
5contentsNorthstar
6typel
=CELL(A2,$D$2) Explain

"l" stands for label — the cell holds text. A number would give "v" (value) and an empty cell "b" (blank).

Example 2 — Put the sheet name on a report

CELL("filename") returns something like C:\Reports\[Q1 Sales.xlsx]Summary. Everything after the ] is the sheet name.

=TEXTAFTER(CELL("filename",A1),"]") Explain
  1. Save the workbook first — an unsaved file returns empty text.
  2. Always give a cell on the same sheet (A1 here); without it, the name can show another sheet's name.
  3. In older versions use =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,31).

Example 3 — Where is the largest sale?

INDEX returns a reference, so CELL can report its address. With the biggest value in B4, this returns $B$4.

=CELL("address",INDEX(B2:B10,MATCH(MAX(B2:B10),B2:B10,0))) Explain

Common mistakes

  • The answer changes on its own. Without the reference argument, CELL describes whichever cell was edited last. Always pass a reference.
  • "format" doesn't update straight away. Changing a cell's number format doesn't trigger a recalculation. Press F9 to refresh.

Notes

  • "format" returns codes such as G (General), F2 (2 decimals), C2 (currency), P0 (percent) and D1 (a d-mmm-yy date).
  • In our practice Sandbox, CELL answers address, row, col, contents, type, prefix, protect and filename; formatting questions (format, width, color, parentheses) need Excel itself.
  • Related functions: INFO, ADDRESS, SHEET, TEXTAFTER.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.