How to Hide Formulas in Google Sheets From Other Users

If you don’t want viewers of your sheet seeing how you generate your data, you’ll need to hide your formulas. There are 2 ways to do just that. Which you choose will depend on how technical your users are and exactly what you want to accomplish.

Hiding the formula bar is very simple (View > Show > Formula bar), but it’s very easy to show it again, and it doesn’t really hide the formulas.

If your users need read-only or viewer access to your sheet, the simplest way to hide the formulae is to keep them in a hidden column and place it within a protected range. However, unless you set your sharing permissions correctly, savvy users will still be able to make a copy of your sheet and un-hide your formula column. More on that in a minute.

The most complete and secure way to hide formulas, and still allow users access to edit the sheet, is to keep your calculations in a separate sheet with restricted access and import the results using the IMPORTRANGE function.

If you want to follow along with our explanations, take a copy of our hidden Formula example sheet for this article.

Use IMPORTRANGE and a second sheet to hide formulas

If you can’t restrict the permissions on your sheet, your best option for hiding formulas from your users is to keep them inside another restricted sheet. This is secure (unless you get the sharing permissions wrong) and still allows your users to edit or comment on the sheet with the data.

This technique will require you to re-arrange your sheets so that your formulae are in a separate sheet to the one you will allow your users to access. The simplest way to do that is to make a complete copy of your sheet (the one with your formulae in it). We will use this copy as the restricted sheet. Once you have copied your sheet using File > Make a Copy, you then need to restrict access to it.

Here’s how to restrict access to the sheet with your formulas:

1. Click the File > Share > Share with others option in the menu.
2. Ensure that ‘People with access’ only lists people who you want to see your formula sheet
3. Ensure that ‘General access‘ is ‘Restricted‘ so that only people with access can open the sheet.

So now you should have a sheet that only you can access and contains all your formulas. We now need to use IMPORTRANGE in your shared sheet to replace the cells containing formulae you want to hide with data from your restricted sheet.

IMPORTRANGE(spreadsheet_url, range_string)

This function takes 2 parameters. The URL of a spreadsheet to get data from, and a string with the range of data to import.

The simplest way to show its use is with an example. Our restricted sheet has the formula =1+2+3+4+5 in cell A1. We want to display the result in our shared sheet. First, we need to grab the URL of the restricted sheet from the browser address bar. It will look like this:

https://docs.google.com/spreadsheets/d/1KM_7t3ik0VTT_OurxNMkH9vusRBduRXOvabLEIe3pYw/edit#gid=0

Then remove the edit# part after the final forward slash. So it looks like this:

https://docs.google.com/spreadsheets/d/1KM_7t3ik0VTT_OurxNMkH9vusRBduRXOvabLEIe3pYw/

This is our spreadsheet_url for the IMPORTRANGE function. The range_string parameter is simply the reference of the cells we want to import, in this case A1. That gives us:

IMPORTRANGE("https://docs.google.com/spreadsheets/d/1KM_7t3ik0VTT_OurxNMkH9vusRBduRXOvabLEIe3pYw/","A1")

That example imports the value of just 1 cell. It is also possible to import entire columns and rows by specifying different range_strings. Here are some examples:

• "A:D" will import columns A, B, C, D
• "A1:A5" will import cells A1, A2, A3, A4, A5
• "Sheet2:A3:A" will import all of column A from row 3 onwards in Sheet2

Anyone can get to the URL of the sheet containing the formulas by looking at your IMPORTRANGE formula. But we restricted the sheet containing the formulas to your access only. So they won’t be able to open it!

Hopefully, you can now see how you can use this technique to keep your calculations separate from what users can see.

Use a protected range and hidden column

If your users only need read-only access to your sheet, you can restrict access to the formulas by putting them in a hidden column, and protecting the range containing that column. This only works properly with read-only access sheets and permissions set correctly. If you do not set the permissions correctly, your users can simply make a copy of the sheet (File > Make a copy) and remove the protection from your hidden column.

Here’s how to use a protected range to lock a hidden column:

1. Copy your formulas into a new column that you will hide.
2. Change your original formulas to reference the data in the new column instead of using the formulas.
3. Select your column by clicking the column header.
4. Click ‘Data > Protect sheets and ranges‘ on the menu.
5. Enter a description of your range, i.e. ‘Hidden Formulas!’.
6. As you selected your column before opening the Protect sheets and ranges toolbar, it should already be in the Range box. If not, enter the range of your formula column, i.e. D:D for column D.
7. Click the Set permissions button.
8. In the ‘Range editing permissions‘ box check ‘Restrict who can edit this range‘, and select only you.
9. Hide the formula column – right click the column header and click select ‘Hide column’.

Now we’ve protected and hidden your column, you need to ensure that you secure your sheet correctly so that users cannot remove your protected range, or un-hide your column by making a copy of your sheet.

1. Click the File > Share > Share with others option in the menu.
2. Ensure that ‘People with access’ only lists people as ‘Viewers‘ unless you want them to access your formulas
3. Ensure that if you have ‘General access‘ enabled, you also set it to ‘Viewer‘.
4. Click the settings icon on (cog, top right)
5. In ‘Settings for your sheet’, ensure that ‘Viewers and commenters can see the option to download, print and copy’ is un-ticked.

Now, you can give the sharing URL of your sheet to anyone and your formulas are secure.

Hiding the formula bar

It is tempting to think that simply using the menu option ‘View > Show > Formula bar‘ to hide the formula bar will stop people from being able to see your formulas. That, unfortunately, isn’t the case. Even with the formula bar hidden, simply double clicking into any cell, or hitting Enter ↵ will open the cell in edit mode, and reveal the formula.

Not to mention the fact that any user of any permission level can simply use the same option to show the formula bar again.

Hiding the formula bar is simply an option to give you more screen space!

View show formulae

You may also have noticed that the ‘View > Show‘ menu contains a Formulae option with the following keyboard shortcut: Ctrl + ' (⌘ + ' on Mac). This will not help you create hidden formulas. All this option does is make ALL the cells in the sheet show formulas instead of their values.

This is a handy option when you’re working on or debugging a sheet. But it won’t help you keep your formulas a secret!