How to Hide Formulas in Google Sheets Similar to Excel

13
961

By default, at the time of writing this Google Sheet tutorial, there is no solution to hide formulas in Google Sheets similar to Excel. But there is a tricky way to do it with a workaround.

I’m not talking about hiding or unhiding formulas using the keyboard shortcuts “Ctrl+~”. The above is available both in Excel as well as in Google Sheets. This is only useful to reveal the formula instead of formula results in a spreadsheet. I’m talking about the Excel > Format Cell > Protection > Hidden. This will fully hide the view of formula from a cell or a range of cells. Even if someone select the cell they can’t see the formula in the formula bar. We will come back to this topic. Before that I should explain the usefulness of the “Ctrl+~” key.

When we talk about the awesome “Ctrl+~” shortcut key, it is very useful to copy and paste formula from Excel to Google Sheet or vice a versa. When you copy formulas in this way, you can paste the formula instead of value. I mean when you copy formula between two different spreadsheet applications, first reveal the formula using the above short cut key, then copy and paste.

Spreadsheets

Before going to tell you how to hide formulas in Google Sheets in the real sense, let me explain the Excel steps first. It will be useful for you at some point of time in the future and also to understand what I meant by hiding formulas.

Hide Active Cells’ Formulas Appearing on Formula Bar – Excel

  1. Select entire cells using Ctrl+A.
  2. Go to Format menu using Ctrl+1.
  3. Go to the Protection Tab.
  4. Remove the selection against “Locked” as well as “Hidden” and Click OK button.
  5. Select the range containing formulas to hide.
  6. Repeat the above step 1 & 2.
  7. Select “Locked” and “Hidden” and click OK button.
  8. Go to Review Tab, Changes Group, protect sheet button. You can use “Alt+R+P+S” key or “Alt+H+O+P” to access it. Default settings are OK. Type a password and lock the sheet.

This will hide the formula from showing on the formula bar while selecting cells in Excel.

Excel Alternative to Hide Formulas in Google Sheets Formula Bar

Hiding formulas similar to the above sheet protection is not available in Google Sheets. You can of course protect sheets. But that is only limited to protect cells from editing. No option to hide formulas so far.

The alternative to hide formulas in Google sheet is to use the IMPORTRANGE function.

How to Use IMPORTRANGE Function to Hide Formulas in Google Sheets

In this workaround you should maintain two worksheets. One the normal Google Sheet with hidden formula and the second spreadsheet file to store your formulas.

See the detailed example below.

I have two spreadsheets – “Test Hide Formulas-1” and “Formula Export”.

The “Test Hide Formulas-1” is the master file. On this file I want to hide formulas in few cells. I’ve some conditional formulas in the yellow highlighted cells on this file. I don’t want to disclose the formula with anyone who has permission to view this Google Sheet file.

In a nut shell, what we are going to do is, we will shift this formulas to a new file, then delete the formulas here and apply Google Sheets IMPORTRANGE function to import the shifted formula result.

Hide Formulas in Google Sheets - File 1

In detail: To hide the formulas in the yellow highlighted cells I did a trick. I created a new file and named it as “Formula Export”. Then I made this “Formula Export” file Private.

How to Make a Google Sheet File Private?

To make your Google Sheet File Private, on Google Drive, right click on the file and select “Share”.

Now under access change the Link Sharing to “Off-Specific people”.

make file private in google sheets

Open this “Formula Export” file then copy and paste the entire content from the file “Test Hide Formulas-1” in it. Instead of yellow colour highlight, here I used Blue colour. This file is now a copy of our master file.

hide formula using function is Google Sheets

Now go to our first file, i.e. the master file, “Test Hide Formulas-1”. Remove the formulas in the Yellow highlighted cells and apply a Red colour in the cell as below. Colouring only to explain you where to apply what.

Here in the Red coloured cell use the IMPORTRANGE function to call the formulas from the second file “Formula Export”.

google sheet importrange function tips

You can apply the IMPORTRANGE function as below.

Learn Google Sheet IMPORTRANGE function

Syntax: IMPORTRANGE(spreadsheet_key, range_string)

In this formula; spreadsheet_key  is the URL of the spreadsheet from where data to be imported and that URL to be put inside double quotes.

Now range_string . It’s the range to be imported that also inside double quotes.

As per our example our formula will be look like as below.

