Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to show or hide formulas in cells of specified range/active sheet/all sheets in Excel?

Sometime we may need to show formulas in cells instead of their calculated results. Here we will provide you with some methods to display formulas in cells.

Show or hide formulas in cells of active worksheet by setting Excel options

Show or hide formulas in cells of all worksheets or active worksheet

Show or hide formulas in cells of specified range

Remove formulas but keep calculated values/numbers from selected cells in Excel

One click to show or hide formulas in specified cells in Excel

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial


By default, it will display the formulas' calculated results in Microsoft Excel cells. We can only view formulas in the Formulas Bars when we select specific cell. It is not easy to view all formulas in the whole worksheet. Actually, we can modify the viewing setting in Excel Options dialog box, and show all formulas in cells:

1. Click the File > Options in Excel 2010/2013, or click Office Button > Excel Options in Excel 2007;

2. Now you get into the Excel Option dialog box. Please select the Advanced in the left bar, and then go to the Display options for this worksheet section and check the Show formulas in cells instead of their calculated results option. See screenshot:

Note: For hiding formulas from the active worksheet, please uncheck the Show formulas in cells instead of their calculated results option.

3. Click OK button to save the settings.

And then it will show all formulas in cells instead of their calculated results in active worksheet at once.


Kutools for Excel will help us easily toggle between viewing formulas' calculated results in cells and displaying formulas in cells with View Options tool.

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Click Kutools > Show / Hide > View Options to open the View Options dialog box.

2. In the opening View Options dialog box, please check the Formulas option (or uncheck the Formulas option for hiding formulas) firstly, next click the Apply to all sheets button, and at last click the Ok button. See screenshot:

Note: If you want to show (or hide) formulas only in active worksheet, please check (or uncheck) the Formulas option and then click the Ok button.

And then you will see all formulas in all worksheets (or active worksheets if you didn't click the Apply to all sheets button) are displaying automatically.

The View Options utility of Kutools for Excel makes it possible to show or hide most of Microsoft Excel settings quickly, such as Inner Tabs, Formula Bar, Status Bar, Windows in Taskbar, Gridlines, Page breaks, Zeros Values, Vertical scroll bar, Horizontal scroll bar, Sheet tabs, … etc. It will save your time in looking for these setting when you need to show or hide them.

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


Both above methods will show all formulas in current worksheet, and change the column width automatically in Excel. But sometimes you may just want to show formulas in specified range. In this condition, you can try Kutools for Excel's Convert Formula to Text utility.

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

Select the range in which you will show formulas in cells, and click the Kutools > Content > Convert Formula to Text.

Note: Select the text of formulas, and click Kutools > Content > Convert Text to Formula to hide the formula and show the calculated results.

Then you will see all formulas in the selected range are shown immediately.

Note: If you want to show the calculated results instead of formulas again, just select the range you will convert, and then click the Kutools > Content > Convert Text to Formula.

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


In this Video, the Kutools tab and the Kutools Plus tab are added by Kutools for Excel. If need it, please click here to have a 60-day free trial without limitation!


One click to remove formulas but keep values/numbers from selected cells in Excel

When applying formulas in Excel, in general the calculated result displays in the cell, while the formula shows in the formula bar. Now, Kutools for Excel's To Actual utility can help Excel users to quickly remove all formulas but keep calculated results from the specified cells in Excel with only one click. Full Feature Free Trial 60-day!

ad to actual 4


Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for you.

  • Designed for 1500+ work scenarios, helps you solve 80% Excel problems.
  • Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  • Being used by 110,000 elites and 300+ well-known companies.

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    dash · 4 years ago
    how to change everyday different color from row A1:A2...BY CONDITIONAL FORMATTING
  • To post as a guest, your comment is unpublished.
    abc · 5 years ago
    Sir
    i have to 100 (one hundred) how write formula
  • To post as a guest, your comment is unpublished.
    mike s · 5 years ago
    Thanks that was perfect