To make it easier to use an electronic form or calculator, you may want to hide certain rows in the spreadsheet until a specific condition is met. In a survey, you may even want to hide entire worksheets unless they are relevant. 

To hide rows or worksheets in a spreadsheet, use the Hide Rows/Sheets widget. First you specify what parts of the spreadsheet to hide, then you designate the condition that will trigger the display of the selected rows or sheets. Follow the step-by-step instructions below to use the Hide Rows/Sheets widget.

Example webpage: Hide Rows Sheet

Example spreadsheet:  Hide_rows_sheets.xlsx

Insert the Hide Rows/Sheet widget

Click on the Hide Rows Sheet icon in the Widgets tab of the task pane.

step1

Click the Add new button.

addnew

Select Hide Sheet to hide whole worksheets or Hide Rows to hide rows.

For Hide Sheet, an option named Sheet to hide will appear where you tick the checkboxes corresponding to the sheets you want the browser to hide when a condition is true.

hidesheetstep234

For Hide Rows, an option named Rows to Hide will appear where you need to choose which rows you need to hide in the browser. Click on the Select Rows button and use the dialog box named Select Row range to select the range of rows to hide when a condition is true.

addrow-1

In the Controlling Cell option, select the cell which will control if the rows or worksheets are hidden or visible. If the value of the controlling cell is True or non-zero, the controlled rows or sheets are hidden.

The controlling cell may contain a checkbox widget, a textbox widget or any other widget where values can be entered.

A default value for the controlling cell can be set from the link. Read more below.

step-234         hidesheetstep234

To remember what these widget properties do, give them a descriptive name that tells you what you intended with the setup.

addrowstep6

If you don’t want the controlling cell to be visible, insert a Utility Cell widget from the Widgets tab of the task pane into the controlling cell and check Make hidden field.

Convert the spreadsheet and test the form or calculator.

Use the link to the form to hide rows or sheets

The value of the controlling cell can be assigned a value from the link, e.g. “../form.htm?hide_prices=true”.

The autosave process will normally save the latest value for all input fields in Browser Storage. This autosaved value will override the default value in the link. If hide_prices in the example above is unchecked on a form, it will appear as unchecked the next time the same user opens the same form. If you want the value in the link to override the autosaved value, you have to disable Browser Storage on the Workbook tab.

Edit or remove the Hide Rows/Sheet widget.

Click the Hide Rows/Sheet widget.

step1

In the list of hidden rows/sheets, look for the name you provided earlier while hiding rows or sheet. If you want to edit the settings, click the name. If you want to remove the hide rows sheets properties, click on the Trash icon next to the name.

editstep2

To edit a setting, click the corresponding “pencil” button.

edit_hrstep4

To change the sheets to hide, just check or uncheck the sheets accordingly.

edit_hsstep4

After you click the “pencil button” beside the Rows to Hide option, the Select Row range dialog box appears. Select the rows to hide in the worksheet if the condition is true.

edit_hrstep5

After you click the “pencil button” beside the Controlling cell option, the Select controlling cell dialog box will appear to let you select the cell which will make the worksheets or rows hidden or visible.

edit_hsstep5

Change the name of the widget properties, if required.

editstep6

Click the Update button.

editstep7

Convert the spreadsheet and test the form or calculator.