How to Hide Columns from Certain Users in Google Sheets

Google Sheets is a shared platform, so naturally, there might be certain information that you might wish to hide from other users who are sharing the worksheet. Unfortunately, given its online and shareable nature, it’s not yet possible to completely hide columns from users who share the same worksheet.

There are, however, some ways around this. In this tutorial, we will share some neat hacks which can help you work around the Google Sheets system and hide columns from certain users in Google Sheets.

We will use the following dataset to demonstrate all the techniques in this tutorial:

google sheets hide columns from certain users

 

Temporarily Hiding Columns in Google Sheets

In general, most Google Sheets users like to hide columns away from view to reduce distractions, or get a better view of other data columns.

Here’s how it’s done:

  1. Select the headers of the columns you want to hide.
  2. Right-click on your selection.
  3. Click on ‘Hide column(s)’ from the context menu that appears.
Temporarily Hiding Columns in Google Sheets

This will hide your selected columns, as shown below. You should see arrows next to the two columns before and after the hidden columns:

Notice in the above screenshot, the ‘Location’ column is now hidden.

Users with whom your worksheet is shared will also find the column(s) hidden.

However, these columns are only temporarily hidden. This means anyone (including other users who have access to the worksheet) can easily click on one of the arrows before or after hidden columns to unhide them.

Note: Hidden columns do not get printed. They also don’t appear in the worksheet’s print preview.

 

Using the Protect Range Feature of Google Sheets to Hide Columns from Certain Users

Sometimes you might want to hide certain columns because they contain sensitive data. You might not want other users to tamper with or change the contents of those columns.

In such cases, simply hiding the columns with the ‘Hide columns’ option will not work. You would also need to protect the columns.

Here’s how you can hide and protect columns from certain users in Google Sheets:

  • Select the headers of the columns you want to hide.
  • Right-click on your selection.
  • Click on ‘Protect Range’ from the context menu that appears. Using the Protect Range Feature of Google Sheets to Hide Columns from Certain Users
  • This will open the ‘Protect sheets and ranges’ sidebar on the right side of the window.
  • Make sure the ‘Range’ tab is selected.
  • Click on the ‘Set permissions’ button.
  • This will open the ‘Range editing permissions’ window.
  • Make sure the ‘Restrict who can edit this range’ button is checked.
  • Select which users you want to allow edit permissions to. If you want certain users to not receive access to the selected columns, make sure the checkboxes next to their names are unchecked.
  • Click Done. This will protect the selected columns from getting edited by the shared users.
  • Now you can go ahead and hide the columns (by right-clicking them and selecting ‘Hide column(s)’). Using the Protect Range Feature of Google Sheets to Hide Columns from Certain Users

Once you do this, the users who don’t have edit access will see that there are hidden column(s), but they will not be able to unhide the column(s), so they will, in turn, not be able to see or make any changes to the column(s).

Here’s the message that they will see if they try to unhide the column(s):

But there is a catch here. This will not completely prevent them from viewing the contents of this column. The users can easily make a copy of the worksheet and then unhide the columns in the duplicate sheet.

In other words, this technique prevents users from making changes to the original sheet’s contents. But it cannot prevent users from making changes to the columns in a separate copy of the worksheet.

 

Using IMPORTRANGE to Hide Columns from Certain Users in Google Sheets

If columns in your sheet contain secret information that you don’t want other users to see at all, simply protecting the columns will not be enough. Unfortunately, there is no way to completely hide columns on Google Sheets, at least not as of yet.

But here’s a hack that might work. It might not be very straightforward and might need a little more effort on your part, but we found this to be the most effective way so far.

The hack involves using the IMPORTRANGE function to import the column(s) from a separate, private file, and then protecting and hiding the columns(s) in the shared file.

The IMPORTRANGE function is a handy Google Sheets function that lets you access cells from a separate Google Sheets file. The syntax or the function is as follows:

IMPORTRANGE(url,sheet_name!cell_range)

Here,

  • url is the url of the file from where you want to import the data
  • sheet_name is the name of the worksheet that you want to import data from
  • cell_range is the range of cells that you want to import

Both parameters of the function should be enclosed in double quotes.

To understand more about the IMPORTRANGE function, click here.

Here are the steps to hide columns from certain users in Google Sheets using the IMPORTRANGE function:

First create a private sheet containing the columns you want to hide from other users (You can choose to grant access to those users who you don’t mind sharing the details with though).

Now open the file that you want to share with other users.

In this file, you can import the columns (that you want to protect from certain users) from your private file. But you have to make sure that the column where you import the cells does not already contain any data.

To import the cells, click on the first cell of the column and use the IMPORTRANGE function to import the range of cells. So if you want to import cells B1:B9 from Sheet1 of your private file, you can type:

 =IMPORTRANGE(“url”,”Sheet1!B1:B9) 

You will enter the URL of the private file instead of the word “url” here.

Using IMPORTRANGE to Hide Columns from Certain Users in Google Sheets

Notice that this will copy all the cells from the given range into the column where you entered the formula.

Now use the ‘Protect Range’ feature to protect the column where you inserted the formula (as shown in the previous section).

Using the Protect Range Feature of Google Sheets to Hide Columns from Certain Users

Use the ‘Hide Column’ feature to hide the column (as shown in the first section of this article).

Temporarily Hiding Columns in Google Sheets

You are now all set to share this new file.

 

What Happens When You Use the IMPORTRANGE METHOD to Hide and Protect a Column?

When you apply this technique to hide and protect a column, all users can see that there is a column or a set of hidden columns. But users who do not have edit permissions will not be allowed to unhide the columns.

They will see a message as shown below:

Now what happens if the user makes a copy of the sheet?

In this case, again all the columns will get copied to the new sheet. The user can still see that there are hidden columns.

However, when they try to unhide the columns, they will see a #REF! error. When they hover over the cell containing the error message, they will see a message that says: “You don’t have permission to access that sheet.

What Happens When You Use the IMPORTRANGE METHOD to Hide and Protect a Column?

This is because the IMPORTRANGE function is trying to access a file that the user does not have access to.

Note: When you want to make changes to data in the hidden columns, you will need to make the changes in the private file (not the shared file). Since the IMPORTRANGE function works dynamically, the changes you make will automatically get updated in your shared file.

These were some handy tips and tricks that you can use the next time you want to hide any columns from prying eyes. We hope our explanations were clear and helpful.

Nahid
+ posts

Nahid Akhter is a writer, programmer, and online course content creator. She has an MS in Computer Science and has been in the education line for more than 14 years. Her specialization is in programming and Tech-writing, and her areas of interest include Office productivity, Artificial Intelligence, and Web design and development.