How to Show Formulas in Excel (Instead of Results)

Why Seeing Formulas in Excel Is Helpful

Excel normally shows results, not the formulas that produce them. That’s efficient for day-to-day work, but it hides the logic needed when something requires review.

Auditing and debugging spreadsheets

When a number looks wrong, displayed formulas make it easier to diagnose the issue. Instead of hunting through individual cells, you can scan the sheet and spot problems immediately, such as:

• Broken references: A formula pointing to a deleted cell returns errors like #REF!.

• Incorrect ranges: Totals may skip rows or columns because the formula only covers part of the data.

• Faulty logic: Conditions, operators, or calculation order may not match the intended logic.

• Inconsistent formulas: One cell in a column may follow a different pattern than the rest.

• Hardcoded values: A result may look calculated, but actually contains a fixed number.

Learning and reviewing spreadsheet logic

Showing formulas is also one of the fastest ways to understand how a spreadsheet works if it wasn’t built by you.

This is particularly helpful in two situations:

• Learning Excel: Study real formulas, see how functions combine, and understand how references behave across cells.
• Reviewing files: Follow how values flow through the sheet and confirm where key numbers come from.

This approach works well with spreadsheets that rely on:

• Cross-sheet references like =Revenue!F2

• Conditional logic like =IF(C2="Paid","Yes","No")

• Lookup formulas like =XLOOKUP(A2,Products[ID],Products[Price])

How to Show All Formulas in a Worksheet

Excel includes a built-in feature that displays all formulas at once. Here are three quick and easy ways to show all formulas in a worksheet: 

1. Using the shortcut key 

The fastest way to show formulas is to use a keyboard shortcut.

Here’s how:

• Open the worksheet
• Press Ctrl + (grave accent). It's this key: ` key; it looks like a backwards apostrophe. You can find it between the esc and tab keys on the keyboard.

Note: Press the same shortcut again to switch back to normal view. 

Display all the formulas using keyboard shortcut. Image by Author.

Here, Excel replaces all results with their formulas across the entire worksheet. It will automatically expand column widths to fit longer formulas. 

Note: This shortcut affects only the active worksheet. Other sheets in the same workbook remain unchanged. 

2. Turning Show Formulas on and off from the ribbon

We can do the same thing using a feature available on the Excel ribbon: 

• In the Ribbon, go to the Formulas tab
• In the Formula Auditing group, click the Show Formulas button

Excel switches the worksheet to formula view immediately.

To turn it off, click Show Formulas again.

Show all the formulas using the Ribbon button. Image by Author.

3. Using the Excel Options menu 

Excel Options is another way to display formulas in cells.

To do this: 

• In Excel 2010, go to File > Options (for Excel 2007,  go to Office Button > Excel Options)
• An Excel Options dialog box will appear. In the left panel, click Advanced
• Scroll down and look for the Display options for this worksheet section
• Select the sheet from the drop-down, then check the Show formulas in cells instead of their calculated results box

Now, when we enter a formula into a cell, it will only display the formula, not the result.

Display the formulas using the Excel Options menu. Image by Author.

How to Show Formulas in Individual Cells

Sometimes we don’t want to see every formula in a worksheet. Instead, we want to see a single cell formula. 

Viewing a formula in the formula bar

The simplest way to check a formula in individual cells is to use the formula bar:

• Click the cell that contains a formula
• Look at the formula bar above the worksheet grid

Excel shows the full formula in the formula bar while the cell shows the result.

Show the formula of a single cell in the formula bar. Image by Author.

In this method, the worksheet stays unchanged, and Excel shows the formula without expanding the layout or affecting other cells.

Note: If the formula bar is hidden, enable it by going to View > Formula Bar.

Displaying formulas directly in cells

Sometimes, you want formulas to appear inside cells as text instead of being calculated. This is common when you’re creating training materials, writing documentation, or sharing examples where the formula itself matters more than the result.

Excel gives you two reliable ways to do this:

• Use an apostrophe to turn a formula into text

• Use the FORMULATEXT() function to display an existing formula

Each method works slightly differently and is helpful in different situations.

Method 1: Use an apostrophe

The quickest way to display a formula as text is to add an apostrophe before it. Excel then treats the formula as plain text and does not calculate it. Here’s how to do this:

1. Double-click the cell, or select it and click in the formula bar

