Excel Show Formula as Text in Another Cell: Direct Cell Reference Trick

That moment when you need to see the actual formula instead of its result? I’ve been there a hundred times during late-night spreadsheet sessions.

There’s a direct cell reference trick that reveals formulas as text without breaking your workflow. No add-ins, no complex steps—just a simple prefix that flips the switch.

You’ll spot errors faster, audit calculations better, and even share formulas with colleagues without them seeing the computed value. Works in every Excel version from 2010 onward.

Plus, it’s reversible in seconds if you change your mind. Here’s how to do it right the first time.

What Youll Need

Instructions

Here’s the direct method I use to display formulas as text in Excel without losing functionality.

Click on the cell where you want the formula to appear as text. I always start by entering the formula normally, like =SUM(A1:A10), to ensure it works before converting it. This prevents errors from propagating through your worksheet.

Verify the formula returns the expected result. If it shows an error, fix it now before proceeding. A broken formula will still display as text, but it won’t be useful for reference or debugging later.

In the cell where you want to display the formula as text, enter =FORMULATEXT(reference), replacing “reference” with the cell containing your original formula. For example, if your formula is in A1, use =FORMULATEXT(A1).

Press Enter, and the cell will now show the formula instead of its result. This function is dynamic—if you edit the original formula, the text representation updates automatically. I rely on this for auditing complex sheets.

For a static display that won’t update with changes, type an apostrophe ‘ before the formula in the target cell, like ‘=SUM(A1:A10). Excel treats the cell as text, showing the formula verbatim.

This method is ideal for documentation or when you need to preserve a formula’s state at a specific moment. Note that the apostrophe isn’t visible in the cell—only the formula text appears. Real talk: this is the simplest way if you don’t need dynamic updates.

To convert multiple formulas to text at once, press Ctrl+H to open Find and Replace. In the “Find what” field, enter =. In the “Replace with” field, enter ‘=. Click Replace All.

This adds an apostrophe to every formula in the selection, turning them into text. Double-check a few cells to confirm the formulas now display as text. If any cells still calculate, repeat the process for those specific cells.

Review the cells to ensure all formulas appear as text. If any still show results, reapply the apostrophe prefix or FORMULATEXT function. I like to format these cells with a distinct font color or background to differentiate them from active formulas.

For large worksheets, use conditional formatting to highlight cells containing formulas as text. This makes it easier to track and manage them over time. Here’s the thing—this small step saves hours of confusion later.

Tips and Common Mistakes

These practical insights will help you avoid common pitfalls when displaying formulas as text in Excel.

The Technique That Changed Everything: In Step 2, when using the FORMULATEXT function, remember it only works with cell references containing formulas. If you reference a cell with a value instead of a formula, you’ll get a #NAME? error. I learned this the hard way after spending hours troubleshooting what seemed like a simple mistake.

Static vs Dynamic Clarity: For Step 3’s apostrophe method, understand this creates a permanent text version that won’t update if you change the original formula. This is perfect for documentation but terrible for auditing live sheets. I use this when I need to preserve a formula’s exact state at a specific point in time.

Bulk Conversion Warning: When using the Find and Replace method in Step 4, be extremely careful with your selection. I once accidentally converted every cell in a 5000-row spreadsheet because I forgot to limit the selection range. Always double-check your highlighted area before clicking Replace All.

Formatting Insight: The tip in Step 5 about using distinct formatting for text formulas is more important than you might think. In complex sheets, I’ve found that using a light gray background with italicized text makes these cells instantly recognizable, preventing accidental edits to what should be static documentation.

Frequently Asked Questions

Displaying a formula as text in Excel is a breeze with the __PROTECT_6 function or the apostrophe trick. 💡 Now that you’ve mastered this, try combining it with PROTECT_7__ to document multiple formulas at once! Experiment and streamline your workflow today.