Display or hide formulas

You can control the display of formulas in the following ways:

  • Switch between displaying formulas and formula results on a worksheet    This method is a toggle between two different display modes. It does not change the state of the cells.
  • Prevent formulas from displaying in the formula bar by protecting the cells that contain them    This method changes the state of the cells – protected cells cannot be edited.
  • Show hidden formulas by removing the protection from cells    This method changes the state of the cells – unprotected cells can be edited. Make sure this is an acceptable risk, and make a backup copy of the workbook before you proceed.

Switch between displaying formulas and their values on a worksheet

  • Press CTRL + ` (grave accent).

Prevent a formula from displaying in the formula bar

 Note    This procedure also prevents the cells that contain the formula from being edited.

  1. Select the range of cells whose formulas you want to hide. You can also select nonadjacent ranges or the entire sheet.
  2. On the Home tab, in the Cells group, click Format, and then click Format Cells.
  3. In the Format Cells dialog box, on the Protection tab, select the Hidden check box.
  4. Click OK.
  5. On the Review tab, in the Changes group, click Protect Sheet.
  6. Make sure the Protect worksheet and contents of locked cells check box is selected, and then click OK.

Show formulas that were previously hidden by removing protection

If the workbook is shared, unshare it before you remove protection. If the workbook is not shared, you can skip ahead to the procedure to remove protection.

 Note    Unsharing a workbook deletes the workbook’s change history (change history: In a shared workbook, information that is maintained about changes made in past editing sessions. The information includes the name of the person who made each change, when the change was made, and what data was changed.). If you want to keep a copy of this information, print out the History worksheet (History worksheet: A separate worksheet that lists changes being tracked in a shared workbook, including the name of the person who made the change, when and where it was made, what data was deleted or replaced, and how conflicts were resolved.) or copy it to another workbook.

Print or copy the change history of a shared workbook
  1. On the Review tab, click Track Changes, and then click Highlight Changes.
  2. On the Highlight Changes dialog box, select the When checkbox, then select All from the list box.
  3. Clear the Who and Where check boxes.
  4. Select the List changes on a new sheet check box, and then click OK.
  5. Do one or more of the following:
  • To print the History worksheet, click Print Button image.
  • To copy the history to another workbook, select the cells you want to copy, click Copy Button image, switch to another workbook, click where you want the copy to go, and click Paste Button image.

 Note    You may also want to save or print the current version of the workbook, because this history might not apply to later versions. For example, cell locations, including row numbers, in the copied history may no longer be current.

Unshare a workbook
  1. On the Review tab, in the Changes group, click Share Workbook, and then click the Editing tab.
  2. Make sure that you are the only person listed in the Who has this workbook open now box. If necessary, select another listed user and click Remove User.
  3. Clear the Allow changes by more than one user at the same time check box.
  4. When prompted about the effects on other users, click Yes.

Remove protection and unhide formulas

  1. On the Review tab, in the Changes group, do one of the following:
  • Click Unprotect Sheet
  • If you do not see Unprotect Sheet, do one of the following:
    • In Excel 2007: Click Protect Workbook, and then under Restrict Editing, click Protect Structure and Windows (to clear the check box).
    • In Excel 2010: Click Protect Workbook.
  1. Select the range of cells whose formulas you want to unhide.
  2. Right-click the range of cells, and then click Format Cells.
  3. On the Protection tab of the Format Cells dialog box, clear the Hidden check box.
 
 
Applies to:
Excel 2010, Excel 2007