2. Type an apostrophe (') before the formula

3. Press Enter

Excel will display the formula exactly as written in the cell, without calculating it.

Display formula in cells using an apostrophe. Image by Author.

Method 2: Use Find & Replace

You can also use Find & Replace to apply this approach more quickly:

1. Go to the Home tab > Find & Select > Replace. Or press Ctrl + H

2. In Find what, enter =

3. In Replace with, enter '= (apostrophe followed by equals sign)

4. Click Find Next > Replace to update one formula at a time. If you don’t press Find Next, it will throw an error pop-up. Or click Replace All to update all formulas in the worksheet

Excel will stop calculating those formulas and display them as text instead.

Display formulas of a cell using the Find & Select option. Image by Author.

Note: This method affects other formulas linked to the same cells.

Method 3: Use the FORMULATEXT() function 

The FORMULATEXT() function displays a formula from another cell as text, while leaving the original formula intact. This is helpful when formulas may change and you want the displayed text to update automatically.

Here’s how to use this function: 

1. Click an empty cell

2. Enter the formula: =FORMULATEXT(reference)

3. Replace reference with the cell that contains the formula you want to display

For example, you can display the formulas from G2:G6 in H2:H6 by referencing each corresponding cell.

Display the formulas as text using FORMULATEXT(). Image by Author.

Tip:

• Use apostrophes for static examples and teaching files.

• Use FORMULATEXT() when formulas may change and need to stay visible.

How to Show Formulas Without Changing Cell Formatting

When you turn on formula view, Excel prioritizes showing the full formula, not preserving your layout. Columns may expand, text can wrap, and cells may look cluttered. That’s normal, but it doesn’t mean you have to live with a messy worksheet.

With a few small adjustments, you can keep formulas visible and maintain a readable layout.

Fixing column width and layout issues

Formulas are usually longer than the values they calculate. When they are shown, Excel widens columns so the entire formula fits on one line.

You have a couple of easy ways to manage this:

Option 1: Set column widths before showing formulas

If layout is important, resize your columns first. Excel keeps those widths when you turn on formula view, even if the formulas are longer than the cell.

This works well for training files or screenshots where consistency matters.

Option 2: Auto-fit columns intentionally

If you want Excel to adjust columns automatically:

1. Select the entire worksheet (Ctrl + A or click the Select All button in the top-left corner)
2. Double-click any column boundary

Excel then widens columns based on the longest visible formula.

Fix the column width. Image by Author.

After you finish reviewing formulas, you can turn formula view off and auto-fit the columns again to return the sheet to a normal layout.

Preventing formulas from wrapping or overlapping

Long formulas can become hard to read if Wrap Text is enabled. Excel may break a single formula across multiple lines inside the same cell, which quickly looks cluttered.

To keep formulas on one line:

1. Go to the Home tab
2. In the Alignment group, check whether Wrap Text is turned on
3. If it is, click it once to turn it off

This keeps formulas on a single line, making them easier to scan.

Turn off the Wrap Text. Image by Author.

Note: These changes only affect how formulas are displayed. They do not change how Excel calculates results.

How to Show Formulas for Printing or Review

By default, Excel prints results, not formulas. But during audits, peer reviews, or training sessions, the formulas themselves are often what you want to see.

With a little setup, you can prepare a worksheet so formulas are easy to read on paper or in a shared file, without disrupting the layout or confusing reviewers.

Printing worksheets with formulas visible

Before printing, make sure the formulas are displayed clearly and fit on the page. Here’s how to do it: 

1. Turn on formula display using any method covered earlier
2. Adjust column widths so formulas fit on one line where possible
3. Go to File > Print to open Print Preview
4. Review the preview and adjust the layout as needed:
• Switch to Landscape orientation to fit more formulas across the page.
• Click Page Setup to fine-tune margins, scaling, or page breaks.
5. When everything looks right, click OK and print the sheet or save it as a PDF

Print the sheets with formulas visible in the cells. Image by Author.

Sharing formula-based reviews

When you’re sharing a spreadsheet for formula review, clarity weighs more than presentation. 

Here are a few best practices to follow so your reviewers understand the logic quickly:

• Keep the layout simple: Avoid merged cells, hidden rows, or unnecessary formatting.
• Control column width intentionally: Make formulas readable without excessive scrolling.
• Add context where needed: A short note next to complex formulas can save time and prevent misunderstandings.
• Share a review copy: Duplicate the file before sharing so the original stays safe from accidental edits.

Note: Turn off formula view before final delivery unless the recipient specifically asks to see formulas. Most end users only need results.

Common Problems When Showing Formulas

Showing formulas may be confusing the first time you use Excel. But you should know in most cases, Excel isn’t broken, but maybe a setting is toggled or the cell is being treated as text.

Here’s how to recognize each situation and fix it quickly.

Accidentally enabling Show Formulas

Sometimes, Excel displays formulas instead of results across the entire worksheet. This usually happens when Show Formulas is turned on by accident, often by pressing a keyboard shortcut.

When this happens, all calculated values disappear and are replaced by formulas.

How to fix it:

• Press Ctrl + (the grave accent key, usually above Tab)
• Or go to the Formulas tab and click Show Formulas to turn it off

The worksheet immediately returns to showing results.

Formulas not working

Sometimes a formula appears in a cell, but Excel never calculates it even though Show Formulas is turned off. This usually means the cell was formatted as text before the formula was entered. Excel treats anything typed into a text-formatted cell as plain text.

Formula displayed as text instead of the result. Image by Author.

How to fix it:

1. Select the affected cell

2. Go to the Home tab > Number group

3. Change the format from Text to General

4. Click inside the cell, press F2, then press Enter

This forces Excel to re-read the formula and calculate it correctly.

Change the cell format to General. Image by Author.

Confusing formulas with text values

Sometimes a cell looks like it contains a formula, but Excel still doesn’t calculate it even after changing the format to General.

This usually happens because the formula starts with:

• A leading apostrophe ('=SUM(A1:A5))

• Or a leading space before the equals sign

Both tell Excel to treat the content as text. How to fix it:

1. Click the cell
2. Look in the formula bar
3. Remove any leading apostrophe or extra space
4. Press Enter

Once the extra character is removed, Excel calculates the formula normally.

Remove the characters from the formula. Image by Author.

Best Practices for Working with Visible Formulas

Showing formulas may be helpful, but you don’t want it turned on all the time. The goal is to move smoothly between views and protect formulas when files are shared with others.

Here are a few best practices to help you stay efficient and avoid mistakes.

Switching between values and formulas efficiently

When editing or reviewing a worksheet, you’ll often switch back and forth between results and formulas. The fastest way to do this is with the keyboard shortcut.

• Press Ctrl + to toggle Show Formulas on or off

This lets you quickly inspect logic, then return to normal values without changing any formatting or calculations.

Recommended workflow:

1. Turn on formula view to review or debug.
2. Make edits where needed.
3. Turn formula view off before continuing regular work.

Using the shortcut keeps you focused and avoids unnecessary layout changes.

Using formulas safely in shared files

In shared spreadsheets, formulas are easy to change by accident. One misplaced click can overwrite a key calculation without anyone noticing.

To prevent this, you can lock formula cells while still allowing others to edit inputs. So here’s how to protect formulas:

1. Select the entire worksheet (Ctrl + A or click the Select All button)

2. Right-click and choose Format Cells

3. In the Format cells dialog box > open the Protection tab and uncheck Locked and Hidden

4. Select only the cells that contain formulas

5. Right-click > Format Cells > Protection, then check Locked

6. Click OK

Lock specific cells using the Format Cells option. Image by Author.

7. Go to the Review tab and click Protect Sheet
8. In the Protect sheet dialog box, enter the password to protect the sheet. And check the Select unlocked cells box
9. Click OK

Now, users can edit input cells but cannot change protected formulas. 

 Protect the sheet from accidental edits. Image by Author.

Highlighting cells with formulas instead of showing them

Sometimes you don’t need to see formulas everywhere. You just want to know which cells contain formulas. In this case, highlighting formula cells is safer and easier than turning on a full formula view.

Option 1: Use Conditional Formatting (persistent)

Conditional formatting highlights formula cells automatically without changing what the worksheet displays.

Here’s how to use it: 

1. Select the range you want to scan

2. Go to Home > Conditional Formatting > New Rule

3. Choose Use a formula to determine which cells to format

4. Enter: =ISFORMULA(A1)(Replace A1 with the first cell in your selected range.)

5. Choose a fill color or border and apply the rule

6. Click OK

This way, all formula cells in the range stay highlighted.

Highlight cells with formulas using Conditional Formatting. Image by Author.

Option 2: Use Find & Select (temporary)

The Find & Select option is helpful for quick checks. Here’s how to use it:

1. Go to Home > Find & Select > Formulas
2. Excel highlights all formula cells in gray

The highlight disappears as soon as you click another cell.

Highlight cells with formulas using Find & Select. Image by Author.

Final Thoughts 

Now that you know the different ways to show formulas in Excel, you can choose the one that works best for your spreadsheet. Some options are better for quick checks, while others are better for reviews, sharing, or keeping formulas safe.

If you want to learn more, try to understand how formulas support analysis. You can start with our Data Analysis in Excel course to understand the basics, then move on to our Data Analysis with Excel Power Tools skills track to work more efficiently with more complex datasets.