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.
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
- Select entire cells using Ctrl+A.
- Go to Format menu using Ctrl+1.
- Go to the Protection Tab.
- Remove the selection against “Locked” as well as “Hidden” and Click OK button.
- Select the range containing formulas to hide.
- Repeat the above step 1 & 2.
- Select “Locked” and “Hidden” and click OK button.
- 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.
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”.
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.
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”.
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.
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.
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.
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.