How to Hide Formulas in Excel

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:

Show Formulas Option in Excel

  • 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.

Paste Special In Excel To Paste Only Values

  • 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’.
Format_cells_hidden_option
  • 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’.
How to Hide Formulas in Excel
  • 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.

About Ankit Kaul

Ankit is the founder of Excel Trick. He is tech Geek who loves to sit in front of his square headed girlfriend (his PC) all day long. :D. Ankit has a strong passion for learning Microsoft Excel. His only aim is to turn you guys into 'Excel Geeks'.
You can find Ankit on FaceBook or Google Plus

Comments

  1. Anil Dutt Bhargava says:

    thanks for it

  2. 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.

  3. yasmeen says:

    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 :)

Speak Your Mind

*

Privacy Policy | Disclaimer | About