How to Hide Formulas in Google Sheets Similar to Excel

2
294

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.

Hide Formulas in Google Sheets - File 1

To hide the formulas in the highlighted cells i did a trick. I created a new file and named it as “Formula Export”. Then I made this 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 file and copy paste the entire content from file “Test Hide Formulas-1”. Now If you want you can remove the content of all the cells other than the highlighted one in the second file. I changed the highlighted colour to Blue here in the second Google Sheet file. I didn’t delete any content. 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”)

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

 

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

2 COMMENTS

LEAVE A REPLY

Please enter your comment!
Please enter your name here