Best Ways to Hide and Unhide Columns in Google Sheets

Hiding columns is often handy for hiding unnecessary details in large Google Sheets. Sounds reasonable, but how to unhide columns like a pro?

This article answers this question with several methods to hide/ unhide columns in Google Sheets. Let’s get started!

Why Hide or Unhide Columns?

The critical question is, why hide the column? There are various reasons to hide an existing column in the worksheet:

Reasons to Hide the Columns in Google Sheets

Reason 01: There might exist one or more blank columns in between (left for future use). These columns are not part of the current work/ discussion, therefore, hide them, so empty columns do not appear in the printout.

For example, in this sheet, the user has left columns C & column D blank. The user will give quantity and packing information In the future.

In the above google sheet, the user has hidden columns C & D instead of deleting them.

The printout might not require a column with some formula/ condition/ note. Therefore it is necessary to hide the column. In the google sheet, column D has packing information.

Suppose, in some reports, packing information seems additional, and the user wants to avoid this information. Therefore, users do not have to create a separate sheet for printout or delete column C for printout.

The simple thing is to hide column C, which will become similar to the second sheet, where columns C & D are not visible.

Reason 02: The user might want to concentrate on the third and eighth columns for the time being, whereas the values in columns fourth to seventh make it difficult to focus; therefore, the user can hide columns four to seven.

For example, in this google sheet, column B has item names, and column F has prices. The user has a lengthy list of these items. The boss is on call, and the assistant has to read things and costs.

Concentrating on the particular row is difficult, as prices can also mix with column D having quantities. Therefore, the simple solution is to hide from column C to column E.

Reason 03: The user has some valuable information that the user does not want to disclose in the printout (due to privacy or for better focus of the current audience); therefore, the user can hide the required column.

Consider the google sheet, where the teacher is preparing the grades. The teacher wants to adjust sessional marks, one or two so that students promote to the next step. However, the teacher wants to avoid exposing this adjustment to students; instead, the teacher wants to show only final sessional marks to students.

Therefore, the teacher will hide columns D & E, as you can see in the following google sheet with five columns only.

Reason 04: The user wants to take the printout on a single page; therefore, hiding some less essential columns from the current printout is a good idea.

The teacher feels uncomfortable working on multiple pages, whereas the teacher finds a solution that columns B & column D is unnecessary, so the answer is to hide them.

This sheet has only seven columns, and it is possible to take a printout on a single page. There can be more reasons to hide a column; however, the next question is, why unhide the columns?

Reasons to Unhide the Columns

There can be many reasons; however, we can list down some main reasons to unhide the columns:

The user has hidden the columns because they were not in use. Now user wants to put values in those columns. Therefore, users have to unhide them to use them
The user might like to modify the formula in the hidden column because it was incorrect or some change is required. For example, the tax rate is now 15% from 12%
The user has completed the work for which, in the past, the user has hidden some intermediate columns. Now, it is required to unhide those hidden columns
The user requires modifying the values in the hidden fields. For example, rate modification to calculate tax or allowances, or commission.

How to Hide Columns in Google Sheets?

Our focus is how to unhide the column, but for beginners, we will quickly discuss the ways to hide the column:

Method 01: Hide Columns in Google Sheets Using Context Menu

Due to any reason, if the user wants to hide one or more columns, the simplest way is to select columns using the mouse and hide them using the context menu option. For example, in the google sheet, the user has selected the columns F, H, I, K & L to hide. After that, the user opens the context menu by right-clicking the mouse on one of the selected columns. The third option is “Hide columns.” By clicking this option, the user can hide the columns selected.

In the next view, it is visible that columns F, H, I, K & L are not visible. The user can hide columns one by one using the same process if the selection of multiple groups seems complicated.

However, the user can select multiple groups by pressing the “ctrl” key while selecting various groups.

Method 02: Hide Columns in Google Sheets Using Script

The script is also helpful in hiding the columns. In the script, the user has to provide a range of columns, just like the third line has a range ‘B: C.’ In the last statement, the function ‘hide columns’ will hide the columns in the range.

function hide_columns() {

  var sheet = SpreadsheetApp.getActive();

  sheet.getRange('B:C').activate();

  var active_sheet = sheet.getActiveSheet();

  var columns = sheet.getActiveRange().getColumn();

  var num_cols = sheet.getActiveRange().getNumColumns();

  active_sheet.hideColumns(columns, num_cols);

};

How to Unhide Columns in Google Sheets?

There are many ways to unhide the columns. We discuss them one by one with detailed examples:

Method 01: Unhide columns in Google Sheets using simple clicks

Consider the Google sheet and notice column B is not visible, whereas columns A & C are, which means someone has hidden column B. Two small left and right arrows are visual in the junction of column A & column C.

Simply click on the left or right arrow between the columns to unhide the hidden column (B).

Method 01: Unhide columns in Google Sheets using simple clicks

Suppose the user hides multiple columns in the group, where columns are neighbors to each other. In such a case, a single set of arrows appears in the junction of two left-right columns. To unhide all the columns, a single click is enough.

In the google sheet, columns C to F are missing, meaning they are hidden. So by clicking either the left or right arrow, the user can unhide column C to column F.

