Using formulas in excel can make your day to day tasks easier. Excel provides formulas for just about anything. But if you are sharing your spreadsheets with other people then it looks quite rude to write formulas as it is.

All the formulas and the calculation stuff should be hidden from the viewers. The best way to do this is to hide your formulas in excel sheet.

Today I will be sharing few tips on how to hide formulas in Excel.

### 1. Toggling the ‘Show Formulas’ option in Excel:

This method is simplest of all. To toggle ‘Show Formulas’ option in excel simply follow the below steps:

- With an excel sheet opened, navigate to the ‘Formulas’ tab.
- There you will see an option ‘Show Formulas’, clicking once on that button will reveal all the formulas and clicking twice will hide the formulas.
- Instead of performing step 2 you can simply hit “ Ctrl + ~ “ keys on keyboard, pressing the keys once will show all the formulas and pressing them again will make the formulas hidden.

As you can see that this method is not completely fool-proof because the formula is still visible in the top formula bar. It only helps to hide your formulas from showing up inside the cells.

### 2. Replacing the formula with Values:

This is an easy way to get rid of all your formulas at once. Here we are going to replace the formula with the value that it has resulted into. This will remove all your formulas and will replace them with corresponding values, use this method only if you don’t need the formulas again.

- Select the range where you have applied the formula.
- Now right click and select the ‘Paste Special’ option from the menu.
- After the paste special window gets opened select the radio button ‘Values’ and click ‘OK’.
- This will replace all the formulas with their values.

This method is not suitable if you might need the formula again in near future. In such cases the next method comes quite handy.

### 3. Hiding the formula by protecting the Excel sheet:

If you want to hide your formulas temporarily and use them again at your wish. Then you should try hiding formulas by protecting the worksheet. In this method we will first hide the cell formulas and then lock the complete excel sheet. Follow below steps to do this:

- Firstly select all the cells for which you have applied the formula.
- After this, right click and select the option ‘Format Cells’.
- Next, in the ‘Format Cells’ window navigate to the protection tab.
- In protection tab, check the ‘Hidden’ checkbox and click ‘OK’.

- One thing that I want to make clear is that just checking the hidden checkbox can’t do anything until you have protected the excel sheet.
- To protect the excel sheet navigate to the ‘Review’ tab, select the option ‘Protect Sheet’.
- After this the system will ask you to enter your password, after entering the password click ‘OK’.

- Then re-enter the Password and all your excel formulas are hidden.

To view you formulas again simply visit the ‘Review tab’ again and unprotect the sheet. After this uncheck the hidden option in the ‘Protection’ tab of format cells.

So, this was all about how to hide formulas in excel. If you know any other trick then please do share them with us.

thanks for it

Its my pleasure Anil.

Hello there Ankit,

Can you please assist to hide a formula in the formula bar yet still doing the calculations without protecting the sheet.

I still be able to use the sheet containing formulas yet other users not to see the formulas other then end result only.

Awaiting yours,

Thanks

Hi Varma,

There is no other option to hide a formula in formula bar without protecting the sheet.

But, as a workaround you can try to hide the formula bar itself.

To do this navigate to “View” Tab in the top ribbon and then uncheck the “Formula bar” option.

Hi Ankit,

I want to hide the formulas in the cell,but when it comes to display of results above in ‘Formula bar’,this is showing NULL,why is that? Ex:If i am applying vlookup, i don want to see in both Cell and Formula bar as “=VLOOKUP(C6,$B$6:$B$12,1,FALSE) ” rather i want to see the result in Cell also in Formula bar too.

By Protecting the sheet, we are able to see the particular Cell with required result but not in the formula bar.Could you please let me know how can i get it in this way?

Yasmeen you can use the Method-2 for this.

By replacing a formula with its value you will be able to see the value in the formula bar also.

Hope it helps