The HideColumnIf function

The HideColumnIf function hides a column in a Wrap when a controlling cell is TRUE. It works like when you hide rows with the Hide Rows/Sheets widget or the HideRowsIf function, but on columns instead of rows.

When a controlling cell returns TRUE, the HideColumnf function hides the column it is in. You activate the function by inserting it into any cell on the column you want to hide.

There is a similar funciton that hides unnecessary rows.

Example: hiding irrelevant input fields

You wouldn’t ask a technician “how many problems were reported in the fifth carriage” if you knew the train only had three carriages. It is a common problem in Wraps and other web forms that some questions only need to be asked under certain conditions. Unless a question is relevant, it is often better to hide it from the user.

To create a Wrap that handles this situation automatically, you can begin by asking the respondent how many cars there are in the train. In the example below, their response will appear as a choice from the dropdown widget in cell B1, which is named car_count.

Screenshot of a Wrap with a dropdown for the number of cars in a train

We will use column C and up to ask for information about defects in the corresponding carriage. We could create a separate controlling cell for each column but in this case, it is easier to use a condition directly in the HideColumnIf function call.

Screenshot of a HideColumnIf function call

  • You can see in the formula in G2 above that column G will be hidden if a value less than five is selected in the car_count dropdown, i.e. we will not be needing column G if there are less than five cars in the train.
  • An identical function in F2 hides column F if there is no fourth car in the train.
  • For every supported number of cars, there is a column in the table, with a formula that makes the column visible only if the corresponding car exists in the train and it becomes necessary to ask for its defects.

One of the advantages of the HideColumnIf function is that we can use Excel’s “fill right” feature, or copy/paste, to copy the HideColumnIf function call to each column. Let’s assume we insert a row of controlling cells as row 3 in the example above, and that the HideColumnIf function in C2 if expressed as =HideColumnIf(C3). When we copy/paste or fill this formula to the right, each HideColumnIf function will automatically reference its own controlling cell in the same column.

You may want to hide both the HideColumnIf function and its controlling cell from the user. You can do this with the Utility widget.

Function reference

Screenshot of a HideColumnIf function call

Example

In this example, column G of the Wrap will be hidden if the car_count dropdown (in B1) is set to a value less than five.

This is what this HideColumnIf function call will look like:

=HideColumnIf(car_count<5)

Format and parameters

=HideColumnIf(expression)

The cell containing the HideColumnIf function can be hidden or in a hidden row.

expression

Specify the address or name of the controlling cell that returns TRUE when the column is to be hidden. The controlling cell can be on a different column or worksheet than the column it is hiding. The same controlling cell can be used by any number of HideColumnIf functions on any number of sheets in the same workbook.

The controlling cell can be hidden, in a hidden row, and/or on a hidden sheet.

In the example above, we could have inserted a controlling cell in D3 with the formula

=car_count<2

However, this content would not fill down or copy/paste nicely, since you would have to manually change the number of cars for each column. To make it possible to use the same function for all the columns of the table, you could change it to

=car_count<COLUMN()-2

Since column D is the fourth column, this formula evaluates to the same condition as the one above, but it can now be copied unchanged to work also for all other columns in the spreadsheet. Perhaps a minor saving in this case with only five columns, but if your table includes many columns using a smarter formula can save time and reduce the risk of errors.

If the Wrap doesn’t already have a cell that you can use as the controlling cell, you can control the visibility of the column with a condition directly in the HideColumnIf function that either returns TRUE or FALSE like in the examples on this page.

The controlling cell is an input cell and must be named. The cell with the HideColumnIf widget does not have to be named, and naming it adds little value or readability.

Known issues

  • You cannot hide a column that has merged cells. If C6:G6 are merged into one cell, you cannot hide any of the columns C through G because it would include parts of the merged cell. WrapCreator will detect this and issue a warning message. You will have to unmerge the cells or refrain from hiding the column.
  • If there are empty cells in two or more adjacent horizontal cells, Excel sometimes merges them. This behavior is something we can detect but not control. If we find that such a spontaneous merge affects a hidden column, we will let you know with a warning message. In this case, you will have to fill one or more of the affected cells with any invisible character, e.g. space, which will prevent Excel from merging the cell.