Suppose the user has hidden the columns with gaps. For example, see the google sheet, where the user has hidden columns C and columns G, H. Clearly, there are two pairs of arrows. The first arrow is at the junction of column B & column D. Second arrow is at the intersection of column F and column H.

In this case, the click will unhide only the group of columns between the neighboring columns. For example, clicking the first pair will unhide column C only. The click to the second pair will unhide column G only.

Method 02: Unhide columns in Google Sheets using the context menu

The user can unhide columns by selecting a pair of neighboring columns, where hidden columns come between the two. After selection, the user has to right-click the mouse.

For example, in the following google sheet, the user has selected column F & column H, where column G in between is invisible (which means hidden). Right-clicking the selection causes a context menu to appear (as in the image), where multiple options are available. There is an option “Unhide columns”. By clicking this option, the user can unhide the columns.

Method 02: Unhide columns in Google Sheets using the context menu

The next question is, can we unhide multiple non-neighboring columns together, or do we only do it one by one? The answer is yes, you can unhide multiple adjacent hidden cells together. Let’s see how to do that.

Method 03: Unhide Multiple Groups of Columns in Google Sheets Using Context Menu

Using the context menu option, the user can unhide multiple groups of columns in one step. See the first group from the left side of the screen. Select the first column to the left of the first hidden group of columns.

Similarly, check the last group of columns towards the right-hand side of the screen. Select the first column to the right of the previously hidden group of columns.

In the google sheet notice, there are two hidden columns. One is column C between column B & column D, and the second is column G between column F & column H. Therefore, the user has selected column B to column H, covering both hidden columns.

Again, right-click and select “Unhide columns,” All the hidden columns between the chosen columns will unhide.

Method 04: Use scripts to unhide columns in Google Sheets

The user can write scripts if a set of tasks is required repeatedly. To write the script, select the option Apps Script from the Extensions menu. A window similar to the following will apear.

The user can press plus sign to add a new script. In the above script page, we have created three scripts, as can be seen on the left side of the snippet. When you click on any script, it opens up in the editor on the right side. Once you are done writing your script, you can click Run to execute the script.

To run any other script, click the name of the script from the left window and click run to execute the macro. To read more about scripts click here. Let’s develop and explain scripts to unhide columns one by one.

Unhide single columns using script in Google Sheets

It is also possible to write a script to unhide the columns. See the script:

function unhide_columns() {
  var sheet = SpreadsheetApp.getActive();
  sheet.getRange('B:B').activate();
  var active_sheet = sheet.getActiveSheet();
  var columns = sheet.getActiveRange().getColumn();
  var num_cols = sheet.getActiveRange().getNumColumns();
  active_sheet.showColumns(columns, num_cols);
};

Using this script, we can unhide column B. In the first line, the function will get an active spreadsheet. This function will return a spreadsheet if any sheet is active. Otherwise, the function will return NULL. The activate() method in the second line will activate the range of columns (the range of hidden columns).

Here, we are giving ‘B:B’, because we want to show a single column. We may extend range towards the left. For example, ‘B:E’; means, we want to show hidden columns in the range B to E. The third line will get the active sheet from the set range.

The fourth line will select the hidden columns out of the range (I will re-explain this statement later). The fifth line will choose the number of hidden columns. Finally, the last line will unhide the hidden columns.

Unhide multiple columns in Google Sheets using the script

We can unhide multiple neighboring columns using the script:

function unhide_columns() {
  var sheet = SpreadsheetApp.getActive();
  sheet.getRange('B:D').activate();
  var active_sheet = sheet.getActiveSheet();
  var columns = sheet.getActiveRange().getColumn();
  var num_cols = sheet.getActiveRange().getNumColumns();
  active_sheet.showColumns(columns, num_cols);
};

We have a group of hidden columns from column B to column D. We can unhide column B to column D using this script. The script is the same; only the second line has a range from column B to column D.

Unhide multiple groups of columns in Google Sheets using the script

We can unhide multiple groups of columns (where the column need not be in neighbors). In the google sheet, the user has hideن column B between columns A & C. Next; the user has hidden columns F & column G between columns E & H.

The user can unhide both groups with a single similar script with only a change in the range:

function unhide_columns() {
  var sheet = SpreadsheetApp.getActive();
  sheet.getRange('B:F').activate();
  var active_sheet = sheet.getActiveSheet();
  var columns = sheet.getActiveRange().getColumn();
  var num_cols = sheet.getActiveRange().getNumColumns();
  active_sheet.showColumns(columns, num_cols);
};

This script will unhide both groups, column B and columns F & G. Here, the variable columns will contain column B, column F & column G. Similarly, the second last line will get three in variable “num_cols.”

Conclusion

Google sheets are handy for doing a variety of tasks requiring row-column format. Hide/ un-hide is a handy utility. Users can hide the column whenever needed. The hide operation can provide safety and ease of handling the data.

There are multiple ways of un-hiding the columns, and the user can choose any of them according to the requirement. For example, the script is handy if the user has to hide/ un-hide frequently. For occasional use, an arrow key or context menu option is available.