Categories
Interior

How to show formulas in cells and hide formulas completely in excel 2013

How to show formulas in cells and hide formulas completely in excel 2013

An Excel formula is any equation entered into a cell on a Microsoft Excel spreadsheet. These formulas are what make Excel so powerful. Any program can generate a table, and if that’s all you’re using Excel for, you are really missing out. When you use formulas on your spreadsheets, you transform them from boring databases into interactive tools that can provide you with a wealth of valuable information.

Formula Basics

Every Excel formula begins with an equals (=) sign, even if you aren’t doing any math. If you don’t use an equals sign, the information you enter will simply appear at face value. Everything placed after the equals sign is important information that will be used to calculate the final value displayed in the cell. You’ll be able to see the formula as you enter it into the cell, but once you hit “Enter,” the cell will simply display the result of the formula. To see the formula again, select the cell in question and look at the formula bar at the top of the page.

Math Equations

Math equations are the simplest types of Excel formulas. At the most basic level, these use standard operators like the plus sign (+), minus sign (-), backslash (/) and asterisk (*) to add, subtract, divide and multiply respectively. For complex math equations, parentheses go around the part of the equation that should be calculated first. If you’re getting really complex, you can even use multiple sets of parentheses.

Cell References

For many spreadsheets, you won’t want to go back to the original formula to change all the information you’re working with. This is where cell references come in handy. By entering a reference to another cell on the worksheet, you can tell the formula to work its calculation with whatever number is placed in that cell. The formula can then be changed quickly by trying out different numbers in the reference cell. To reference a cell, simply enter the location of the call as designated by its column and row; for example, A1 is the cell in the top left corner of the spreadsheet. To reference a cell on another worksheet within the same workbook, type the name of the worksheet followed by an exclamation point, then the location of the cell. So Sheet1!A1 would refer to the A1 cell on the worksheet titled “Sheet1.” If you want to reference a range of cells, use a colon between the first and last cell of the range. The formula =SUM(A1:A12) will calculate the total sum of all the figures in the range from A1 down to A12.

Additional Formulas

Once you are comfortable with the basics of what a formula is, you can really begin to lasso the power of this tool. Excel is equipped with several additional formula operators that will help you perform complex calculations. The formula =SQRT(A1) will calculate the square root of the referenced cell. The PMT formula uses four key numbers to calculate payments. To use this formula, enter =PMT(percent of interest/number of months to divide yearly interest by, total number of months in loan period, amount of loan). Thus, =PMT(0.07/12,360,150,000) will return the monthly payment for a loan of $150,000 at 7 percent interest paid over a 30-year period. If you reference cells instead of entering actual numbers in this type of formula, you’ll have a simple worksheet that lets you play with all the variables until you get the result you’re looking for.

Lori Kaufman
How to show formulas in cells and hide formulas completely in excel 2013Lori Kaufman
Writer

Lori Kaufman is a technology expert with 25 years of experience. She’s been a senior technical writer, worked as a programmer, and has even run her own multi-location business. Read more.

How to show formulas in cells and hide formulas completely in excel 2013

If you are working on an Excel worksheet with a lot of formulas in it, it may become difficult to follow and keep track of all your formulas. Excel provides a simple way of displaying formulas in the cells in addition to the formula bar.

This feature also displays the dependencies for each formula in the cells (when selected), so you can track the data being used in each calculation. Displaying formulas in cells helps you to find cells containing formulas and to quickly read through all your formulas and check for errors. You can also print the spreadsheet with the formulas in the cells to help check your work.

