Excel FORMULATEXT

Show me the magic behind the number.

What in the World Is FORMULATEXT?

Okay, so you know how sometimes you look at a cell and it’s just a number, like 42, and you think, “Where did that come from?” Maybe it’s a sum, maybe it’s a VLOOKUP, maybe it’s some crazy nested IF that your coworker wrote three years ago and nobody touches.

FORMULATEXT is like a pair of X-ray glasses. You point it at a cell, and instead of showing you the result, it shows you the formula as text. It’s that simple.

The Syntax (It’s Embarrassingly Short)

That’s it. One argument: the cell you want to peek inside. It’s like a detective’s magnifying glass for your spreadsheet.

Why Should You Care?

• Audit time: You get a spreadsheet from someone else. Instead of clicking into 50 cells to see what’s going on, you can use FORMULATEXT to print all the formulas in a neat column.
• Teaching / explaining: You’re showing a class how a formula works. Instead of saying “trust me”, you can show the exact formula next to the result.
• Documentation: You can keep a “formula map” sheet that lists every formula in your workbook. It’s like leaving breadcrumbs for your future self.
• Debugging: When a formula breaks, seeing the text version helps you spot typos or missing references.

Easy Dipping Your Toes

Example 1: What’s the SUM?

You’ve got a cell, say A10, that adds up a bunch of numbers: =SUM(A1:A9). You want to see that formula as text in another cell.

In cell B10, type:

B10 will now show: =SUM(A1:A9). You can literally copy that text and paste it somewhere else if you need to.

Example 2: Check If a Cell Has a Formula

You’ve got a big sheet. You want to know which cells are raw numbers and which are calculated. Combine FORMULATEXT with ISFORMULA.

In cell C1:

Now if A1 has a formula, it shows the formula. If not, it says “No formula”. Handy, right?

Example 3: Quick Formula Peek

You’re in a meeting. Someone points to a cell and asks, “What’s that calculating?” Instead of clicking into the cell (and possibly messing something up), just type =FORMULATEXT(cell) in an empty cell and boom — you’re the hero.

Medium Getting Crafty

Example 1: Build a Formula Dictionary

You’ve got a workbook with a bunch of named ranges and complex formulas. You want a summary sheet that lists every formula, so you can see at a glance what’s happening.

In column A, list your formula cells (e.g., A1, A2, A3…). In column B:

Drag that down. Now you’ve got a living dictionary. If anyone changes the formulas in column A, column B updates automatically. You’re basically a wizard.

Example 2: Find All VLOOKUPs

You want to find every cell that uses VLOOKUP. Combine FORMULATEXT with ISNUMBER and SEARCH.

In a helper column next to your data:

It checks if “VLOOKUP” appears in the formula text. If yes, it marks it. Now you can filter for “Yes” and see all your VLOOKUPs. Great for when you’re refactoring.

Example 3: Formula Length Check

Ever seen a formula that goes on for miles? You can use LEN and FORMULATEXT to find the longest formulas in your sheet.

In a cell next to your formula:

Sort that column descending, and you’ll spot the monsters that need simplifying. Your future self will thank you.

Hard Next Level Stuff

Example 1: Dynamic Formula Documentation with Comments

You can combine FORMULATEXT with your own notes to create self-documenting spreadsheets. Imagine column A has formulas, column B shows the formula as text, and column C has your explanation.

Set up like this:

Now anyone who opens your workbook can hover over D1 and see the explanation. You’re basically writing a user manual inside Excel.

Example 2: Find and Replace Across Formulas

You need to change a sheet name in 50 formulas. Instead of doing it manually, use FORMULATEXT to find all formulas that reference “Sheet1” and then use SUBSTITUTE to create corrected formulas.

In column C (assuming formula in A1):

Now column C shows the formula with “Sheet2” instead of “Sheet1”. You can copy that text and paste it into the original cells (with a little care). It’s a hacky but effective find-and-replace for formulas.

Example 3: Formula Dependencies Map

You’ve got a complex model. You want to see which cells depend on which. Use FORMULATEXT to extract cell references and build a crude dependency tree.

This one’s a bit advanced, but stay with me:

It’s not pretty, but you can build a list of all references used in a formula. Combine that with a bit of VBA or Power Query, and you’ve got a full dependency map. That’s the kind of stuff that makes IT guys jealous.

Cheat Sheet: The Rules

• Only works on cells with formulas. If the cell has a number, text, or is blank, you get #N/A. That’s Excel’s way of saying “nothing to see here.”
• It returns the formula as a string. You can’t use the result in calculations (unless you use VALUE or something, but that’s a whole other story).
• It updates automatically. If the formula in the referenced cell changes, FORMULATEXT updates too. No need to recalc manually.
• You can use it with other text functions. LEN, FIND, SEARCH, SUBSTITUTE, LEFT, RIGHT, MID — they all work on the text that FORMULATEXT returns.

Stuff You’ll Probably Mess Up (It’s Fine, We All Do)

• Pointing it at a cell without a formula. You’ll get #N/A. It’s not broken, it’s just doing its job.
• Forgetting that it’s case-sensitive? No, it’s not. Excel formulas aren’t case-sensitive. But you might type FORMULATEXT wrong. It happens.
• Trying to use the result in a calculation. The result is text, not a number. If you try to add it to something, you’ll get an error. Use VALUE if you need to convert.
• Not using it for audits. This is the biggest mistake. FORMULATEXT is a goldmine for auditing and you’re sitting on it. Use it.

Now You Try

Open Excel and try these out. Seriously. Don’t just read — do.

1. Basic peek: Type =SUM(1,2,3) in A1. In B1, type =FORMULATEXT(A1). See? Magic.
2. Find all formulas: Fill a column with some formulas (SUM, AVERAGE, etc.). In the next column, use FORMULATEXT to show each formula. Now you’ve got a mini audit.
3. Check for VLOOKUP: Write a formula that uses FORMULATEXT and SEARCH to flag any cell that contains “VLOOKUP”.
4. Formula length: Find the longest formula in your sheet using LEN and FORMULATEXT. Challenge your coworkers to beat it.
5. Build a dashboard: Create a little dashboard that shows the formula, the result, and a description for every key cell in your workbook. You’ll never be confused again.