How to Hide Formula in Excel

To hide formulas follow these simple steps:

Step1: Un-Protect Worksheet

At this point when you click on a cell, it shows you the entire formula in the formula bar.



  • First of all, you need to make sure that your worksheet in unprotected.
  • For this, go to review tab and in protect the group.
  • If you find a “Unprotect Worksheet” button there, that means your worksheet is protected and you need to unprotect it.


  • Click on that button to un protect it.
If you have “Protect Sheet” button there that means your worksheet is unprotected. In that case, skip above steps and start from here.

Step2: Activate Hide Formula Option

  • Now, select the cell or range of cell, or select all the non-contiguous cells where you want to hide formulas.
  • After that, right-click and select format cells option.
   
  •  From format cells option, go to protection tab.
  • In protection tab, you have two check boxes to mark, first locked and second is hidden.
  • Mark on both of them and click OK.

 These both options which you have checked solve your both of the problems which we have discussed above.

Locked option restricts editing of a cell and hidden option hides a formula from the formula bar.

Now the final step is to protect the worksheet.

Step3: Protect Worksheet

  •  Right click on worksheet tab and then click on “Protect Sheet”.
  • Here you need to enter a password. So enter a password (Twice). 
  •     And in the end, click OK.
 
Now all the cell which you had selected got formula hidden from the formula bar.
 
 

 If you send this file to someone they will not able to see the formula or edit it unless this worksheet is unprotected.

How do I find hidden formulas?

Once you protect a file using above steps even you can’t see formula until you unprotect worksheet.

But if you just want to find them from a protected worksheet where they are, you can use “Go To Special” option.

Go to Home Tab -> Editing -> Find & Select -> Go to Special.

Select “Formulas” option and it’s all four sub option (Numbers, Text, Logicals, Errors) and click OK.

Once you click OK, it Excel will select all the cell from worksheet where you have formulas.

Comments

Post a Comment

Popular posts from this blog

How to Automatically Add Serial Numbers in Excel

Image
Serial numbers are like salt. You never feel their presence but their absence makes your data tasteless. Yes, they are important because, with a serial number, you can have a unique identity to each entry of your data. But the sad news is, adding them manually is a pain. It’s really hard to add a number in every row one after another.  The good news is, there are some ways which we can use to automatically add serial numbers in a column. 5 Ways to Insert Serial Number Column in Excel  And today, in this post, I’d like to share with you 5-Quick Methods. You can use any of these methods which you think is perfect for you. These methods can generate numbers up to a specific number or can add a running column of numbers. So without further ado, here are the methods. #1. Row Numbers as Serial Numbers. By default, in a spreadsheet, you have row numbers which act as a serial number for the date.If you don’t want to use serial numbers for filtering or any other purpose then
Read more

Delete Blank Rows in Excel

Image
Using Go To Special Technique to Delete Blank Rows in Excel Suppose you have a dataset as shown below: The above data has three blank rows in it.Here are the steps to quickly delete blank rows from this dataset.  Select the entire dataset (A1:C13 in this case).Press the F5 key It will open the Go To dialog box. You can get this dialog box from Home –> Editing –> Find and Select –> Go To. In the Go To dialog box, click on the Special button. This will open the Go To Special dialog box. In the Go To Special dialog box, select Blanks. Click Ok. This would select all the blank cells in the dataset.  Now that you have all the blank rows selected, here are the steps to delete these rows: Right-click on any of the selected cells and select Delete. In the Delete dialog box, select ‘Entire row’.   Click OK. This would instantly delete all the blank rows in the dataset. Note tha
Read more