Show or Hide recipe in Excel making use of a keyboard Shortcut, switch or Formula

by Avantix finding out Team | to update February 25, 2020

You can easily present or hide recipe in a variety of ways in Microsoft Excel. You deserve to use a keyboard shortcut, click a button and also even use a formula to display formulas. Although you have the right to double-click a cell or press F2 to present the formula in one cell, the very first two techniques will show formulas in every cells. V the 3rd method, you can view formulas for details cells.

You are watching: You can press the ctrl+accent mark(`) keys to display the formulas in a worksheet.

Recommended article: How come Delete blank Rows in Excel (5 Ways)

Do you desire to learn an ext about Excel? Check the end our digital classroom or live classroom Excel process >

Showing formulas using a key-board shortcut

You can present or hide formulas making use of a key-board shortcut. Press Ctrl + tilde (~) or Ctrl + accent grave (`) to present or hide formulas. The tilde / accent grave crucial appears on the peak left of most keyboards below the Esc key. This shortcut works in all versions of Excel.

Showing formulas making use of a button

An easy way to present or hide formulas in Excel is to use the present Formulas button.

To display formulas using a button:

Click the formulas tab in the Ribbon.In the Formula Auditing group, click present Formulas. The worksheet will certainly now display screen with formulas instead of values.Click display Formulas again to hide the formulas.

Below is the formulas tab in the Ribbon:


Showing formulas utilizing the FORMULATEXT function

You can also use the FORMULATEXT role in a cell to screen the formula from one more cell as a message string. This is really useful if you desire to audit a worksheet and view both values and formulas. The FORMULATEXT duty is accessible in Excel 2013 and also later versions.

The syntax for the FORMULATEXT function is =FORMULATEXT(reference) where referral is a cabinet or a variety of cells.

The FORMULATEXT function will return an #N/A error if:

The formula refers to a cell the does no contain a formula.The formula ad to one more workbook however the workbook is no open.

In the complying with example, us have continuous formulas in pillar C and in obelisk D, we’ve supplied the FORMULATEXT function:


So in D2, the formula would certainly be =FORMULATEXT(C2).

Bonus: Hiding formulas and also locking cells

There is one more method that you can use if you want to really hide formulas and prevent others from unhiding them. You’ll need to select the covert option in the style Cells dialog crate for details cells and also then safeguard the worksheet.

The first step is to hide the formulas:

Select the cells through the recipe you great to hide.Right-click the selected cell(s) and choose format Cells or press Ctrl + 1. The format Cells dialog appears.Click the defense tab.Check Hidden. If you desire to protect the cell(s) as well, for sure Locked is checked.Click OK. Nothing will show up to occur until you defend the sheet.

Below is the format Cells dialog box:


The 2nd step is to defend the worksheet:

Display the worksheet v the cells that have actually been formatted as hidden in the style Cells dialog box.Click the evaluation tab in the Ribbon.In the changes group, click safeguard Sheet. A dialog crate appears.Check or uncheck the desired options (you would generally leave the very first two checked).Enter a password (you will require to collection a password or everyone will have the ability to unprotect the sheet). Passwords are case sensitive and also you should keep a copy of your passwords what else.Enter the password again.Click OK. Every formulas friend have marked as concealed will no longer appear in the Formula Bar.

Below is the protect Sheet dialog box:


To unhide formulas and unprotect the worksheet:

Display the preferred worksheet.Click the testimonial tab in the Ribbon and click Unprotect Sheet.Enter the proper password.Click OK.

The an initial two techniques are used many often however the critical two provide some exciting alternatives.

See more: Is 5X114.3 The Same As 5X115 Wheel Fit On A 5X114, 5X115 Vs 5X114

Subscribe come get much more articles prefer this one

Did you uncover this article helpful? If girlfriend would like to receive new articles, sign up with our email list.

More resources

How to Fill blank Cells in Excel through a value from a cell Above

How to use Flash fill in Excel to Clean or Extract Data (Beginner’s Guide)

Automatically amount Rows and also Columns using the Quick evaluation Tool

Related courses

Microsoft Excel: intermediary / Advanced

Microsoft Excel: Data evaluation with Functions, Dashboards and What-If evaluation Tools