To display formulas in cells containing them, press the Ctrl + ` (the grave accent key). The formulas in each cell display as shown in the image above. The cells involved in the calculation are bordered in colors that match the cell references in the formula to help you track the data.

You can also click Show Formulas in the Formula Auditing section of the Formulas tab to display formulas in the cells.

How to show formulas in cells and hide formulas completely in excel 2013

Even if you don’t show formulas in the cells, when you click on a cell containing a formula, the formula displays in the formula bar. If you don’t want the formulas visible to users of your spreadsheet, you can hide them and protect the sheet. To do this, select the cells whose formulas you want to hide.

How to show formulas in cells and hide formulas completely in excel 2013

In the Cells section of the Home tab, click Format and select Format Cells from the drop-down menu.

How to show formulas in cells and hide formulas completely in excel 2013

The Format Cells dialog box displays. On the Protection tab, select the Hidden check box. Click OK.

How to show formulas in cells and hide formulas completely in excel 2013

To finish hiding the formulas, you must protect the sheet. Click Format in the Cells section of the Home tab again. This time, select Protect Sheet from the drop-down menu.

How to show formulas in cells and hide formulas completely in excel 2013

On the Protect Sheet dialog box, make sure the Protect worksheet and contents of locked cells check box is selected. Enter a password in the Password to unprotect sheet edit box that will allow you to unprotect the sheet and show the formulas again. In the Allow all users of this worksheet to list box, select the check boxes for the tasks you want to allow the users to perform. Click OK.

How to show formulas in cells and hide formulas completely in excel 2013

Enter your password again in the Reenter password to proceed edit box on the Confirm Password dialog box and click OK.

How to show formulas in cells and hide formulas completely in excel 2013

Now, you’ll notice that when you select a cell containing a formula, the formula bar is empty.

How to show formulas in cells and hide formulas completely in excel 2013

To show the formulas in the formula bar again, click Format in the Cells section of the Home tab and select Unprotect Sheet from the drop-down menu.

How to show formulas in cells and hide formulas completely in excel 2013

Enter your password on the Unprotect Sheet dialog box and click OK.

How to show formulas in cells and hide formulas completely in excel 2013

All your formulas will be visible again when those cells are selected in the worksheet.

  • › The Best Gaming Keyboards of 2021: Be on Top of Your Game
  • › Why Sublime Text Is Great For Writers, Not Just Programmers
  • › What Is a ULED TV, and How Is It Different?
  • › Why Professionals Will Actually Want a 2021 MacBook Pro
  • › How to Add Images to Questions in Google Forms

How to show formulas in cells and hide formulas completely in excel 2013 Lori Kaufman
Lori Kaufman is a technology expert with 25 years of experience. She’s been a senior technical writer, worked as a programmer, and has even run her own multi-location business.
Read Full Bio »

Hey there! Thanks for dropping by Hack Zone! Take a look around and grab the RSS feed to stay updated. See you around!

How to Show Formulas in Cells and Hide Formulas Completely in Excel 2013 !!

If you are working on an Excel worksheet with a lot of formulas in it, it may become difficult to follow and keep track of all your formulas. Excel provides a simple way of displaying formulas in the cells in addition to the formula bar.

This feature also displays the dependencies for each formula in the cells (when selected), so you can track the data being used in each calculation. Displaying formulas in cells helps you to find cells containing formulas and to quickly read through all your formulas and check for errors. You can also print the spreadsheet with the formulas in the cells to help check your work.

To display formulas in cells containing them, press the Ctrl + ` (the grave accent key). The formulas in each cell display as shown in the image above. The cells involved in the calculation are bordered in colors that match the cell references in the formula to help you track the data.

You can also click Show Formulas in the Formula Auditing section of the Formulas tab to display formulas in the cells.

Even if you don’t show formulas in the cells, when you click on a cell containing a formula, the formula displays in the formula bar. If you don’t want the formulas visible to users of your spreadsheet, you can hide them and protect the sheet. To do this, select the cells whose formulas you want to hide.

In the Cells section of the Home tab, click Format and select Format Cells from the drop-down menu.

The Format Cells dialog box displays. On the Protection tab, select the Hidden check box. Click OK.

To finish hiding the formulas, you must protect the sheet. Click Format in the Cells section of the Home tab again. This time, select Protect Sheet from the drop-down menu.

On the Protect Sheet dialog box, make sure the Protect worksheet and contents of locked cells check box is selected. Enter a password in the Password to unprotect sheet edit box that will allow you to unprotect the sheet and show the formulas again. In the Allow all users of this worksheet to list box, select the check boxes for the tasks you want to allow the users to perform. Click OK.

Enter your password again in the Reenter password to proceed edit box on the Confirm Password dialog box and click OK.