=importrange(“https://docs.google.com/spreadsheets/d/1MoV7fwyyinbVvyEOTD2e4zX2R9HPkojhMPIzg8MuZTw/edit#gid=0”,“Sheet1!E2:G4”)

Here the URL is the URL of the file “Formula Export”. The second part in this formula is the range i.e., SHEET1!E2:G4. Here, SHEET1 is the sheet name in the file “Formula Export” and E2:G4 is the range where the formula resides.

Once you applied IMPORTRANGE function, the result may not appear instantly. You may see an error like this.

allow access to import range

Click Allow access. Now in the master file you have successfully imported the formula results. Now you can share this master file “Test Hide Formulas-1” to the people you want.

Those who have access to this file can only see the IMPORTRANGE function instead of the other formulas. They won’t see what formulas you applied to get the result. They can’t open the URL in the IMPORTRANGE to see your hidden formulas as that file is already set to private.

Conclusion:

I know some of you will raise your eyebrows and ask me why should we hide formulas? Honestly I don’t know. I simply liked to send files, when I was using Excel, to my clients and consultants after hiding the formulas I had used. I don’t know why. I’m sure there may be people with my similar interest.

13 COMMENTS

  1. I tried to do this but was not successful. Are you still able to do this? I could’ve easily done something wrong as I’m not proficient at working with Google sheets.

    • Hi Mike,

      I have two sections on this post. One is dealing with Excel and other with Google Sheets. You may please check the steps under the title “Excel Alternative to Hide Formulas in Google Sheets Formula Bar”. It will definitely work.

      Regards,

  2. I created a spreadsheet with range A1:G9 and named it “Master.” Then I clicked on file/make a copy, and named the copy “Master Copy.”,,, I right clicked on “Master Copy” in Google drive and it was already set to off-specific people.

    I go back to “Master Copy” and delete range E5:G9. I replace that range with =IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1OwfytEILDPaeqImRzf1BVY4uwYRZeBararXPOw9M6SM/edit#gid=xxxxxxxxx”,”SHEET1!E5:G9″)

    It gives me #REF! In cell E5. In the IMPORTRANGE function, I used the nine numbers corresponding to my gid=xxxxxxxxx. I assumed I was not supposed to put gid=0. I was not sure where you got SHEET1 from.
    I keep trying to figure this out but I’m not sure where I am making a mistake. Can you please help?

    • Hi,

      You did everything correct, You will of course get that error!

      Just point your cursor on the error. You can see a message popup to “Allow Access”
      Click on it and allow access. That’s all.

      Allow Acees - Necessary Permission to Importrange Function

      I should have mention it in the tutorial. I will add that part to the tutorial too.
      Cheers!

  3. Thanks for your reply. I just tried it again with the same IMPORTRANGE function and it gives me this message — Error Cannot find range or sheet for imported range. I have seen the allow access message but when I clicked on it, it didn’t work. I currently have the privacy set so only I can access Master1. I’m not sure what I could be doing wrong. Any ideas?

    • Hi,
      It works like this.

      You have one file named as “File A”. You want to hide formula in this.
      Make copy of file A. We can name this as “Copy of File A”. Set this file Private.

      Come back to File A and delete the formulas you want to hide. Then use import range function to import the content from “Copy of File A”. Inside the importrange function use the entire URL of “Copy of File A” as it is and use the sheet name and range. All within quotes as per our tutorial.

      If you get that error means, your importrange formula is wrong.

      Please cross check.

      Thanks.

  4. I used this and it still did not work: =IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1OwfytEILDPaeqImRzf1BVY4uwYRZeBararXPOw9M6SM/edit#gid=xxxxxxxxx”,”Sheet1!E5:G9″).

    I wasn’t sure where to find the name of the sheet, so I used Sheet1. It looks like it is not working for other people.

  5. I was finally able to get IMPORTRANGE to fetch data. However, my goal was to let others edit 2 other cells on the spreadsheet. It seems this won’t work. The cells that contain IMPORTRANGE will not work. The values stay the same.

    • Hi Mike,

      I can understand.

      IMPORTRANGE clarification

      I hope this can solve your issue. But the problem is, it can slowdown the process. That is why, I limited my tutorial to that extend.

      See that there is now two importrange formulas, one in each file.

      Thanks,
      Prashanth

LEAVE A REPLY

Please enter your comment!
Please enter your name here