Now, you’ll notice that when you select a cell containing a formula, the formula bar is empty.

To show the formulas in the formula bar again, click Format in the Cells section of the Home tab and select Unprotect Sheet from the drop-down menu.

Enter your password on the Unprotect Sheet dialog box and click OK.

All your formulas will be visible again when those cells are selected in the worksheet.

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

Click on Formulas and then click on Show Formulas to switch between displaying formulas and results.

Press CTRL + ` (grave accent).

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

Select the range of cells whose formulas you want to hide. You can also select nonadjacent ranges or the entire sheet.

Click Home > Format > Format Cells.

On the Protection tab, select the Hidden check box.

Click Review > Protect Sheet.

Make sure the Protect worksheet and contents of locked cells check box is selected, and then click OK.

Click the Review tab, and then click Unprotect Sheet. If the Unprotect Sheet button is unavailable, turn off the Shared Workbook feature first.

If you don’t want the formulas hidden when the sheet is protected in the future, right-click the cells, and click Format Cells. On the Protection tab, clear the Hidden check box.

How to show formulas in cells and hide formulas completely in excel 2013

Click on Formulas and then click on Show Formulas to switch between displaying formulas and results.

If you have data in Excel that you don’t want to be visible, but you still need to be able to use it in calculations, there are two ways to hide cells. It’s another case of Office having an official and unofficial hiding option.

Here we’re talking about hiding individual cells or ranges. There are separate options for hiding row, columns or entire worksheets (aka tabs).

Hiding cell formulas – according to the manual

The approved method is part of Excel’s Protection racket feature. It hides cell formulas but NOT the results. That’s handy if you’re sharing a finished workbook.

Select one or more cells, right-click and choose Format Cells … then the Protection tab. Or press Control + 1 shortcut.

How to show formulas in cells and hide formulas completely in excel 2013

Choose the Hidden option.

As the text note on the tab says, marking a cell as ‘Hidden’ doesn’t hide it. ‘Hidden’ means ‘hiding formulas’.

Truly hide the select ‘Protect Sheet’ or ‘Protect Workbook’ on the Review tab. The password is optional.

How to show formulas in cells and hide formulas completely in excel 2013

Once Protected, any ‘Hidden’ cells will hide the formulas.

See the Formula bar is now blank for all ‘Hidden’ cells.

How to show formulas in cells and hide formulas completely in excel 2013

To reveal the formulas again, click Unprotect Sheet.

Unofficial hiding cells with cell formatting

The other hiding option completely blanks out the cell – formula and value.

To do this, just select the cell(s) you want to hide and either:

  • Press Control+1, or
  • Right-click and select Format Cells.

How to show formulas in cells and hide formulas completely in excel 2013

In the Format Cells dialog, we select the Number tab, and select Custom at the bottom of the list. In the Type field, we type three semi-colons (;;;).

How to show formulas in cells and hide formulas completely in excel 2013

The figures in those cells are now invisible, but they can still be used in formulas.

How to show formulas in cells and hide formulas completely in excel 2013

Why Semi-Colons?

You may be wondering why this seemingly random string of characters is used to hide cell contents. Well, it’s not random at all! The semi-colons come from the way that custom number formats are structured in Excel. If you look at the formats already listed in the Type field of the Format Cells dialog, you will see a bunch of strange symbols. We won’t go into the details of how these work in this article, but the important thing to notice is that they all have semi-colons interspersed in them.

How to show formulas in cells and hide formulas completely in excel 2013

What is happening here is that these symbols are telling Excel how to display four different types of data, and the types are separated by semi-colons:

; ; ;

So whatever you put in each of those positions signifies how to display that type of value. So if you want everything in that cell to not be displayed, you simply leave them all blank, leaving you with just the three semi-colons.

Showing Hidden Cell Contents

If you have hidden some cell contents and later decide that you need to view them again, it is very easy to display them. Simply select the cells again and open the Format Cells dialog (Control+1 or right-click + Format Cells). Back on the Number tab again, we just choose the appropriate category for the type of numbers we are displaying (in this case, Currency).

How to show formulas in cells and hide formulas completely in excel 2013

Hiding Text Content

As this method involves setting the format of number displays, it may seem like it can only be used to hide numerical content, but this is not so. You can do exactly the same thing on text cells, and the data will still be hidden.

How to show formulas in cells and hide formulas completely in excel 2013

How to show formulas in cells and hide formulas completely in excel 2013

Hiding isn’t deleting

A reminder that hidden data isn’t deleted. Anyone with access to the worksheet can see the original data. That’s important whenever data privacy is a consideration.

Lori Kaufman
How to show formulas in cells and hide formulas completely in excel 2013Lori Kaufman
Writer

Lori Kaufman is a technology expert with 25 years of experience. She’s been a senior technical writer, worked as a programmer, and has even run her own multi-location business. Read more.

How to show formulas in cells and hide formulas completely in excel 2013

There may be times when you want to hide information in certain cells or hide entire rows or columns in an Excel worksheet. Maybe you have some extra data you reference in other cells that does not need to be visible.

We will show you how to hide cells and rows and columns in your worksheets and then show them again.

Hide Cells

You can’t hide a cell in the sense that it completely disappears until you unhide it. With what would that cell be replaced? Excel can only blank out a cell so that nothing displays in the cell. Select individual cells or multiple cells using the “Shift” and “Ctrl” keys, just like you would when selecting multiple files in Windows Explorer. Right-click on any of the selected cells and select “Format Cells” from the popup menu.

How to show formulas in cells and hide formulas completely in excel 2013

The “Format Cells” dialog box displays. Make sure the “Number” tab is active and select “Custom” in the “Category” list. In the “Type” edit box, enter three semicolons (;) without the parentheses and click “OK”.

NOTE: You might want to note what the “Type” was for each of the selected cells is before you change it so you can change the type of the cells back to what it was to show the content again.

How to show formulas in cells and hide formulas completely in excel 2013

The data in the selected cells is now hidden, but the value or the formula is still in the cell and displays in the “Formula Bar”.

How to show formulas in cells and hide formulas completely in excel 2013

To unhide the content in the cells, follow the same steps listed above, but choose the original number category and type for the cells rather than “Custom” and the three semicolons.

NOTE: If you type anything into cells in which you hid the content, it will automatically be hidden after you press “Enter”. Also, the original value in the hidden cell will be replaced with the new value or formula that you type into the cell.

Hide Rows and Columns

If you have a large worksheet, you might want to hide some rows and columns for data you don’t currently need to view. To hide an entire row, right-click on the row number and select “Hide”.

NOTE: To hide multiple rows, select the rows first by clicking and dragging over the range of rows you want to hide, and then right-click on the selected rows and select “Hide”. You can select non-sequential rows by pressing “Ctrl” as you click on the row numbers for the rows you want to select.

How to show formulas in cells and hide formulas completely in excel 2013

The hidden row numbers are skipped in the row number column and a double line displays in place of the hidden rows.

How to show formulas in cells and hide formulas completely in excel 2013

Hiding columns is a very similar process to hiding rows. Right-click on the column you want to hide, or select multiple column letters first and then right-click on the selected columns. Select “Hide” from the popup menu.

How to show formulas in cells and hide formulas completely in excel 2013

The hidden column letters are skipped in the row number column and a double line displays in place of the hidden rows.

How to show formulas in cells and hide formulas completely in excel 2013

To unhide a row or multiple rows, select the row before the hidden row(s) and the row after the hidden row(s) and right-click on the selection and select “Unhide” from the popup menu.

How to show formulas in cells and hide formulas completely in excel 2013

To unhide a column or multiple columns, select the two columns surrounding the hidden column(s), right-click on the selection, and select “Unhide” from the popup menu.

How to show formulas in cells and hide formulas completely in excel 2013

If you have a large spreadsheet and you don’t want to hide any cells, rows, or columns, you can freeze rows and columns so any headings you set up don’t scroll when you scroll through your data.

  • › The Best Gaming Keyboards of 2021: Be on Top of Your Game
  • › Why Sublime Text Is Great For Writers, Not Just Programmers
  • › What Is a ULED TV, and How Is It Different?
  • › Why Professionals Will Actually Want a 2021 MacBook Pro
  • › How to Add Images to Questions in Google Forms

How to show formulas in cells and hide formulas completely in excel 2013 Lori Kaufman
Lori Kaufman is a technology expert with 25 years of experience. She’s been a senior technical writer, worked as a programmer, and has even run her own multi-location business.
Read Full Bio »

By default, Excel shows the results of formulas. To show the formulas instead of their results, press CTRL + ` (you can find this key above the tab key).

1. When you select a cell, Excel shows the formula of the cell in the formula bar.

How to show formulas in cells and hide formulas completely in excel 2013

2. To display all formulas, in all cells, press CTRL + ` (you can find this key above the tab key).

How to show formulas in cells and hide formulas completely in excel 2013

How to show formulas in cells and hide formulas completely in excel 2013

Note: as you can see, Excel highlights all cells that are referenced by a formula.

4. To hide all formulas, press CTRL + ` again.

5. If you can’t find the grave accent (`) on your keyboard, on the Formulas tab, in the Formula Auditing group, click Show Formulas.

Note: to hide all formulas, click Show Formulas again. Visit our page about formula auditing in Excel to learn more about tracing precedents, tracing dependents, error checking, etc.

This Excel tutorial explains how to hide formulas from appearing in the edit bar in Excel 2016 (with screenshots and step-by-step instructions).

See solution in other versions of Excel :

Question: In Microsoft Excel 2016, I have formulas in a worksheet that I don’t want to be visible when that cell is selected.

Is there a way I can click on the cell and not see the formula up top in the edit bar?

Answer: As you can see, currently the formulas are visible. When you select cell A3, you can see the formula in the formula bar.

How to show formulas in cells and hide formulas completely in excel 2013

To hide the formulas, first you’ll need to un-protect all of the cells on your sheet. To do this, select all of the rows and columns in your sheet. Right-click on then select “Format Cells” from the popup menu.

How to show formulas in cells and hide formulas completely in excel 2013

When the Format Cells window appears, select the Protection tab. Uncheck the “Locked” checkbox. Click on the OK button.

How to show formulas in cells and hide formulas completely in excel 2013

Next, select the cell(s) that you wish to hide the formulas for. Right-click and then select “Format Cells” from the popup menu.

How to show formulas in cells and hide formulas completely in excel 2013

When the Format Cells window appears, select the Protection tab. Check the “Hidden” checkbox. Click the OK button.

How to show formulas in cells and hide formulas completely in excel 2013

For the formulas to be hidden, you must also protect the worksheet. To do this, select the Review tab in the toolbar at the top of the screen. Then click on Protect Sheet button.

How to show formulas in cells and hide formulas completely in excel 2013

A “Protect Sheet” window will appear. You may enter a password to protect the sheet if you wish. The password is optional. Click on the OK button.

How to show formulas in cells and hide formulas completely in excel 2013

Now when you view your spreadsheet, the formula in cell A3 will no longer appear in the edit bar when the cell is selected.

When you delete a formula, the result of the formula is also deleted. If you don’t want to delete the value, you can instead remove the formula only.

Delete a formula

Select the cell or range of cells that contain the formula.

Delete a formula but keep the results

To do this, you copy the formula and then paste in the same cell by using the Paste Values option.

Select the cell or range of cells that contains the formula.
If the formula is an array formula, you must first select all cells in the range of cells that contains the array formula:

Click a cell in the array formula.

On the Home tab, in the Editing group, click Find & Select, and then click Go To.

Click Special.

Click Current array.

On the Home tab, in the Clipboard group, click Copy .

On the Home tab, in the Clipboard group, click the arrow below Paste , and then click Paste Values.

Delete an array formula

To delete an array formula, make sure you select all cells in the range of cells that contains the array formula. To do that:

Click a cell in the array formula.

On the Home tab, in the Editing group, click Find & Select, and then click Go To.

Click Special.

Click Current array.

Delete a formula but keep the results

Select the cell or range of cells that contains the formula.

Click Home > Copy (or press Ctrl + C).

Click Home > arrow below Paste > Paste Values.

How to show formulas in cells and hide formulas completely in excel 2013

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.